Missing Relationships after Database Split

[Eddy Sincere]

I've set up a basic multi-user document management database which correlates documents with employees. There are a few relationships that work seamlessly (see below).



However, when I split the database the relationships "disappear" (see below).





It seems that the tables/forms are working well, but I'm incredibly nervous to launch the database if the relationships are screwed up.

This problem only happens when I split the database. Also, when I try to create the relationships in the split database they won't work correctly, i.e. I can't enforce referential integrity, the relationship is undetermined, etc.

Maybe the relationship exists but just doesn't show up on the Relationships view. Is this normal or should i be concerned?

[ssanfu]

Try setting up the relationships/RI in the BE first.
Then you can look at the FE and modify the relationships

[Eddy Sincere]

Thanks for the advice. i opened the BE and the relationships were actually correct (1st screenshot). However, when I reopened the FE the relationships are still not showing (2nd screenshot) and one of the tables (Employee Status) is missing entirely.

When I enter a record via the forms the tables are updated respectively. When run reports the new records show up like normal. It's like the relationships actually exist but when I open the relationship screen it's just not showing the info.

1st (BE)



2nd (FE)

[ssanfu]

Try this first in a copy of the dB:

In the FE, in the relationship window, delete the relationships, then delete the tables. Add the tables back. The relationships should auto-connect.

[Eddy Sincere]

I deleted all tables associated with the EmployeeInfo table and the SOPPerPosition table. When I added the tables back into the relationship view they all reconnected with each other, except the EmployeePerPosition table. It still looks like the 2nd screen shot from my 1:54 PM post.

[ssanfu]

You can always manually delete any existing relationships and create new ones - in the FE and/or the BE. If yo modify the relitionships in the BE, be sure to edit/update them in the FE.

[Eddy Sincere]

The only way I can delete the relationship is to go into table design and delete the Row Source Type and Row Source Lookup values, correct? However, I can't delete them from the Relationships screen because they don't show up. If you know a different way please let me know.

Also, here's a new twist. When I hit the All Relationships button in the Relationships tab on the ribbon there are two tables two new tables created by Access. The tables look like they are taking the place of the relationships I want to enforce. Here a screen shot before (1) and after (2) the split when I pressed the All Relationships button.

Attachment 1


Attachment 2

[ssanfu]

The only way I can delete the relationship is to go into table design and delete the Row Source Type and Row Source Lookup values, correct? However, I can't delete them from the Relationships screen because they don't show up. If you know a different way please let me know.

No, in the relationship window, click on a relationship line to make it bold, then press the DELETE key. If the BE relationships display correctly, they should display the same in the FE.

Any chance you can post the dB?
Delete the data from the BE/FE, do a compact and repair, and zip it.

I think the two "new" tables are due to the look up FIELDs. I never use look up fields so I could be wrong. I would have to see the dB.

Also, (IMO) a look up FIELD in a table is a waste of time and is confusing. See http://access.mvps.org/access/lookupfields.htm

[Eddy Sincere]

Sure, I can post the DB per the method you provided. I might get it done today but will definitely have it done by tomorrow.

Sorry, but the solution you provided doesn't work because the relationship lines aren't visible. In the pictures of my previous posts the relationships between the SOPsPerPositionAndDepartment table and EmployeePositions and DepartmentToOrganization tables isn't visible. The relationships are shifting to the "new" tables Access created. Maybe you're correct in that Access created them because of the lookup field, but if it did then it is inconsistent in its application. I have lookup filed in virtually all of my main tables/forms to insure referential integrity and increase the ease with which a user enters data. Why it would create new tables for only those two relationships is kinda strange.

Of course, the table SOPsPerPositionAndDepartment is unique in my database in that it is more of a join. I take the primary key from three separate tables and link them together in that table. This enables me to run a query that links the documents, positions and departments together so that I can then run a report and which identifies the docs an individual employee needs to review.

Thanks for the lookup field article, I wasn't aware of the drawbacks. If lookup fields are evil then how do you insure referential integrity?

What I (think I) mean is, when a person inserts information into a record how do I insure they use standard nomenclature. An example would be the name of a supplier. Three different people could use three different names for the same supplier, thus referential integrity is compromised because I want only one name used in three records. With a 1 to Many relationship and a lookup field that problem goes away because they can only choose the supplier name that shows up on the list. Without the list (supplied by the lookup field) they could type a hundred different names and never "guess" the correct one.

ES

[Eddy Sincere]

NVM. I think I fixed it. Sheesh, I'm an idiot. You are correct when you say the problem was created by my lookup fields. At some point in the recent past I went through and condensed my table names, i.e. removed blank spaces, shortened names, etc. When I did this I thought the new table names would translate to the lookup fields but they didn't. They were still "pointing" to the old table names and therefore the links (and relationships) were broken. After I went through the tables and pointed the lookup fields the correct tables the problem resolved itself. I took a snip and you can see the relationships are restored and working correctly.

Even though the issue is corrected I'm still very curious about your answer to my second question. If you don't use lookup fields how do you ensure the consistent nomenclature while still having a simple/fast user interface. I'll still post the DB so you can see how I use the lookup fields. Also, I realize this is a different question than the original so if I need to start a new thread let em know. Thanks -

ES

[ssanfu]

Wonderful.. If you still want to post your dB, I will take a look at it..

As far as I can tell, look up FIELDS are only useful if you use a form in Datasheet view. I might have used a form in datasheet view once or twice, but mostly I create my "datasheets" using a form in continuous forms view. I have much, much better control of how the form looks and works.

If you have a look up field as the control source of a combo box, you still have to set up the combo box row source. So you have to set it up twice: once in the table def for the field and a second time for the combo box.
From my reading, most experienced programmers avoid the use of look up fields. And if you ever decide you need or want to use SQL Express or SQL Server (or other major dB engine), you will have to redesign your dB because none of the "big boys" have a "look up" field type.

My opinion.... your mileage may vary...

[Eddy Sincere]

Thanks for the info on lookup fields. It's interesting to me that the major players choose not to use them. They seem so handy, especially if you have people entering information into the DB who are not familiar with the content. Oh well, it sounds like they're operating at a different level than I am.

Here is the FE and BE (at least I think I attached them). I removed employee information but left the bulk of the document info. There's nothing sensitive in it so we should alright.

Thanks again for the assistance, it's really appreciated.

ES

[ssanfu]

So I've looked at your dB for a couple of days. I've got a general idea, but since I don't know what you are trying to do or your requirements, I can only comment on what I perceive as potential problems.
---------------------------------

Here is a link to "The Ten Commandments of Access" http://access.mvps.org/access/tencommandments.htm
Naming convention: Upper case/ Lower case letters, numbers and the underscore Only. NO punctuation. (see the Employee Information table)

You know how I feel about "Look up FIELDS" http://access.mvps.org/access/lookupfields.htm

You have used some reserved words as field names. This is a bad idea. It will cause you problems later on.
Here is a link to a list of reserved words in Access and SQL: http://allenbrowne.com/AppIssueBadWord.html

I don't understand why the tables "ControlledDocuments" and "SOPPerPositionAndDepartment" have a 1-to-1 relationship.
I would merge the tables.

The biggest problem I see is that every table that has a primary key, the PK is a text field. While you can have text fields as PKs,
I always use an autonumber as the PK field. And every table has a PK field.

Read this link:http://www.fmsinc.com/free/newtips/primarykey.asp
Think about it for a day. Then re-read it again.
Text fields as PKs are a lot slower.


While I am pasting links, here are two more about Autonumbers:
http://www.utteraccess.com/wiki/index.php/Autonumbers
http://access.mvps.org/access/general/gen0025.htm


You use reports where I would use forms to display info on the screen. And you use macros to open the reports.
I never use macros, only VBA. More control, validation and easier documenting.

It looks like you could use 1 query and 1 report instead of 3 or 4 queries and reports.
For example the queries: QRYSOPExpired, QRYSOPExpire90Days and QRYSOPExpire180Days
and the reports: QRYSOPExpired, QRYSOPExpire90Days amd QRYSOPExpire180Days could be combined and use VBA to set the criteria.

Which brings up.... why did you name the reports starting with "QRY"? I would use "rpt".. I'm, just saying.....

Also, when I clicked on the different search buttons, the parameter dialog box says "Enter a Name". This could be confusing, especially when searching for a document, position, department, .....


Again, sorry. I'm not trying to beat up on you...

[Eddy Sincere]

I've been on vacation all week and just got back today. Thanks for taking the time to review the structure, I sincerely appreciate it and the comments you made. Don't worry about being too negative, criticism is necessary if I'm going to improve. I only get irritated when people provide an editorial. Comments like, "That's stupid" drive me nuts because they don't tell me how to fix anything. You're response wasn't negative and provided clear and concise feedback.

I'm going to review the database during this the week and try to incorporate the recommendations. If I have any questions/comments I'll write back. Thanks again!

ES