Solved

Ooops database split

Posted on 2004-04-30
13
413 Views
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

Help!

Hills
0
Comment
Question by:hills143
  • 5
  • 5
  • 2
  • +1
13 Comments
 

Expert Comment

by:woz_uk
Comment Utility
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.

Woz.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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 ?

Nic;o)
0
 

Expert Comment

by:woz_uk
Comment Utility
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.

Woz
0
 
LVL 10

Expert Comment

by:TOPIO
Comment Utility
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.
0
 
LVL 1

Author Comment

by:hills143
Comment Utility
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.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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 ?

Nic;o)
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:hills143
Comment Utility
No,
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
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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....

Nic;o)
0
 
LVL 1

Author Comment

by:hills143
Comment Utility
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?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
> 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 ?

Nic;o)
0
 
LVL 1

Author Comment

by:hills143
Comment Utility
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.
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
Comment Utility
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 :-)

Sample:
OrderTable
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 ?

Nic;o)
 
0
 
LVL 1

Author Comment

by:hills143
Comment Utility
Yeah, I think I got that.
Thanks Nico
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

744 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

18 Experts available now in Live!

Get 1:1 Help Now