I don’t do much in Access or VBA but sometimes you inherit things that force you, at least temporarily, to go retro.

We have an Access-based data extraction routine that uses several (21) pass-through queries that use an ODBC connection.  The queries are pulling data from an Oracle database maintained by a different agency that enforces a time-out of passwords every three months.

So every three months I have to go through the ODBC connections for these queries and change the password.  And before you chastise me for storing the password in the VBA code, I can only say ‘you’re right’ but that’s the way it is done–not ideal but gets the job done and we’re not dealing with overly sensitive data.

Well, my three-month deadline was approaching and I figured I would simplify the process which I found error-prone.

Some searching and object model diagramming scouring and I came up with this barebones solution, of course, your connection string may vary.

Public Sub UpdateConnectStrings()

Dim obj As AccessObject
Dim dbs As Object
Set dbs = Application.CurrentData

Dim i As Integer

Dim newConnectionString As String
newConnectionString = "ODBC;DRIVER={Microsoft ODBC for Oracle};SERVER=vader;UID=luke;PWD=iMufathr;"

Dim comparisionConnectionString As String
comparisionConnectionString = "ODBC;Driver"

For Each obj In dbs.AllQueries

If (obj.Type = acQuery) Then
  For i = 0 To CurrentDb.QueryDefs.Count - 1
    If (CurrentDb.QueryDefs(i).Name = obj.Name) Then
      If (Left(CurrentDb.QueryDefs(i).Connect, Len(comparisionConnectionString)) = comparisionConnectionString) Then
        CurrentDb.QueryDefs(i).Connect = newConnectionString
      End If
    End If
  Next i
End If

Next obj
End Sub