• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

Ooops database split

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


  • 5
  • 5
  • 2
  • +1
1 Solution
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.

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 ?

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.

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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.
hills143Author Commented:
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.
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 ?

hills143Author Commented:
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
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....

hills143Author Commented:
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?
> 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 ?

hills143Author Commented:
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.
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 ?

hills143Author Commented:
Yeah, I think I got that.
Thanks Nico
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now