hanging ODBC Connect Str has no impact unless I close/reopen Access

[dgelling]

I am developing a form that allows a user to chose a Server & Database & enter their credentials.

The form has several buttons that execute SQL Pass Through queries against a Sybase database.

The first pass works fine: the user choses Server1 and Db1, provides credentials, and executes the pass-thrus. Life is good.

But, if they then chose Server2 and Db2, enter new credentials, and reexecute the pass-thrus, they are still executed against Server1 and Db1. You must close / reopen Access to chose a different back end database.

In a nutshell, Access seems to remember (get stuck?) on the first Server/DB connection I make. I verify that myQuery.Connect has been correctly changed to the new Server / Db combination,

I've set up test code to simply loop through a bunch of hard coded connect strings, execute a "select db_name()" sql pass through and MsgBox the connect string / query results. The connect string changes as expected, but I get the first db_name() every time.

Is there any way to avoid this close / re-open work around?

Many thanks.

 

[PHV]

Are you sure you close and release the Connection object ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dgelling]

Thanks for the quick reply.

I close the connection & the query. However, I don't "release" them.

This is what my code looks like (I replaced my pw with ***).

First the calls to the function with 4 different connect strings:
ParamSPT ("ODBC;DSN=sybase;NA=kmhp3,4100;DB=DEV;UID=dgellin;PWD=**********")
ParamSPT ("ODBC;DSN=sybase;NA=reports,4100;DB=RPT;UID=dgellin;PWD=**********")
ParamSPT ("ODBC;DSN=sybase;NA=facets,4100;DB=PROD;UID=dgellin;PWD=**********")
ParamSPT ("ODBC;DSN=sybase;NA=kmhp24,5200;DB=UTIL;UID=dgellin;PWD=**********")

Now the function definition:
Function ParamSPT(ConnectString As String)

Dim db As DAO.Database, ws As Workspace, MyQ As QueryDef, rs As Recordset

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("", False, False, ConnectString)

Set MyQ = db.CreateQueryDef("")
MyQ.ReturnsRecords = True
MyQ.SQL = "select db_name() as dbname"

MyQ.Connect = ConnectString

Set rs = MyQ.OpenRecordset

While Not rs.EOF
MsgBox ConnectString & ": " & rs.Fields(0).Value
rs.MoveNext
Wend

MyQ.Close
db.Close
ws.Close

End Function

Now the output from each of the four calls:
You can see the connect string changes, but the result at the end of each line is the initial database. It never hits the other servers in calls 2, 3, or 4.

ODBC;DSN=sybase;NA=kmhp3,4100;DB=DEV;UID=dgellin;PWD=**********: DEV
ODBC;DSN=sybase;NA=reports,4100;DB=RPT;UID=dgellin;PWD=**********: DEV
ODBC;DSN=sybase;NA=facets,4100;DB=PROD;UID=dgellin;PWD=**********: DEV
ODBC;DSN=sybase;NA=kmhp24,5200;DB=UTIL;UID=dgellin;PWD=**********: DEV

In fact on the second call, I know I have the incorrect password, and yet the call does not fail, telling me that it's hitting the first server.