I have an application that uses MS Access as the front-end and SQL Server on the back end. In all previous versions of Access, I've used the method 'dbSQLPassThrough' and it works great (and is very fast). With Access 2007 on the front-end, I get the error 3276: Invalid database object reference. What changed in Access 2007 that this no longer works? and, more importantly, what works now?
'Note: strDataPath is set by now and has been verified as a valid path to SQL Server
Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, strDataPath)
'The name of the stored proceedure is car_DocData. It takes one argument: DocID
SQL = "car_DocData " & DocID
Set rst = db.OpenRecordset(SQL, dbOpenSnapshot, dbSQLPassThrough)
'Then I read the results of the recordset.