Problem with SQLPassthrough in Access 2007 using DAO: Invalid database object referen

[transpac80]

Problem with SQLPassthrough in Access 2007 using DAO

I have this piece of VBA code from inside Access to retrieve data from external database (SQL or Oracle)

    sConn = "ODBC;DSN=ABC;UID=XYZ;PWD=123;"
    sSQL = "SELECT * FROM BLAG WHERE UPPER(id) = UPPER('12aBxY')"
 
    Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, sConn)    
    Set rs = db.OpenRecordset(sSQL, dbOpenForwardOnly, dbSQLPassThrough)
 

This works fine with all previous versions of Access. In 2007, I got this error "Invalid database object reference"

Have Been Google for 3 days with no luck. Any suggestion?

[MMcCarthy]

I haven't used 2007 much but I have heard rumours that MS is phasing out odbc. You could try the following ADO code which means you won't need the pass through query.

'Defining variables
Dim cnn As ADODB.Connection
Dim rsy As ADODB.Recordset
Dim db_name As String
Dim UserName As String
Dim Password As String
 
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
 
    db_name = "KRIJGS"
    UserName = "bertuser"
    Password = "abcxyz123"
 
    'Making an ODBC connection according to ADO
    cnn.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" _
    & Password + ";"
 
    rst.Open "SELECT * FROM BLAG WHERE UPPER(id) = UPPER('12aBxY')", cnn, adOpenForwardOnly
 
End Sub