Auto Table Attach

[Brian Robertson]

I have an application that has a number of tables attached from a remote database. Periodically the attachement is lost and the application fails. I would like to drop & attach the tables every time the application is started. Does anyone know what functions or code that can do this ?

 

[dgoulston]

are you talkin bout linking tables......

thats what im doin at the mo, but i need to know how to check what tables exist in the database im linking to....

i can link them fine.. but jus need the user be able to check what table to link to.... as soon as i have that i can tell u how to do the whole thing.. but its all in bits without that...

 

[Brian Robertson]

Yes, I mean "link tables". In my case I know the tables that have to be linked but I want to re-link them automatically every time the application is started. I only know the manual way of linking which is File, Get External Data, Link tables, Pick database and the files will be shown for selection. I'm sure you know this already.

 

[dgoulston]

ive done it using a button on a form, but it can be put into a macro or on load....
this creates a new linked table.

Private Sub Command5_Click()

Dim tablename As String
Dim db As Database
Dim tdf As TableDef
Set ws = DBEngine.Workspaces(0)
Set db = CurrentDb()
Set tdf = db.CreateTableDef("MY TABLE") ' name of table to link

sourcedb = "C:\mydatabase.mdb" 'address of the database to link to on ur computer
tablename = "My Table" ' name of the table in the database to link to
tdf.Connect = ";DATABASE=" & sourcedb
tdf.SourceTableName = tablename
db.TableDefs.Append tdf

End Sub

hope this helps
DAL

 

[Drevlin]

dgoulston,
The following code will let you get a list of tables from the database your user has specified:

Dim tdf2 As TableDefs
Dim db2 As Database
Dim obj As Object

Set db2 = DBEngine(0).OpenDatabase(sourcedb)
Set tdf2 = db2.TableDefs

For Each obj In tdf2
Debug.Print obj.Name
Next

db2.Close

All you need to do is create a form that has a combobox that you populate with these table names and returns the information to your code after the user has selected one.

Peace