Change reference databases with VBA

[Ilan]

This is pretty esoteric question...

I have a 'master' Access database that is referenced to about ten other
Access databases. All databases (including the master) are in several
different dated directories. Example of filepath 1:
"S:\whatever\whatever\2008\09\whatever\database1_200809.mdb" I would like to
write a piece of VBA code that will allow me to update my references from one
set of dates to another, by simply feeding a date to the VBA through a method
of my choice. Example: I supply a date of 12/1/2008 to my VBA in the master
DB, and the VBA code would now change my reference to filepath 1 from
"S:\whatever\whatever\2008\09\whatever\database1_200809.mdb" to
"S:\whatever\whatever\2008\12\whatever\database1_200812.mdb" Note that all of
the non-dated text in any particular file name or file path never changes for
any of my referenced databases. I would be thoroughly grateful (and
incredibly impressed) if anyone knows how to do this. I'm hoping that it
won't be 500 lines of code, either, if that is possible!

 

[Clifford Bass]

Hi Ilan,

Should be pretty easy. Go through all of the TableDefs in your
database and change each TableDef.Connect value as appropriate, following
each change by a TableDef.RefreshLink. Make sure you only change the linked
tables. That is, those whose Connect value starts with ";DATABASE=". I
forget if local tables have a Connect value or if it is just blank.

Hope that helps,

Clifford Bass

 

[Brian]

Here is a little loop I use. This particular one explicitly ignores local
tables & ODBC-based ones (thus refreshing only the tables connected to an
Access back end). Watch the word-wrap on the text; a couple of the single
lines wrapped below.

Dim FilePathData as String
FilePathData = "C:\MyAccessDB.mdb"

Dim TableCount as Integer
For TableCount = 0 To dbProgram.TableDefs.Count - 1
If dbProgram.TableDefs(TableCount).Connect <> "" And
dbProgram.TableDefs(TableCount).Connect Like "ODBC*" = False Then
dbProgram.TableDefs(TableCount).Connect = ";DATABASE=" &
FilePathData & ""
dbProgram.TableDefs(TableCount).RefreshLink
End If
Next TableCount