Updating Access ODBC connections using VBA

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s