Changing Linked Tables into Regular Tables (1 Viewer)

Hello All,
I currently have a bunch of tables that I have linked to a SQL database. For development purposes (easy of use), I'd like to turn all those tables into regular tables rather than linked so if I take the db offsite, all the data is with me. I realize there will be no updates, etc. but that really doesn't matter for my needs in this case.

Is there a script or an easy way to do this? (besides doing a manual import for a LOT of tables)

I've tried searching but I might not be searching for the correct terminology.

Thanks in advance!

Randy

Hello,

The easiest way to do this would be to use a make table query, copying the information to a new table. Deleting the linked table and then rename the new table (regular table) to match the old linked one, this way the rest of the database that refers to the table will not need to be changed.

Example, I have tblClients which is linked to a backend database.

1. Go to 'Create query in Design View'
2. Add tblClients to the query window (double click from list and close list)
3. On the query options bar at the top, click the spinner for "query type" and select "Make Table Query..."
4. From the menu that appears put in "tblClients2" as the table name and hit the ok button.
5. Add all of the fields from tblClients to the query.
6. On the query options bar at the top, click the "Run" button (the Red Exclaimation mark). And press yes to create the table and copy the records etc.
7. Exit the query, delete the linked table tblClients
8. Rename tblClients2 to tblClients

This should successfully turn your linked table into a regular table.

Make your computer act like the linked directory

Make a folder on your harddrive to copy the backend with the linked tables.

Example: c:\Datafolder\Application_be.mdb

From the DOS command line (or a simple .bat file) use the DOS subst command.

Example: subst f: c:\DataFolder where f is the path to the linked tables in your front end.

Done this way, your "links" remain and if you modify the front end (as I assume you are planning) you simply copy the new front end when done.

You can programmatically import them using VBA. Essentially, you would just cycle through the names of the tables you are currently linking and instead of linking them, import them. Look at this command:

Docmd.TransferDatabase

You provide the source, the destination, etc. and programmatically, you can cycle through all of the tables you need. This is basically identical to using File -> Get External Data -> Import and then manually selecting all the tables you want to import at once.