Here is what i use to run pass-through queries from VBA.
Code:
Option Compare Database
Option Explicit
Public Sub vcPassThrough(sPassThroughSQL As String, sUseConnectionStringFrom As String)
Dim db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String
On Error Resume Next
Set db = CurrentDb
If ObjectExists("QUERY", "sqlPassThrough") Then DoCmd.DeleteObject acQuery, "sqlPassThrough"
strSQL = sPassThroughSQL
Set qdExtData = db.CreateQueryDef("sqlPassThrough")
qdExtData.Connect = CurrentDb.TableDefs(sUseConnectionStringFrom).Connect
qdExtData.ReturnsRecords = False
qdExtData.SQL = strSQL
CurrentDb.QueryDefs("sqlPassThrough").Execute
qdExtData.Close
db.Close
Set db = Nothing
End Sub
‘this function might already be present
Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
' Pass the Object type: Table, Query, Form, Report, Macro, or Module
' Pass the Object Name
Dim db As Database
Dim tbl As TableDef
Dim qry As QueryDef
Dim i As Integer
Set db = CurrentDb()
ObjectExists = False
If strObjectType = "Table" Then
For Each tbl In db.TableDefs
If tbl.Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next tbl
ElseIf strObjectType = "Query" Then
For Each qry In db.QueryDefs
If qry.Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next qry
ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next i
ElseIf strObjectType = "Macro" Then
For i = 0 To db.Containers("Scripts").Documents.Count - 1
If db.Containers("Scripts").Documents(i).Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next i
Else
MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
End If
End Function
To use it you would call the sub like this: Call vcPassThrough("UPDATE SQL_TABLE SET.....","yourLinkedSQLView") (the function uses the connect string of the linked view for the newly created pass-through query).
Cheers,