ODBC Connections

[colette]

I am saving an odbc connection string to a global variable. Periodically, it seems to lose the connection. Does anyone have a way of testing that the connection exists. I have tried to do if gblvar=nothing then... and it doesn't like that. It often gives the err=13 Type Mismatch when it doesn't have the connection.

Ultimately, I would love the ability to test for that connection in my error handler and re-open it if necessary.

Also, an FYI it seems that Access interprets not having a connection to ODBC Oracle tables as "ODBC Call Failed" if you do msgbox error and if you do err.number, err.description it gives a type mistmatch error.

Thank you,
Colette

 

[namliam]

On the startup of your database you could execute a "Select sysdate from dual" which should allways work

the "On error" (find more details in the help) trap any error that is given and give a more "sculpted" error...

Seasons greetings from Amsterdam

The Mailman

 

[colette]

Test for Global Connection = nothing

It turns out to be...

if gblCNN Is Nothing then


If you want to close it, here is a sample I found...

If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing

I wish I didn't have to test for a closed connection and re-open, I am not sure why the global variable loses the connection. If anyone has any insight, I would appreciate it.

Has anyone found any best practices information when working with an Access front-end and Oracle back-end.