Solved Compacting & Repair (1 Viewer)

Weekleyba

Registered User.
Local time
Today, 11:47
Joined
Oct 10, 2013
Messages
586
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 19, 2002
Messages
42,971
Just FYI, a C&R sorts each table into PK sequence and writes the table to a new, empty database. This confuses people into thinking that Access actually maintains the physical tables in PK order. Not so. I've explained this many times.

In a file as in a table, Records are grouped into a physical record whose size is generally controlled by the physical storage device. Let's say your average recordsize is 200 characters and the physical recordsize on your HD is 4048 bytes. In that case, the physical record (which is the level at which I/O takes place) can hold a maximum of 20 logical records. Records are written head to tail so the second record immediately follows the first and the third follows the second etc. As each block is filled, the record gets written to disk leaving gaps at the end depending on the actual size of the logical records it contains. Say you want to update a record. The whole physical record is read into memory. You change the address and instead of being 20 characters, the new value takes up 40 characters. That means that the new record won't fit in the space that the old version occupied. If there is enough extra space at the end of the logical record, access rewrites the whole logical record fitting the new version in where it belongs and shifting everything right to accommodate the new size. But, there isn't always room in the physical record to accommodate the increased length. Now what? The database engine replaces the original record with a pointer and adds the replacement to the end of the recordset. So now record 512 might come after record 268888. The more updating you do between compacts, the more likely you are to see the results of this. The difference between updating a table and a file is how space is managed. You can't squeeze an updated record into a flat file. The only way to update a flat file is to read it sequentially and write out the updated version to a new file. Sequential files do not support update in place as dynamic files do. Sequential files only support appends.

So, the bottom line - tables and queries are unordered sets. Think of a bag of marbles. They're all in there but you can't predict the order they will come out. Access is far more predictable than SQL Server for example because the update methods of Jet/ACE are significantly less sophisticated in how they use free space than is SQL Server. However, if you want your data presented in a specific order, the only way to make that happened is to use an order by clause in the RecordSource query or specifically sort the RecordSource.
 

Weekleyba

Registered User.
Local time
Today, 11:47
Joined
Oct 10, 2013
Messages
586
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

Enthusiastic Amateur
Local time
Today, 16:47
Joined
Sep 21, 2011
Messages
14,044
Could someone have sorted the subform to the order they preferred?
 

Weekleyba

Registered User.
Local time
Today, 11:47
Joined
Oct 10, 2013
Messages
586
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

Super Moderator
Staff member
Local time
Today, 16:47
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 11:47
Joined
Oct 10, 2013
Messages
586
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!!

1656769537277.png
 

Attachments

  • DB Order Issue1 - Copy.zip
    35 KB · Views: 112

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:47
Joined
Feb 28, 2001
Messages
26,999
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

Grover Park George
Local time
Today, 09:47
Joined
Nov 25, 2004
Messages
1,776
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

Registered User.
Local time
Today, 11:47
Joined
Oct 10, 2013
Messages
586
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

Grover Park George
Local time
Today, 09:47
Joined
Nov 25, 2004
Messages
1,776
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 19, 2002
Messages
42,971
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.
I see you don't believe me despite what you are seeing for yourself. The ONLY way you can control the order in which records are presented is by sorting the recordset. ALSO, if you are not sorting on a unique column or set of columns, you will still see variations because if 5 records have the exact same values in the sort fields, there is no way to predict how they will be returned. That doesn't mean that they won't be returned in the same order 9 times out of 10 for Jet/ACE. You are more likely to see differences when working with a more sophisticated database engine.

Yes this is fundamental to how relational databases work. Tables and queries, by definition, are unordered sets. People who don't actually study relational database theory wouldn't learn this from Access except by accident as you have mostly because if they bind their forms to tables or queries with no order by as you have, Access saves them if they compact frequently. It is only if they happen to displace one of the early records with an update that this different order becomes noticeable.
 

Weekleyba

Registered User.
Local time
Today, 11:47
Joined
Oct 10, 2013
Messages
586
I’m a believer now.
Thank you for stirring me in the right direction.
I do appreciate it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 19, 2002
Messages
42,971
Glad to be of help. I've encountered experts who never knew this. My experience goes back to well before the introduction of Access in the early 90's. I've been using various relational databases since the dark ages:) so I am more in tune with how they work internally.
 

GPGeorge

Grover Park George
Local time
Today, 09:47
Joined
Nov 25, 2004
Messages
1,776
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

Immoderate Moderator
Staff member
Local time
Today, 11:47
Joined
Feb 28, 2001
Messages
26,999
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."
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 19, 2002
Messages
42,971
Along those lines - Access is designed to be used by novices which need a whole lot of help so Access is very helpful. Most of the time, it gets things right. Even in this case, it gets it right. it would be wasteful to always sort the recordset if YOU didn't want it sorted. It's just that there is no place that I've ever found that explains that Access isn't sorting the recordset when you open one from a table to view the data.
 
Last edited:

GPGeorge

Grover Park George
Local time
Today, 09:47
Joined
Nov 25, 2004
Messages
1,776
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. :rolleyes:
 

Users who are viewing this thread

Top Bottom