Link and re-link tables with VBA 1

[butcher]

i am trying to link tables using VBA

the linked tables are at:
C:\userA\csaDB\DbTs\csa_dbReplica.mdb
each install has a different User folder

user will input the 'UserA' part of the path on open
- i can deal with that bit

i assume i have to link to each table by name;
some table names are:
People;Applications;Panelists etc


i may refer to a table for the linked-table names later but for now i would appreciate any help getting started

thanks
butcher

 

[KenReay]

Hi

a snippet of code which may help, note is uses DAo so you will need a reference to DAO to use it

Set Db = CurrentDb()
DoCmd.Hourglass True
lblProgress.Visible = True
For i = 0 To Db.TableDefs.Count - 1
lblProgress.Caption = "Relinking " & i + 1 & " of " & Db.TableDefs.Count
DoEvents
Set tdf = Db.TableDefs(i)
If tdf.Connect <> "" Then
j = InStr(1, UCase(tdf.Connect), "DATABASE=")
k = InStr(1, UCase(tdf.Connect), "TABLE=")
strConnect = Left(tdf.Connect, j + 8) & txtDataMDB & ";Table=" & tdf.Name
tdf.Connect = strConnect
tdf.RefreshLink
'
End If
Next i

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[butcher]

KenReay

thanks for your rapid response

i get the gist of it

i don't immediately see a 'file path' that i can recreate for each location; maybe i will see the 'connection path' in tdf.connect when the code is running

i will let you know how i get on

thanks
butcher

 

[butcher]

KenReay

sensational stuff
works a treat

i will also use this to switch table locations for projects when i bring them to my PC and take them back to site

thanks heaps
butcher
tasmania