2

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

from https://social.msdn.microsoft.com/Forums/sqlserver/en-US/063270f6-0bb6-4630-85f5-b5b3a72e5295/turning-identityinsert-on-from-ms-access?forum=sqldataaccess

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.

Community
  • 1
  • 1
marc40000
  • 3,167
  • 9
  • 41
  • 63
  • Can you double check the query type? This error is normally returned by SELECT queries, not Pass-Throughs. – David Rushton Mar 11 '16 at 12:47
  • Where can I set the query type? I don't find such a property in QueryDef. – marc40000 Mar 11 '16 at 13:55
  • In addition to @GordThompson's excellent answer; you can also manually check. Right click on your query. Choose *Design View* from the menu. Make sure the *Query Tools Design* ribbon is selected. There are 9 buttons in the *Query Type* section of the ribbon: Select, Make Table, Append, ..., Pass-Through and Data Definition. Check which is highlighted. – David Rushton Mar 11 '16 at 14:16
  • I have no query in design view. I know I can set the passthrough property there. I want to build a query in vba and pass it through to the SQL Server. – marc40000 Mar 11 '16 at 14:19

1 Answers1

4

[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
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • I tried that and added qdf.ReturnsRecords = False. When I call execute, it now tells me Laufzeitfehler '3146' ODBC-Aufruf fehlgeschlagen. Translate to english this means Error 3146 ODBC call failed. – marc40000 Mar 11 '16 at 13:58
  • Ah my query is SET IDENTITY_INSERT [Account] ON. If I run it in SSMS it tells me Table 'Account' does not have the identity property. Cannot perform SET operation. I thought I could just call it on every table before I copy. Thx. – marc40000 Mar 11 '16 at 14:18