Pfthb

I have 2 access databases, one in use as CRM and the other only holds linked tables to a firebird database using ODBC. This firebird database (fdb) is only capable to allow access to one user.

When updating the tables by the CRM through ODBC, the ODBC connection (Firebird) is not released, which means that an other application which needs access cannot open the database. The ODBC connection is only released when the CRM is closed.

Dim dba as database
Dim strODBCname as string
strODBCname = "OSF_ODBC.accdb"
Set dbs = OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)
dbs.execute .... (etc.)

And after all record R/W are completed

set dbs = nothing

Is there an other way to enforce the release of the ODBC connection?

Peter

I've currently no idea how to explicitely release the ODBC connections of the current session, but you could open the database in a second Access application session (a second MsAccess.exe in memory) and release this after work:

Const ODBC_NAME As String = "OSF_ODBC.accdb"

With CreateObject("Access.Application")
 .OpenCurrentDatabase ValidatePath(CurrentProject.Path, False) & ODBC_NAME
 .CurrentDb.Execute ...
 .CurrentDb.Close
 .Quit
End With

This should release the connections for sure.

I don't know which version of Access you are using. But before you create a new application object, you should try

Set wrkAcc = CreateWorkspace("", "admin", "", dbUseJet) 
Set dbs = wrkAcc.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)

or the next level up:

dim dbe=dao.dbengine
set dbe=CreateObject("dao.dbengine")
Set dbs= dbe.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)

More globally, you can modify the registry settings that control ODBC connection caching.