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

X-section Xacto Updated

I just happened to stumble upon an update of a tool to Create Geologic Cross Sections, eXacto Section v. 2.0, that I mentioned in July. Jennifer Carrell at the Illinois Geological Survey wrote this tool. The latest update is from December 8, 2010 and can be downloaded at ESRI’s ArcGIS Resource Center. Our office has used previous versions and finds in very useful in creating cross-sections. I have not tried this latest update.

Create Geologic Cross Sections–eXacto Section v. 2.0, ArcMap 9.3

The Create Geologic Cross Sections–eXacto Section v. 2.0, ArcMap 9.3 written by Jennifer Carrell of the Illinois State Geological Survey is a handy tool for creating cross sections.

It requires ArcGIS and a 3D Analyst Extension license.

You can create profiles against multiple DEMs at once, define the vertical exaggeration, and have it include contact points.  It is well documented and comes with sample data to use with the tutorial.

I did not see it mentioned but it also include a personal geodatabase that include a grid that can be used along with the profiles you create.

If  you make profiles, think this is definitely an option you should look at.  The fact that it is a VBA application is a bit worrisome in that its lifespan is limited.