Strange Database Split

[iamchemist]

I am running a fairly complex Access 2007 multi-user Database (a .accdb Database)at a non-profit Food Pantry. A while back we split that database into backend and frontend parts. However, the split did not seem to go as expected. We did end up with forms, queries, etc. in the frontend, but the backend appeared to be the entire database and not just tables.

Recently I put the Database back together by deleting linked tables from the frontend, importing all the real tables from the backend to the frontend, and then splitting the frontend Database again. This time the split seemed to go well. We ended up with only Tables in the backend and everything else in the frontend. It also speeded up multi-user Database use about 3X. AND THE DATABASE APPEARS TO RUN NORMALLY.

The problem is that when I go to "Database Tools>Relationships on the backend Database, it shows no relationships. Obviously this can't really be true, or it wouldn't be running.

I wondered if I might remedy this by importing the System Table, MSysRelationships, from another recent backend Database, but I am told that I would need "Modify Design Permission" to even try that. I can find no way to get that permission on a .accdb Database.

I would appreciate any suggestions.

 

[dhookom]

Have you considered just recreating the relationships in the back-end?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[iamchemist]

Hi Duane,

Thank you for your reply.

I have considered just putting the relationships back, but it would be a huge task. There are probably 25 Tables and 75 Relationships in the Databaae.

 

[dhookom]

You can use code to find relationships as well as create them. I would think this might take more time to implement than manually recreating.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[iamchemist]

Hi Again Duane,

I took your advice today and tried to put the Relationships in manually.

I was surprised to see that Access wouldn't let me.

I got an error message that said that it could not establish a one-to-many relationship of one table to another, because there were entries in the "many" table that do no exist in the "one" table. I am pretty sure this is not true, but I'm not sure how to prove it.

Thoughts?

 

[dhookom]

You can create a query with an outer join that selects all records from the many table. Set a where condition on the primary key of the one table of Is Null.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[iamchemist]

Duane,

Thanks again for your reply.

I did create such a query. The desired Relationship link between these two tables is a number called "case #", and the query shows that, in fact, there are no case #'s in the "many" table that are not found in the "one" table.

The actual error I get is "Microsoft Access is unable to create this relationship and enforce referential integrity". The guidance suggests there may be data (I assume case# in this case) in the "many" table that are not present in the "one" table. My query says that is not the case.

Are there other causes for this error message, that I should look for?

 

[Andrzejek]

In the "many" table, do you have any records where "case #" is NULL?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

Can you provide the SQL view of "such a query"?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[iamchemist]

Hi Duane,

I stand corrected! I guess I wasn't a very good student before. I went back and created your suggested outer join query more carefully, and it worked. Previously I just looked at case numbers in both tables and checked to see if one table had a case# that did not exist in the other table. When I did it exactly your way I found 4 places in the many table with a null case# in the one table. Deleting those fixed the problem.

Thank you so much for being patient with this slow student! I'll print out your suggestion and store it away for the next time I find myself with this problem.