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.

What is the external database? Another Access DB? If so, why not use linked tables instead of ODBC? — Parfait, Aug 19 '15 at 21:35
The external database is KB_SQL. I have the tables set up as linked tables in Access via the ODBC DSN Data Source so there is no code in how this works. When I access one of these tables (either directly or as part of an Access query) I then authenticate with the KB_SQL server. However the issue is that from that point on Access retains the connection until the database is closed. I want to be able to force this open connection to close so that I can re-authenticate without having to close Access. So far I've not been able to find any VBA functions which will do this. — andshrew, Aug 20 '15 at 08:18

score 1 · Accepted Answer · answered Aug 20 '15 at 15:14

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.

I think you're on to something here - having a few issues with the DSN-less connection string (no error message defined, using [their example](http://kbs.custhelp.com/app/answers/detail/a_id/748/kw/odbc) so this is separate kb_sql issue I think) - but changing it to use a different User DNS that points to the same server does prompt it for credentials again, so I think this suggests that your idea would work. — andshrew, Aug 20 '15 at 15:54
It's been a while since you posted this but I've finally been able to fully test your suggestion to confirm that it works. Your suggestion of a DSN-less connection where you change the `Database=...` (or in my case `Server=...`) to something unique then Access will re-prompt you for credentials and open a brand new connection... allowing me to seamlessly switch which connection my linked tables are authenticating with. — andshrew, Feb 11 '16 at 14:02

score 0 · Answer 2 · answered Aug 20 '15 at 09:43

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.

Thanks for your suggestions @Andre451 - I think the issue is that Access is still retaining an underlying cache of the connection which isn't released until the application is closed. When I tried this it is re-creating the linked tables without prompting for credentials. I've since come across [this page](https://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/) which mentions this behaviour in Access so perhaps it isn't possible to manually clear this cache. — andshrew, Aug 20 '15 at 14:46

Manually Close ODBC DSN Connection

2 Answers2

Linked