Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Ooops database split

Posted on 2004-04-30
Last Modified: 2008-03-10
I created a database with tables, reports forms etc.
Then I split it.  Now I don't have relationships in my FE or my BE.
I have a copy of the DB before I split.
How can I import the relationships from my backup to my backend?
I tried importing the MSysRelationships table, but it just creates another relationships table and I can't delete the old relationships table.

I am using Access 2K


Question by:hills143
  • 5
  • 5
  • 2
  • +1

Expert Comment

ID: 10958135
Did you split your tables manually or did you use the wizard?

If you used the wizard it should create the existing relationships from the original database for you.

If you split it manually you need to select options from the import objects dialog box and ensure the relationships check box is ticked, which it should be by default.

LVL 54

Expert Comment

ID: 10958137
Relationships are normally kept in the backend by design.

I normally just maintain the relationships in the backend and create a print as reference.

Why do you need it in the fornt end ?


Expert Comment

ID: 10958174
Follow on from previous message.

To import your relationships from your original database follow the instructions below:

1. Delete existing tables from backend database
2. Right click the database window in backend database and select import.
3. Select the original database
4. In the Import Objects dialog box select all tables for import
5. Press Options and ensure relationships check box is ticked, usually ticked by default.
6. Press OK

This should now re-import your tables with the relationships.

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

LVL 10

Expert Comment

ID: 10963154
Just a comment, if you're using the Switchboard utilities is best to keep that table in you front end database
that  way if you make changes to the switchboard that only affect the front end you will not be concerned about  unecesary changes to it.

Author Comment

ID: 10967530
I don't want relationships in my front end.  I was just confused why I couldn't find them anywhere.
I used the wizard to split.

I want to keep the data in my back end if possible.
The data that is in my backend is not in my backuo.
If I try to import relationships and click on the options button, there is no option for relationships.
I went to file get external data.
LVL 54

Expert Comment

ID: 10967583
As stated, this is by design.
You can create the relationships in the frontend, but only with "single" connectionlines.
The referential integrety is "guarded" by the backend with the data.
Is ofcourse logical as it could also be an ODBC database (e.g. Oracle or MS SQL) with it's own security and relations.
The database will also return an error when you try to enter data that's not allowed according the specified relationship constraints.

Bit Clearer ?


Author Comment

ID: 10967602
Are you saying that if I had relationships and used the splitter wizard I shouldn't see relationships in my backend relationships window, but there is still referential integrity etc.  I understand that I can add relationships to the front eind if I wish, but I subscribe to the theory that they should be in the back end.  
Do I have to add them all again?
Can they be imported without losing existing data?
Or, are you saying they there but invisible?

P.S. The only thing I have in backend are tables and the only thing in the front end are forms reports and modules
LVL 54

Expert Comment

ID: 10967720
When you did define them with in the relationship window (including the referential integrety) they should still be there in the backend.

Sometimes Access does lose the location information where to show the table(s), but pressing the Grid like button will always reveal all tables and all defined relations.

In the frontend you'll have to recreate the relationships, but I wouldn't as they won't reveal the 1:N relations....


Author Comment

ID: 10967759
If I don't recreate the relations in the front end, it will still be like they are there won't it?
I would prefer users not seeing the relations.

As far as the backend. I clicked the grid, or the show all button, and nothing appeared.
Also, in my MSysRelationships table in the backend, there is no data.
I really don't think there is a single relationship in my backend except for what I have created since the split.

And I think I just realized why.
I think I lost my relationships in a transfer of tables a long time ago.

Anyway I can import table definitions and relationships from one DB and data from another?
LVL 54

Expert Comment

ID: 10967884
> If I don't recreate the relations in the front end, it will still be like they are there won't it?
Yes :-)

>Anyway I can import table definitions and relationships from one DB and data from another?
When you select all tables (in one operation) the relations will be moved too, but when you only select part of them I guess Access will drop the "wrong references" as there's otherwise a relation with a lose end....

That  "mass import" will however lose the "location" information, thus the relationship window's layout is lost :-(

The fact or the tables have data or not won't intefere, however when you delete a table and insert one with the same name, the relations will be lost. To preserve that just empty the original table and append the new data from the other database.
This can be tricky when you have autonumber keys and 1:N relations like Order : OrderDetail.

Need more info on that ?


Author Comment

ID: 10967901
Yes please. I have never appended data before.
The autonumber stuff is not that important as all my autonumbers are transparent to the user and I have a small amount of datat that is only for testing.  I just don't want to re-enter my test data.
LVL 54

Accepted Solution

nico5038 earned 250 total points
ID: 10968508
When you have autonumber fields you'll no be able to insert the "old key" as that can already be present.
As you can have related records in "other tables depending on such an autonumber" key you'll have to add a field to the table you want to append to hold the "old autonumberkey". After that and importing the table that's having a reference to the old key you can apply an update query to reset the "wrong reference" in the "other table depending on such an autonumber key".

Just read it several times :-)

OrderID 0001

OrderDetail table
OrderID 00001
DetailID 00022

Appended order in new table:
OrderID OldID
1023     0001

Now the OldID can be used to change the OrderID in the OrderDetail table

Getting it ?


Author Comment

ID: 10968624
Yeah, I think I got that.
Thanks Nico

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question