Link to home
Start Free TrialLog in
Avatar of pcalabria
pcalabriaFlag for United States of America

asked on

Need help importing MS Access 365 relationships to a new database

My Access 365 MDB has been experiencing numerous unexplained crashing issues (while writing code) so I am attempting to import all the objects into a new database.

I have successfully imported all objects except relationships.

Using the External Databases -- New Data Source --- From Database routine... I have checked the Relationships checkbox in the Options section... but the relationships have not imported.

Please help me find a way to import the relationships from one MDB into another.

Thank you
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pcalabria

ASKER

yes, it is split.
I have 12 MDB and three LDB databases as backend s.

all relationships were created in th e FE.

I can see them when I open the FE of the original.  only a few appear in the FE of the new FE and those relationships seem to be with system files...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Well, you cannot create relationships across multiple backends, did you mean "3 mdb databases as backends".  But relationships in the BE is the only place that you can actually enforce referential integrity (RI).

Relationships in the FE will allow you to add tables to a query grid and have the relationships automatically show up, but they will not enforce RI.  Because of this, you will have to take great caution to adequately address the potential for orphaned records when using multiple backends.

Is there a reason for the BE files?  
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

real relationships enforce RI and the tables must be in the same file -- other lines are mainly for convenience and visual clarity, although they do also link fields

The 12 back-ends should be combined into one file, if possible. Then you can create proper relationships.

> "LDB databases"

the LDB file is for keeping track of users. It is created when users have an MDB open and deleted when all the users have it closed
Thank you all for you help in understanding this issue and I apologize for taking so long to get back to you.

First, let me clarify, while I typed 12 MDB three LDB databases as the BE, this was a brain-freeze moment...
I should have type three MDF databases.

So I have a total of 15 BE databases in all.. 12 of which are MS Access... three of which are MS Server 2008.

My relationships have ALL been created in the FE...
... and are mostly important from a development point of view because of points clarified by several of the experts.
… Yes, most of my relationships are across multiple BEs.

Crystal.. the reason I have not combined them into one file is that most of the BEs are between 1 and 1.4 GB, and even 64-bit Access has a 2GB limit.... and that means performance starts getting very ugly at around 1.7GB.

So my practice has been to separate out the BE when it reaches 1GB.

So my ISSUE was that the FE crashed so I imported all of the BE objects into a new blank FE Access365 table, and the relationships were mostly missing.

I still don't know whether the relationships were stored in the FE where they were created... or somewhere else.

I was hoping to learn where or how they were stored... so I could copy to the newly created FE.

As a side note... for your info only... my experience is that Access 2003 on WinXP operates and compacts flawlessly in this environment which manages billions of records... but switching to Access365 on Win10 Pro has been a nightmare because the combination simply is not stable... I get frequent "System Resources" errors and frequently lose indices while doing a compact and repair.. even with affinity set to 1 (although performance with infinity 1 is definite much more stable it DOES NOT solve the problems).

The problems I am experiencing with this question are due to the instability of Microsoft's newest products... they know about the problems... have had adequate time to fix them... but simply do not... so if anyone has any ideas how to get through to the people at MS who are in a position to fix these problems... please pass along the info... Its been a horrible experience for us :-(.

Thanks.
Thanks all