Coverting linked tables into local tables

[Jess]

I have an Access application whose tables are linked to a remote SQL server
database –the data resides in SQL server. I do not have control over the
backups.


I would like to create an Access database (a new database) whose tables are
local –reside in Access- and contain all the data in the linked tables. This
database should also contain the other databases objects: forms, modules,
reports, etc.

Is there an easy way to accomplish this?

 

[John W. Vinson]

I have an Access application whose tables are linked to a remote SQL server
database –the data resides in SQL server. I do not have control over the
backups.


I would like to create an Access database (a new database) whose tables are
local –reside in Access- and contain all the data in the linked tables. This
database should also contain the other databases objects: forms, modules,
reports, etc.

Is there an easy way to accomplish this?

I agree with Pieter that this is probably wasted effort: unless your SQL DBA
is extraordinarily inept, s/he should have the data thoroughly backed up
already! However, I do know that SQL DBA's can be hard to communicate with
(I've been one, briefly... <shiver>)...

What you can do is create a new Access database. Link to the ODBC connection
to your SQL server. You can select each table (one at a time), Copy it, and
Paste; when you paste, select "Design view only" and specify a new name (if
your linked tables all start with dbo you can simply omit that).

It should be possible to automate this process by looping through the
Tabledefs collection but it would be more code than I'd be comfortable dashing
off untested, and more time than I'd want to spend on a donated-time basis
writing it and testing it.

 

[John Spencer]

If you are using a DSN to link to the SQL server tables then the easiest way
is to:
== Create a new db
== Select Get External Data: Import form the menu
== Select ODBC Databases from the Files of Type drop down
== Select the relevant DSN from the dialog
== Select the tables you want to import

Once this is done, you will need to set up the relationships and indexes on
the tables and rename the tables as appropriate (before setting up the
relationships)

After that you can import the forms, modules, reports, etc from whatever
source contains them.

I hope you have a good reason for doing this.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County