Set link to tables at runtime

  • Thread starter DavidMarlowe via AccessMonster.com
  • Start date
D

DavidMarlowe via AccessMonster.com

I have an Access app that I roll out to 7 locations. The code part is the
same but each location has it's data in a different location. Currently I
copy the original to each user's network path and then open it and relink to
their data, but I'm sure there's a way to do this better.

If I just knew the terminology better I'm sure I could find this on the MS
site.

Thanks is advance
 
D

DavidMarlowe via AccessMonster.com

Just to add info: I'm running 2007 and I distribute as an .accde the data is
split to another .accdb in another location.
 
E

Evi

Can you split the database so that each user has the frontend forms etc
while the server has 1 copy of tables in a backend?
Evi
 
D

DavidMarlowe via AccessMonster.com

I'm split into front end/back end. Dev's code looks to me like it's looking
at the names (list) of currently linked tables and offering to pull them from
somewhere else.

I guess what I want to do is something simple like:
Open other DB in some path
Link to table x, y and z
Open my main form and carry on

If I'm reading the sample code right (and it *is* late) it looks like I can
do this:

DBEngine(0).OpenDatabase("\\serverX\databaseY.accdb")

Set tdfLocal = dbCurr.TableDefs("Orders") <----- can I just add
names?
tdfLocal .Connect = ";Database=" & strDBPath
tdfLocal.RefreshLink

Set tdfLocal = dbCurr.TableDefs("Customers")
tdfLocal .Connect = ";Database=" & strDBPath
tdfLocal.RefreshLink

etc...

So assuming I know the name/location of my DB and the names of the tables I
need, do I need to go though all that "walk the collection" stuff he's doing?

Maybe a better question is "do the tables I link have to be pre-determined in
my front end, or can I dynamically link as I go?"

Did that make sense? Did I mention it's late? thx
 
D

Douglas J. Steele

You can hard-code the table names if you want, but why bother? Unless you're
in the situation where tables x and y are linked to database a, while table
z is linked to database b, it's far simpler to use the looping approach
Dev's got. In that way, should you add additional tables in the future, you
don't have to change your code.
 
D

DavidMarlowe via AccessMonster.com

Just posting my solution here.Yeah, I did want the option to possibly add a
unique table to one of them but I agree it's easier to loop through them all.

' I created an array of table names and then call this function in a for loop:


Function AttachMyTablesFromClinicDB(stPath As String, stTableName As String,
stDatabase As String, Optional stPassword As String)
On Error GoTo Attach_Err
Dim td As TableDef
Dim stConnect As String
Dim dbs As Database

Set dbs = CurrentDb()

' Delete the old one, no error if it's not currently there
For Each td In CurrentDb.TableDefs
If td.Name = stTableName Then
dbs.TableDefs.Delete stTableName
End If
Next

'create the definition for the new one
Set td = CurrentDb.CreateTableDef(stTableName)
td.Connect = ";DATABASE=" & stPath & ";PWD=" & stPassword
td.SourceTableName = stTableName

' append the new one
dbs.TableDefs.Append td
AttachTablesFromClinicDB = True
td.RefreshLink
End Function
 
Top