I have an Access db which has several tables linked. Now I'ld like to execute SET IDENTITY_INSERT ON/OFF on those tables. I googled and found things like this
Private mDb As Database
Public Sub SetIdentityInsert(strTableName As String, strOnorOff As String)
Dim qdf As QueryDef
Dim strSQL As String
On Error GoTo Proc_Err
Set qdf = mDb.QueryDefs("qryIDENTITY_INSERT")
strSQL = "SET IDENTITY_INSERT " & strTableName & " " & strOnorOff
qdf.SQL = strSQL
qdf.Execute
Proc_Exit:
On Error Resume Next
Set qdf = Nothing
Exit Sub
Proc_Err:
Resume Proc_Exit
Resume
End Sub
However, if I do this
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("Passthru")
I complains that the passed string has to be one of DELETE, INSERT, SELECT, PROCEDURE or UPDATE.
I'm pretty sure this is possible somehow. Here the author pastes example code calling a Sub ExecutePassThru, but the code for it is absent.
[It] complains that the passed string has to be one of DELETE, INSERT, SELECT, PROCEDURE or UPDATE.
Access is not recognizing that the query is (intended to be) a pass-through query because the .Connect property of the QueryDef has not been set to a string that begins with "ODBC;". You need to set the .Connect property before you set the .SQL property.
Since you already have linked tables defined in the database you can just copy one of their .Connect properties, like this:
Dim cdb As DAO.Database
Set cdb = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = cdb.QueryDefs("Passthru")
qdf.Connect = cdb.TableDefs("YourExistingLinkedTableName").Connect
qdf.ReturnsRecords = False
qdf.SQL = "SET IDENTITY_INSERT " & TheRestOfYourSqlCommand