linking tables and relationships


P

Puppet_Sock

So I have a legacy Access database. And the tables are included in the
database file. And there are bunches of relationships defined for lots
of
the tables.

Now I want to split the tables off into a separate file, then link to
that
separate file. I have the usual reasons for wanting to do that.
Database size, sharing among multiple users, etc. Some of the
tables are getting quite large and I may put each of those in its
own separate file.

How can I get the existing relationships to apply to the linked
tables?
That is, other than manually recreating them on the newly linked
table.
I can do that, but it will take a long time. The guy who wrote the
existing database was very fond of adding relationships.
 
J

John W. Vinson

So I have a legacy Access database. And the tables are included in the
database file. And there are bunches of relationships defined for lots
of
the tables.

Now I want to split the tables off into a separate file, then link to
that
separate file. I have the usual reasons for wanting to do that.
Database size, sharing among multiple users, etc. Some of the
tables are getting quite large and I may put each of those in its
own separate file.

How can I get the existing relationships to apply to the linked
tables?
That is, other than manually recreating them on the newly linked
table.
I can do that, but it will take a long time. The guy who wrote the
existing database was very fond of adding relationships.
The Database Splitter Wizard will do all that for you. It will preserve the
relationships just fine!

If you want to do it manually, make a copy of the database. This will be your
new frontend. (KEEP THE ORIGINAL FOR SAFETY!!!)

Now create a new empty database and use External Data... Import to import all
the tables. There's an option that imports the relationships - use it.

Now open your new frontend, take a deep calming breath, and DELETE all the
tables. Then use External Data... Link to link to the tables from their new
location; compact and repair the database to recover the space formerly
occupied by the tables.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
P

Puppet_Sock

On Wed, 27 Mar 2013 08:40:46 -0700 (PDT), Puppet_Sock [snip]
The Database Splitter Wizard will do all that for you. It will preserve the
relationships just fine!
Thanks John. That`s pretty cool.
Socks
 
P

Puppet_Sock

On Mar 27, 12:15 pm, John W. Vinson
The Database Splitter Wizard will do all that for you. It will preserve the
relationships just fine!
[snip]

To give something back: Supposing you want to move the back end
file created by the Splitter Wizard. It's quite simply done. Move the
back end file. Then open the front end file. Then right click on any
table and select the Linked Table Manager. Select all the linked
tables, and update them. It will prompt you to select the new
location.
Presto laundry-o, you've got tables linked to the new location.

When you do things the way the Access developers have
prepared for, they go pretty smoothly.
Socks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top