Linking Tables on the fly

[Gilbert 2097]

I'm using 2003 - I have a split Db (linked manually) with tables I use for
current data (sales,employees,customers,etc)from a set of seasonal kiosks. I
want to keep just the data from other years in separate Db's and call them up
using code to change the links to that years Db and then change it back to
current. I've been reading alot of the threads about splitting and it seems
that I'll want a table of Paths to pick from but what is the actual method to
use to link to a certain Db. I have used .TransferDatabase which has the
acLink option - is this good or is there another easier way. I saw someone
say that the links in the frontend apply to any Db that's attached - in other
words (as I understand it) if I change the Db containing the records - if
they have the same names - then the links will just go to the new tables. Is
this basically correct - can you help me understand what I'm trying to do?
Suggestions? Thanx

 

[Brian]

Look here for sample re-linking code:

http://www.mvps.org/access/tables/tbl0009.htm

You can either populate the path using a combo box whos RowSource is a table
of paths, or you can browse at runtime using a Windows API call. See this
link for the API info:

http://www.mvps.org/access/api/api0001.htm

 

[Gilbert 2097]

thanx Brian - I think I have just 1 more ?? All the code I've read sets up a
Tabledef using the CreateTableDef but if I have a database with liked tables
that are already set up, aren't there tableDefs in the collection already?
How do I assign them to a TableDef object? Can I say
Dim tdfLinked as TableDef and then
Set tdfLinked = CurrentDb.TableDef("NameOfTable")

and then use the method tdfLinked.Connect to change the Db it's pointing to

Many times you can try different things and then learn from your mistakes
but this is getting into ticklish places so I didn't want to mess things up
really bad.
Thanx for any help!!

 

[Brian]

Here's the germane portion of the Sub I use to do the re-linking part, and it
does, indeed, use the Connect method, but it also needs the RefreshLink
method. This assumes that the table names are the same in each back end file,
and just the name of the .mdb file is different.

Dim dbBackEnd As DAO.Database
Dim TableCount As Integer
Dim FilePath as String
FilePath = "<PATH-TO-YOUR-DATA-FILE-GOES-HERE>"

For TableCount = 0 To dbBackEnd.TableDefs.Count - 1
If dbBackEnd.TableDefs(TableCount).Connect <> "" Then
dbBackEnd.TableDefs(TableCount).Connect = ";DATABASE=" & FilePath & ""
dbBackEnd.TableDefs(TableCount).RefreshLink
End If
Next TableCount

 

[Gilbert 2097]

Thanx Brian - That's perfect. Sometimes you know a lot of individual items
but just aren't sure how to put it all together.This is just what I needed.