I am moving an Access 97 database to Access 2000. Everything looks good except for Combo Boxes which were previously populated from ODBC queries. Looks like an old trick no longer works and I'm looking for a workaround.
The code below is called from a form named "testit" and a Combo Box named PartNumber is automatically populated with the results of an Oracle query. This works nicely in Access 97. Under Access 2000, there is a long delay as if it is trying to talk to Oracle. Then, the following error is displayed:
"The record source ‘SELECT DISTINCT Part_Num, Part_Desc FROM [Public.Part_MASTER] IN ‘’ ‘ODBC;DSN=Oracle 32bit ORAP;UID=username;PWD=userpwd;DBQ=PRODDB’ WHERE Part_Num <> Null ORDER BY Part_Num’ specified on this form or report does not exist.
You misspelled the name, or it was deleted or renamed in the current database, or it exists in a different database.
In the Form or Report’s Design view, display the property sheet by clicking the Properties button, and then set the RecordSource property to an existing table or query."
Any ideas for making this code under Access 2000? This seems most puzzling.
Bill
P.S. Forgive the formatting below. I'm new to this site and it looks horrible in my browser!
The code below is called from a form named "testit" and a Combo Box named PartNumber is automatically populated with the results of an Oracle query. This works nicely in Access 97. Under Access 2000, there is a long delay as if it is trying to talk to Oracle. Then, the following error is displayed:
"The record source ‘SELECT DISTINCT Part_Num, Part_Desc FROM [Public.Part_MASTER] IN ‘’ ‘ODBC;DSN=Oracle 32bit ORAP;UID=username;PWD=userpwd;DBQ=PRODDB’ WHERE Part_Num <> Null ORDER BY Part_Num’ specified on this form or report does not exist.
You misspelled the name, or it was deleted or renamed in the current database, or it exists in a different database.
In the Form or Report’s Design view, display the property sheet by clicking the Properties button, and then set the RecordSource property to an existing table or query."
Any ideas for making this code under Access 2000? This seems most puzzling.
Bill
P.S. Forgive the formatting below. I'm new to this site and it looks horrible in my browser!
Code:
Function testit() As Integer
Dim ConnectString As String
Dim ODBCConnection As connection
Dim strParts As String
Dim wrkODBC As workspace
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set ODBCConnection = wrkODBC.OpenConnection("Oracle 32bit ORAP", dbDriverNoPrompt, , "ODBC;DSN=Oracle 32bit ORAP;UID=username;PWD=userpwd;DBQ=PRODDB")
ConnectString = "ODBC;DSN=Oracle 32bit ORAP;UID=username;PWD=userpwd;DBQ=PRODDB;"
strParts = "SELECT DISTINCT Part_Num, Part_Desc FROM [Public.Part_MASTER] IN '' '" & ConnectString & "' " & "WHERE Part_Num <> Null ORDER BY Part_Num"
Forms("testit").PartNumber.RowSource = strParts
End Function
