[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Ooops database split

Posted on 2004-04-30
Medium Priority
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
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
  • 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.

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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 1000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

649 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