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.

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now