Little problem when exporting Linked Tables From 1 mdb File to Another mdb file....

[jbpbgame]

Lets say I have DBase1.mdb file where tables are linked from an sql server and DBase2.mdb where I want the tables from DBase1.mdb be transferred as local table...

I want to accomplish this using vba...and not manually where I copy the tables and paste it to another file getting the data and the structure locally...


I already managed to be able to export linked tables to another file...but the problem the exported tables is also a linked table one...


heres my function...

Function Export2Access()
DoCmd.TransferDatabase transfertype:=acExport, _
databasetype:="Microsoft Access", _
databasename:=FILEPATH, _
ObjectType:=acTable, _
Source:=TABLENAME, _
Destination:=DESTABLENAME, _
structureonly:=False
End Function



Thanks in advance....

 

[boblarson]

Create a query on the table and export the query results as a table.

 

[jbpbgame]

That would do... but what if i want the function to loop itself so that all linked tables are automatically transferred <lets say I have 50+ linked tables>... also is there another way that i dont need to create a query???

The problem in using a query is that the exported result table did not have the structures of linked table one... like the primary keys and indexes...I would want to prevent that...

 

[JamesMcS]

If your tables are numbered sequentially you could use a for loop, the docmd.runsql statement to run your make table queries > dbase 2, then another SQL statement to set the primary key and indexes (CREATE INDEX Indexname ON <tblname>(<field>) With PRIMARY)