Setting default ODBC connection

[hadwin]

Hi everyone,

I have a database that retrieve a bunch of data from an external Oracle database through ODBC, it then save them as local tables.

I therefore have a lot of SQL Pass-Through queries. I used to have a manual process of clicking on each query, to retrieve one table at a time. I now managed to code it in VBA, so at the click of a button all the Pass-Through SQL queries will run.

However, now the "Select Data Source" window keep on popping up. Is it possible to have this window only pop up once, let the user set the ODBC connection, then save it as the default so the window won't pop up again.

Also, can anyone tell me how I can do error checking for ODBC errors.
ie. if ODBC returns a error, I can display a message box telling user what to do instead of the default error message.

Thanks,

Hadwin.

 

[SBendBuckeye]

If you combine ADO and DAO, I think you can automate the entire process. Check out www.ConnectionStrings.com to find the Oracle ADO connection strings. Once you have established an ADO connection, you can create an ADO recordset based on the SQL string found in the DAO QueryDef collection.

 

[hadwin]

Thanks for the answer...

Only problem is I am using am using MS Access 97. I don't believe Access 97 have ADO, only DAO.. Is there any way to work around this?

I am thinking since I'm sure Access 97 have the "Select Data Source" window already, all I need to do is to find out how Access call this (in code). Hopefully, this window can return the vaules for UID, PWD & DSN etc... then all I need to do is format it into a string and pass it into the .Connect property of each QueryDef.

 

[SBendBuckeye]

Oracle New ODBC
"Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;"

Oracle Old ODBC
"Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"

Here is some sample code based on Access97 to get you started. You need to set a reference to ADO. Change Dbq path to your database. Good Luck!

Option Compare Database
Option Explicit

Sub TestIt()
'Note: Set a reference to highest Microsoft ADO library on your maching
Dim qdf As DAO.QueryDef
Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field
Dim cnn As New ADODB.Connection
Set qdf = CurrentDb.QueryDefs(0)
cnn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=B:\Sandbox\ArrayProcessing.mdb;Uid=Admin;Pwd=;"
cnn.Open
'Let Intellisense help you with the last 2 options
rs.Open qdf.SQL, cnn, adOpenForwardOnly, adLockReadOnly
rs.MoveFirst
For Each fld In rs.Fields
Debug.Print fld.Name; " ";
Next fld
Debug.Print
rs.MoveFirst
Do Until rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Value; " ";
Next fld
Debug.Print
rs.MoveNext
Loop
rs.Close
cnn.Close
Set qdf = Nothing: Set fld = Nothing: Set rs = Nothing: Set cnn = Nothing
End Sub