Access run time error 156

Question

In Access 2003 I am getting run time error 156 : Incorrect syntax near teh keyword 'Trigger' when I run the following code.

Dim myline As String

Dim mySQL As String

myline = Me.tbllineID

mySQL = "DISABLE TRIGGER dbo.tblroconcernAfterUpdate ON dbo.tblroconcern"

mySQL = mySQL & " GO"

mySQL = mySQL & " Update tblroconcern"

mySQL = mySQL & " Set dochold = 0"

mySQL = mySQL & " WHERE     (tbllineID = " & myline & ")"

mySQL = mySQL & " GO"

mySQL = mySQL & " ENABLE TRIGGER dbo.tblroconcernAfterUpdate ON dbo.tblroconcern"

mySQL = mySQL & " GO"

DoCmd.RunSQL (mySQL)

The SQL works when run from SQL Server Management Studio so I am thinking that Access wants to do something differant with the work Trigger.  I have tried it without the dbo. prefix and with [ ] around the trigger and table names.   Any ideas on a work around?

Answer 1

Ouch!

You will need to construct a DSN to the SQL Server or construct a query that links to the server.

In the VBA window, enter the following.

?currentDb().TableDefs("nameOfALinkedTable").Connect

Copy and paste the connection string (remove password and UserID if there).  That will tell me how you are connected to the data on the server.  You will need that connect string to set up your passthrough queries.

Answer 2

No.  How do I build a pass through query?

Answer 3

In order to do this, you need a passthrough query.  A standard ODBC query will not work.

Even with a passthrough query I'm not sure you could make this work.

Do you have any idea on how to build a pass through query?