Programmatically convert linked tables to local 2

[PCX]

I have a linked database that has 500 tables all with various names and fields.

Is there a way to programmatically read each table name and create local tables in a new (& separate) database with definitions and data matching the linked table?

I am trying to create a new local database (not linked) from the existing linked database.

Thanks

 

[Remou]

Hi PCX
I have only tested this lightly, and only on a few unimportant tables, but it might give you some ideas:

For Each tdf In db.TableDefs
If tdf.SourceTableName <> "" Then
    strSourceName = tdf.SourceTableName
    strDBName = Mid(tdf.Connect, 11)
    strDestName = tdf.Name
    DoCmd.DeleteObject acTable, strDestName
    DoCmd.TransferDatabase acImport, "Microsoft Access", strDBName, acTable, strSourceName, strDestName
End If
Next

For any casual reader, this code snippet could really mess up your database.

 

[JerryKlmns]

You could also create those local tables without importing the object (linked tabl) using instead a make table action query

Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
If tdf.Connect <> "" Then
DoCmd.RunSQL "Select " & tdf.Name & ".* Into L_" & tdf.Name & " From " & tdf.Name & ";"
'Uncomment the following line to delete the linked tables
' DoCmd.DeleteObject acTable, tdf.Name
End If
Next

 

[PCX]

Thanks guys, both methods seemed to do exactly what I wanted in two different ways.

All of this has revealed a rather nasty slow ODBC link in my source database and it is sooooo slow that both methods time-out while trying to read the tables. No problem with your coding though.

Thanks

 

[PCX]

I may have stumbled onto something.

The slowness is because some of the linked tables do not contain data.

Is there a way to exclude those tables so they don't jam up the ODBC link?

 

[Remou]

Hi again
tdf.RecordCount

 

[JerryKlmns]

Remou,

I get a tdf.RecordCount=-1 for empty and non-empty linked tables

 

[Remou]

[tt]Dim rs As DAO.Recordset
...
Set rs = tdf.OpenRecordset
rs.MoveLast
Debug.Print rs.RecordCount[/tt]

 

[JerryKlmns]

I would change that

If tdf.Connect <> "" Then

to

If tdf.Connect <> "" And Not (tdf.OpenRecordset.EOF And tdf.OpenRecordset.BOF) Then

 

[Remou]

JerryKlmns
Much nicer indeed.

 

[Remou]

Another quick thought, if there are empty tables that are not going to be converted, perhaps the conversion should be done in two stages. One to loop through and disconnect any empty tables (listing them?) and two to convert the remaining tables.