Change reference databases with VBA

  • Thread starter Thread starter Ilan
  • Start date Start date
I

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!
 
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
 
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
 
Back
Top