By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,225 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,225 IT Pros & Developers. It's quick & easy.

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

P: 1
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)

Expand|Select|Wrap|Line Numbers
  1.     sConn = "ODBC;DSN=ABC;UID=XYZ;PWD=123;"
  2.     sSQL = "SELECT * FROM BLAG WHERE UPPER(id) = UPPER('12aBxY')"
  3.  
  4.     Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, sConn)    
  5.     Set rs = db.OpenRecordset(sSQL, dbOpenForwardOnly, dbSQLPassThrough)
  6.  
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?
Jan 30 '08 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. 'Defining variables
  2. Dim cnn As ADODB.Connection
  3. Dim rsy As ADODB.Recordset
  4. Dim db_name As String
  5. Dim UserName As String
  6. Dim Password As String
  7.  
  8.     Set cnn = New ADODB.Connection
  9.     Set rst = New ADODB.Recordset
  10.  
  11.     db_name = "KRIJGS"
  12.     UserName = "bertuser"
  13.     Password = "abcxyz123"
  14.  
  15.     'Making an ODBC connection according to ADO
  16.     cnn.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" _
  17.     & Password + ";"
  18.  
  19.     rst.Open "SELECT * FROM BLAG WHERE UPPER(id) = UPPER('12aBxY')", cnn, adOpenForwardOnly
  20.  
  21. End Sub
Feb 5 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.