Linking to different back-end tables

[MrTibbs]

How can I link to a back-end table while keeping the same table alias depending on certain parameters?
Specifically: I want to be able to link to tables staff1, staff2 or staff3 while calling whichever one I link to 'staff'
I'm using Access 97

 

[GaryC]

I suspect what you are trying to do is design just one set of forms and reports which read 'staff' and then be able to swap between tables.

1) If so you could make the link to the backend only at run-time and set the link table name to 'staff' during that 'run' and destroy it on complete.

2) If you only need to read data from the tables you could create a Union Query (called staff) to combine the three tables
and the filter the data at run-time.

3) You could use the 'recordsource' property of your Forms and Reports and set it at run-time to point at Staff1 or Staff2 or Staff3.

HTH
2)

 

[MrTibbs]

I already link to the original table at run-time. What I can't seem to do is change the alias at the time of the connection.
Here is a code snippet illustrating what i'm doing:
Set tdf = dbs.CreateTableDef(AttachTable(i))
tdf.Connect = ";Database=" + SystemPath + AttachFiles(i)
tdf.SourceTableName = AttachTable(i)
<snip>
' Append the new table pointer
dbs.TableDefs.Append tdf

Can I alter the alias after connecting instead? e.g. connect to tblStaff1 then change it's alias to tblStaff?

 

[MrTibbs]

Pat,

That's what i'm doing. It's just a bit tricky (re-opening databases or multiple Access sessions) to maintain 4 identical small databases so I wondered if there was an easy way to put all the tables in one database and just connect to the one's I want at run-time.

 

[MrTibbs]

Each pc has the front-end db (forms, queries, reports, modules) loaded on to it. The shared data is on the network.
Each user starts the front-end with a different command line parameter e.g. /CMD "MA" and that parameter drives the selection of the back-end tables. It all works, security is OK and the only niggle is that my 4 staff and attendance tables are held in 4 seperate db's because I don't know how to replicate clicking on a linked table and changing it's apparent name (alias) in code. It's easy from the Table window. Is it possible in VBA?