Рекомендую:

Function Execute_Passthrough_Query(sqltext As String)

' Purpose: Run a temporary pass-through query.
' Accepts: sqltext: SQL string to run or stored
' procedure's name.
' Returns: nothing.

Dim dbSQL As Database, qdSQL As QueryDef
Dim strConnect As String
Dim strCommand As String

strConnect = "ODBC;APP= My App's Name; " _
& "DRIVER=SQL Server;SERVER=MY SERVER; " _
& "DATABASE=MY DB;Trusted_Connection=Yes"
Set dbSQL = DBEngine.Workspaces(0).Databases(0)
' Create a temporary QueryDef object that is not saved.
Set qdSQL = dbSQL.CreateQueryDef("")
' Set the ReturnsRecords property to False in order to use the
' Execute method.
qdSQL.ReturnsRecords = False
qdSQL.Connect = strConnect
qdSQL.SQL = sqltext
qdSQL.Execute
qdSQL.Close
Set qdSQL = Nothing
dbSQL.Close
Set dbSQL = Nothing

End Function

Как вызывать:

Dim strSQL as String
strSQL="INSERT INTO MyTable (nmFirm) VALUES ('Рога и копыта')"
Call Execute_Passthrough_Query(strSQL)