Keeping an open connection to an Access database in Visio VBA

[Visisthebest]

Is it possible to keep an open connection to an Access database in VBA to keep a diagram in Sync with an associated Access file?

Here is some examples of opening a db connection to Access in Excel:
https://www.exceltip.com/import-and-export-in-vba/how-to-connect-excel-to-access-database-using-vba.html

I will add context menu vba commands to shapes that change the diagram and update the access file, but I fear constantly opening/closing a connection to an Access db can slow the user experience down in a noticeable manner for the end user, maybe other issues as well.

Thank you for your help and advice!

[Yacine]

That is actually how it is meant to work.
Differently from working with excel (or other) files, Access manipulations are done on a single connection base.
Don't open however Access, just connect to the file.

[Visisthebest]

Super thank you Yacine I didn't know this! How do the various VBA sessions (each time a users starts a piece of vba code) retrieve the same open connection? What is a good way to work with this, are there code examples?

[Yacine]

How do the various VBA sessions (each time a users starts a piece of vba code) retrieve the same open connection?

They don't.
Each session opens its own connection, reads or writes to the DB then closes it.
Just guessing that opening exclusively a DB is nothing one would like to do. The DB is generally meant to be used by several users (sessions). Everyone uses it for a very short time, then releases it for others.

What is a good way to work with this, are there code examples?

Here's a snippet I wrote many years ago - just to get you started.

Code Select Expand

Private Sub UserForm_Initialize()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim MyShape As Visio.Shape
Dim Criteria(7), TotalCriteria, SQLStr As String
Dim i As Integer
Const DBFullName = "somepath\DBname.mdb"

Const TableName = "myTableName"

On Error GoTo errHandler
    If Visio.Application.IsUndoingOrRedoing Then
        GoTo exitHere
    End If


' *** some Criteria handling stuff here ****


If TotalCriteria = "" Then Exit Sub
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";" ' **** you need to find the appropriate connection string to your DB!
Set rs = New ADODB.Recordset

SQLStr = "SELECT DISTINCT * FROM " & TableName & " WHERE " & TotalCriteria
rs.Open SQLStr, cn, adOpenStatic, adLockOptimistic, adCmdText

If rs.RecordCount < 1 Then
    MsgBox "keine Datensätze gefunden"
    GoTo CloseDB
End If
rs.MoveFirst
For i = 1 To rs.RecordCount
    If rs!materialnr <> "" Then
        ListMaterial.AddItem rs!materialnr
    Else
        ListMaterial.AddItem "-"
    End If
    If rs!teil <> "" Then ListMaterial.Column(1, ListMaterial.ListCount - 1) = rs!teil
    If rs!nennweite <> "" Then ListMaterial.Column(2, ListMaterial.ListCount - 1) = rs!nennweite
    '.... etc.
    rs.MoveNext
Next i

CloseDB:
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
exitHere:
    Exit Sub
errHandler:
    MsgBox Err.Description
    Resume exitHere
End Sub

[Nikolay]

I fear constantly opening/closing a connection to an Access db can slow the user experience down in a noticeable manner for the end user, maybe other issues as well.

I would not worry about that. There is connection pooling, provided by the underlying data access library (e.g. ADO).
https://en.wikipedia.org/wiki/Connection_pool

BTW, you could try my BackSync Extension - it should be able to write data back to Access, if you simply connect your database to shapes using standard Visio "Connect to Data" feature.

[Visisthebest]

Thank you Yacine and Nikolay for your help! The connection pooling is very useful, otherwise the user experience could become really sluggish.

Nikolay the BackSync Extension is really impressive didn't know this was possible with Visio.