Manually Close ODBC DSN Connection

Question

I have an MS Access database that connects to another database via a user level DNS ODBC data source.

When the connection is first initiated the ODBC driver will prompt me for a username and password. The database that it then connects to on the server is dependent on the username I use.

Once the connection is established Access will retain / keep it alive until I close the Access database.

Is there a way to force Access to close all open ODBC connections, requiring me to provide the login credentials again when I next try to access an object on the server. What I want to be able to do is switch which database the ODBC connection is accessing without having to completely close Access and re-open the database.

Answer 1

Two things:
1) You can make the process of closing and re-opening Access as painless as possible for your users by doing:

' Close and restart
Shell "restart.bat", vbNormalFocus
Application.Quit

with restart.bat consisting of something like

REM wait for Access to close
TIMEOUT 3
REM your actual command line goes here
msaccess.exe mydatabase.mdb

2) You mentioned:

What I want to be able to do is switch which database the ODBC connection is accessing

The link you posted gave me an idea:

Any subsequent ODBC objects that happen to match on three parameters—ODBC driver, server, and database—will reuse that cached connection.

If you switch your linked tables from User DSN to DSN-less connections, your desired behaviour should happen automatically.
Just change the .Connect string of all tables by altering the "Database=..." part, and Access should ask for credentials when a table is accessed the next time.

Answer 2

Oh, linked tables. I don't think this is possible.
You can try to reset the connection information, but I doubt it will work:

Dim TD As TableDef

For Each TD In DB.TableDefs
    ' Linked table? You can also check the .Connect string more specifically for KB_SQL
    If TD.Connect <> "" Then
        TD.Connect = TD.Connect
        TD.RefreshLink
    End If
Next TD

A more radical approach that might actually work is to delete the table links and re-add them.

Dim TD As TableDef
Dim sConn As String, sName As String

For Each TD In DB.TableDefs
    If TD.Connect <> "" Then
        sName = TD.Name
        sConn = TD.Connect
        ' Remove linked table
        DB.TableDefs.Delete sName
        ' and add it as new link
        DoCmd.TransferDatabase acLink, "ODBC Database", sConn, acTable, sName, sName
    End If
Next TD

You may need a different sort of loop than For Each, since you are modifying the DB.TableDefs collection in the loop.