DAO connection via ODBC Manager not closing

Question

I'm using the following code to create a DAO connection:

Set myWorkspace = DBEngine.CreateWorkspace("MYAPP", "admin", "")

Set DB = myWorkspace.OpenDatabase(DBName, dbDriverNoPrompt, True, IDatenbank_BuildConnectionString(ODBCConnection))

IDatenbank_BuildConnectionString = "ODBC" & _
                                  ";DSN=" & ODBCConnection.Name & _
                                  ";Uid=" & ODBCConnection.User & _
                                  ";Pwd=" & ODBCConnection.Password

where

IDatenbank_BuildConnectionString = ODBC;DSN=US Company Int;Uid=ALA1Bala;Pwd=hihihi

I'm using the following code to close the connection:

Set myWorkspace = Nothing

Set DB = Nothing

or

DB.Close()

The problem is even if the DB object is setup to null the connection is not getting closed. Due to previous, when a new user is logged in the old credentials are being used for connection and the connection object is created with properties from previous connection. The connection session seems to exists on Oracle database.

How do I destroy the connection when using a DAO driver? Moreover, I have to mention that I can use alternative logging option by using ADO connection and I'm not facing similar issues and the connection is closed and recreated corectly.

Answer 1

I can create a connection in a different way and that way the connection is closing:

Dim DB as DAO.Database
Dim DBE as DBEngine

Set DBE = new DBEngine
Set DB = DBE.Workspaces(0).OpenDatabase(DBName, dbDriverNoPrompt, true, IDatenbank_BuildConnectionString(ODBCConnection))

Call DB.Close
Set DB = Nothing
Set DBE = Nothing

To check in SQL developer if you have an active session:

select * from v$session where username='<enter_your_username>'