A simple example of reattaching one table by VBA

[Saintor]

Can somebody provide the minimal code (DAO) to link one Access table from
an external file (A2K)?

I have seen the examples in MVPS/MS and the code seems to want to to
everything. just can't get the essential.

Is there any difference between A97 and A2K?

TIA

[Allen Browne]

The code is the same for A97 and later versions.

The basic idea is to loop through the TableDefs collection.
Skip these tables:
- no Connect string (so they are local tables),
- temp (name starting with "~"),
- system tables (names starting with MSys.)
For the others, set the Connect property, and RefreshLink.

Basic example:
Dim tdf As DAO.TableDef
For Each tdf In dbEngine(0)(0).TableDefs
If ((tdf.Attributes And dbSystemObject) = 0) And (tdf.Connect <>
vbNullString) And Not (tdf.Name Like "~*") Then
tdf.Connect = ";DATABASE=C:\MyFolder\MyFile.mdb"
tdf.RefreshLink
End If
Next

The lengthy code at the mvps site tests first to see if a reconnect is
necessary (by trying to OpenRecordset with error handling.) If it is, it
pops up the File Open dialog so the user can choose the file to connect to.
That involves an API call, and that's what most of the lengthy code is for.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:hC********************@wagner.videotron.net.. .

Can somebody provide the minimal code (DAO) to link one Access table from
an external file (A2K)?

I have seen the examples in MVPS/MS and the code seems to want to to
everything. just can't get the essential.

Is there any difference between A97 and A2K?

TIA