×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Strange Database Split

Strange Database Split

Strange Database Split

(OP)
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.

RE: Strange Database Split

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

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

RE: Strange Database Split

(OP)
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.

RE: Strange Database Split

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

RE: Strange Database Split

(OP)
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?

RE: Strange Database Split

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

RE: Strange Database Split

(OP)
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?

RE: Strange Database Split

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

---- Andy

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

RE: Strange Database Split

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

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

RE: Strange Database Split

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close