What could break relation and indexes? (1 Viewer)

Hello!

I am having some problems with a simple 2 related tables:
1. Order (parent)
2. Order-Details (child)

In Order table I have an OrderNo Autonumber field that is also the primary key.
The tables are in "1 to many" relation with "Enforce Referential Integrity" checked.
I have one form in Master/Details scenario and one form that is using a query that use data from both tables and which is used for modify one specific field in the "child table" (Order-Details).

Even in this simple configuration, with only 2-3 concurent users the database will broke strangely:
- PK missing in the parent table
- Relation dissapeared
- on compact and repair the MSysCompactError table reads 3 errors:
ERR:-1017 - Could not find field 'Description'. (in parent table)
ERR:-1053 - Could not find field 'Description'. (in parent table)
ERR:-1522 - No unique index found for the referenced field of the primary table. (in child table)
- on the child table will remain orphaned records
- on the parent table will remain an blank autonumber field with a lot of "####" on some other fields.

I have checked the PC's hardware, networking... but I cannot understand what in ACCESS could break the tables in this way.

Any sugestion on how to catch the problem?

Kind regards,
Lucian

Please post a copy of your database in zip format so readers can see what you have in context.

Hi jdraw,

Thank you for your suggestion, but I think that would be more difficult to understand, firstly because all the field names are not in english, and is a "split" configuration with Front End/Back End and the FE is also "not in english".

Anyway I could not reproduce the problem nor on my LAN nor on the client LAN, the problem just appears from time to time weekly or even monthly.

So I would like to find in the first place one reason that could break the database in this way, if there is one, or if someone bump on this kind of problem and maybe found a solution.

If you think will be usefull, I could try to simplify the database only to 3-5 fields per table and translate them in english just to undersand the logic.

there is no need to add both parent and child
table in a Query.

Create a query for the master table and make
this the record source of the form.

create a query for the child table and make a form
out of it.

add the child form to the parent form as subform.

create link master/child field on the subform.

link master fields: OrderNo
link child fields: <the fk of the child>

Hi arnelgp,

Thank you for your message. I need that special query with fields from parent and child tables because the client need a "special view", even some of the fields that came from parent table will have repeating values like this:

O.OrderNo, O.ClientName, OD.PartNo, OD.QtyOrdered, OD.QtyReceived

where O=Order, OD=OrderDetail

And this "special view" form with this query is used to quickly complete only the QtyReceived field from the child table.

The other master/child forms as you described them, are connected directly to the tables (no queries) and are used to fill in the initial client order. Only when receive the parts will use that "special query" to complete the received quantity.

When OD.QtyReceived = OD.QtyOrdered for all the OD.PartNo of the same OrderNo that order will be marked as "ready for client".

And somewhere on that chain, the record from the Order table will dissapear, leaving orphans in the Order-Detail table and also deleting the relation between tables.

I recommend you tell readers exactly what you are trying to accomplish in plain English. Better still mock up an example to show us. Don't guess at how it could be done until readers understand WHAT you need.

Show some examples or screen captures of "parent table will have repeating values like this"

Thank you for suggestion.
I'll try until tomorrow to simplify my database and attach a sample so maybe someone could point me where could be the problem.

Well that isn't exactly what I was suggesting.

Can you describe to readers in 5-6 lines of simple, plain English what business process(es) are involved?

Here is a sample of the sort of info and detail requested:

ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

When you "break" relationships like that and you have an FE/BE split, it ALMOST sounds like one of the tables is FE and the other is BE, and further that the network between FE and BE is not stable. Is there a chance that either of those conjectures is true?