Solved Compacting & Repair

[Weekleyba]

I had a strange thing happen in my split database.
Some of my subforms records were not in order. The parent form was fine but the subform would show the last record first.
Both forms had tables for record sources. There was no sorting on the forms.
I tried everything..... a couple hours actually of work, trying to fix it.

A finally brought the tables back in the to database to work on it as a non-split database.
Once I compacted it, everything was fine.
I went back to the split database and compacted, but it still had the problem.
I tried looking a many things and compacted the database many times as I was working on it.

A few minutes ago, everyone left so no one was in the split database, just me.
I compacted again and....the problem went away!

Are there some things that 'Compact & Repair Database' does not do when the Back End is being used by others in a split database?

 

[Weekleyba]

I understand in general, but not sure I understand in relation to my database.
In my database:
Parent Form (F1) - Record Source - Table1
Subform (F2) - Record Source - Table 2
All my tables have primary keys and the tables present the data by record...in order. Always the same order.
The two forms are linked by the primary key from Table1.

So my understanding is, with no other sorting or ordering applied, when you open the F1, it will display the first record...always.
That first record in F1 links to the first record in F2. You can then step through the records, in order.
This is how everything is working for me.
Is this not the proper way to set up F1 and F2? Do I still need a query or specific sort for the RecordSource?

If the answer is no, then I believe my question was not answered.
If the answer is yes, then I was led astray a long time ago on how to set up Parent and Sub forms. (Which could totally be the case..yikes)

 

[Gasman]

Could someone have sorted the subform to the order they preferred?

 

[Weekleyba]

No, that didn’t happen.
When trying to figure this out, I took the two tables and drug them into a completely new database. So the new database only had the two tables in it. No forms, queries, modules, vba, etc.
I then created a query and joined the two tables to the PK of table1 (one to one). I grabbed only the PKs of both tables and ran the query. Nearly all records sorted correctly. But the newer entries would have table2s PK out of order.
instead of being:
318, 319, 320, 321 like would be expected,
It would be
321, 318, 319, 320
It would place the latest one first.
Newer, meaning within the last week.
But when I ran the Compact and Repair on the new database, it corrected it.
So back to the main database. While a user was in the database (causing a locked file in the BE) if I ran C&R from my FE, the issue remained.
If I deleted the links to the tables and pulled the actual tables into the database and then ran C&R, it corrected the problem.
So it would appear to me that C&R does not fully work on the BE while another user is in it.
Am I correct or is something else going on that I’m missing?

 

[CJ_London]

instead of being:
318, 319, 320, 321 like would be expected,

Only you are expecting that
In addition to Pat's comments about how data is stored, I'll add that when a query is executed it uses the most efficient way to retrieve the data - which may not be the order in which records were created.

So it would appear to me that C&R does not fully work on the BE while another user is in it.

you cannot compact/repair when another user is using it.

Am I correct or is something else going on that I’m missing?

nothing is going on - just you expecting something to work in a specific way.

Point is, if you want a specific order, you have to specify that order

 

[Weekleyba]

Thanks guys. I'm a bit blown away right now. I truly did not know this before. I was never taught that I needed to specify the order between a form and a subform once they were linked with the PK. I though it would always pull the records in order.
So C&R does not work on the BE when someone is in the database. I didn't know that either but makes sense to me.

For fun, attached is a DB that shows the problem.
Run the query and see that it does not sort correctly. See image below.
But, Compact and Repair it and it will then run correctly.
I still find this strange....

Thanks again for all your expertise!!

 

[The_Doc_Man]

Is this not the proper way to set up F1 and F2? Do I still need a query or specific sort for the RecordSource?

Your answers are "Yes (it is not the proper way)" and "Yes (you need a query)."

Remember that it doesn't matter whether you are displaying records on a form or a sub-form. Both recordsets are derived from tables that have no inherent ordering. It is the query with an ORDER BY clause that imposes order on the chaos that is an Access table.

Access table ordering is based on SET theory for which sets are like a bag of marbles. If you do any updating of records, or if you can insert new records in arbitrary order, those changed or new records typically go to the end of the table regardless of whether there is a primary key associated with the records. For INSERT, that may seem right, but for UPDATE, the updated record goes to the end of the table regardless of the keys.

As Pat has stated, the C&R will copy the records in order of their PK fields, so it LOOKS like it is doing a sort for you. But in fact, the reason it does what it does is because that C&R must skip deleted records. It knows a record with a PK has been deleted by the removal of the record from the PK's index. I.e. C&R follows the PK index to find non-deleted records. That means that the sort performed by the C&R is incidental to the process, a by-product of the method. A C&R on a table without an index will return the records in whatever order that table had at the time. Please note that I am not being specific on exactly how the C&R knows that the indexed record has been deleted - only that it DOES know. I.e. could be a flag; could also be an actual deletion. Microsoft doesn't tell us the details.

It is true that if you have parent/child links set up for the form and sub-form, the order of the records in the parent WILL have an effect on the records in the child - but if there is a secondary sort field for the child form, you're on your own there.

 

[GPGeorge]

The safest course of action is not to assume ANYTHING about the order of records in any table in any situation.
The safest method is to assume that YOU are responsible for telling Access how to proceed, and for doing so in excruciating detail.

If you need records to appear in a specific sequence, apply that sequence directly and explicitly. ORDER BY is your friend.

 

[Weekleyba]

I’m still a bit shocked that I was never taught or read something on this before. This is foundational to setting up a DB, is it not?
I’m also shocked that my DBs have been working fine (up until this instance) without me specifying sorting when using tables as a record source for forms and reports.
Looks like I have some work to do on the four DBs that I currently manage for my department.
Thanks again guys for all your input.
Please share any other details related to this topic.

 

[GPGeorge]

I guess people who have been in the relational database application business for years take it for granted and don't necessarily think it is important to mention. On the other hand, I've seen dozens of discussions on this topic, so it's not been kept a secret.

Things do "work" with or without sorting in many cases, but the point is that if you require a specific sequence, you must specify that sequence, rather than rely on good luck or coincidence.

 

[Weekleyba]

I’m a believer now.
Thank you for stirring me in the right direction.
I do appreciate it.

 

[GPGeorge]

A couple of years ago, I coined a saying that I think can apply here, although I was originally thinking of references in VBA.

Ambiguity is required in poetry, humor and politics.
Ambiguity is anathema in relational database applications.

When anything is left up to the database engine to decide, i.e. if your instructions to the database engine are ambiguous, the results are unpredictable.

Sometimes people "get a joke", and sometimes they do not.

Left to its own devices, sometimes Access interprets ambiguous instructions "correctly", and gets the sort order "right", but once in a while it doesn't.

 

[The_Doc_Man]

When anything is left up to the database engine to decide, i.e. if your instructions to the database engine are ambiguous, the results are unpredictable.

Actually, not technically true for any queries where there is truly an ambiguity. It is EASY to predict. The query will not be updateable.

I know this to be true because I finally got my crystal ball back from the dry cleaners. I shook it real good before I asked it what Access would say for an ambiguous query. It told me clearly, "Situation hazy, ask again later."

 

[GPGeorge]

Actually, not technically true for any queries where there is truly an ambiguity. It is EASY to predict. The query will not be updateable.

I know this to be true because I finally got my crystal ball back from the dry cleaners. I shook it real good before I asked it what Access would say for an ambiguous query. It told me clearly, "Situation hazy, ask again later."

I knew I was stretching the analogy to its outer limits.