June 16, 2004 at 10:07 am
How do I close an ODBC connection in Access.
Unless I shutdown Access, SQL Server seems to keep an ODBC connection and uses the SQL Authentication ID for the original user not the new user. As you can imagine this bit me when using passthru queries against SQL SErver procedures which derive the current user name.
I am currently using the code below which successfully closes the OLEDB connection but not the ODBC connection.
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Set db = CurrentDb
For Each qdf In db.QueryDefs
If qdf.Type = dbQSQLPassThrough Then
qdf.Connect = "ODBC;"
End If
Next qdf
' Release the global connection object
' when the form is closed,
' even if the user doesn't quit
If Not gcnn Is Nothing Then
If gcnn.State = adStateOpen Then gcnn.Close
Set gcnn = Nothing
End If
June 17, 2004 at 2:17 am
Problem is that Microsoft have changed all the rules about DAO.
Two distinct approaches:
Use ADOX with all the problems implied.
Secondly, rethink the DAO in ADO terms, set up an ODBC Workspace. Within the workspace set up a DAO Connection object. Within the connection object. set up your query defs. This gives you an ADO type structure where you do have global DAO connection object which you can then dispose of.
The documentation is dire.
June 18, 2004 at 8:45 am
Thanks for this but are there any examples of the code required to set up and close this type of connection?
June 18, 2004 at 9:10 am
Not that I know of. I'm using Access 2003 which has a good guide (for Access) to DAO.
Basically, the problem seems to be that if you use currentdb you are referring to the default workspace. which apparently can't be closed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply