Question about field size

[Pat Hartman]

I imagine if a changed record was written in place you would see the data change. If you write the record at the end of the current file, then the original record might not be cleared. It would (or could) just be left in position with the original data, but there would be no pointer to it. Maybe the space couldn't actually get reused until after a compact operation.

Of course you don't see the record move. You are not looking at the hard drive. The recordset is instantiated in memory and so it it just adjusted so the record stays where it was originally. If you change the PK of a record does it automatically move? No. You are seeing an image. You see the affects after you close the table and reopen it in DS view. You would need to sort the recordset to see the row with the ID move. Record 2 is no longer between 1 and 3. If you scroll, you will find it later in the recordset.

 

[gemma-the-husky]

Hmm. I was thinking that if you looked at a database file with a hex editor, you might be able to inspect the physical order. Therefore you might see both the old data which is now no longer pointed to and can now be recovered, and the new record which has just been created, but I'm not really sure.

 

[Pat Hartman]

I was thinking that if you looked at a database file with a hex editor

In theory, yes for Jet and ACE because they are standard files. But, you'd have to do a lot of work to create data you could "see" with the hex editor and then figure out how Jet and ACE encode pointers.

 

[The_Doc_Man]

I will agree that with a good hex editor, you could probably decipher something meaningful out of an Access database file.

However, please be aware that in general it is a violation of proprietary software End User License Agreements to reverse-engineer their products. It is unclear that MS could claim the database file but they probably COULD take legal action for publishing proprietary information. The potential lawsuits are probably more trouble than it is worth.

Please also note that I have to make that disclaimer in the forum, not privately but publicly, in order to protect forum interests. The forum members can do what they want, but acting in my capacity as a moderator on the Access World Forums, I want to have it on record.

The forum officially doesn't condone reverse-engineering proprietary formats.

Jon didn't put me up to saying this... I learned this rule in the Navy as a contractor. Jon, you can thank me or spank me later.

 

[Pat Hartman]

I agree with your official statement. However, if some folks hadn't poked around, we would not have the tools we have that can take advantage of the internal structure. MS certainly never bothered to create them for us.

 

[The_Doc_Man]

I agree with your official statement. However, if some folks hadn't poked around, we would not have the tools we have that can take advantage of the internal structure. MS certainly never bothered to create them for us.

If it weren't for Access being Microsoft's red-headed step-child, we would HAVE a lot of those tools. But it is a legal mine-field to publish too much despite the potential utility of such publication.

 

[Pat Hartman]

Good thing that the people who poke around don't publish their results. They use them to make good tools for us.

 

[gemma-the-husky]

It was more just clarifying how the data changed, in the light of our discussions. If you only have 2 records in the database, and then edited the first record, would you now see 3 records, or would you still see 2. Just academic interest.

 

[The_Doc_Man]

Dave, to the best of my understanding, if you updated a record (and for the sake of argument, did so in a way that made a string field longer by 1 or more bytes), then this is what would happen.

JET/ACE would make a new, free-standing record that is not yet part of any table, then would copy fields one at a time in fielddef order, probably applying the update to the string field this record in passing. It would put this record at the first free spot in its current virtual memory area.

Next, it would perform a transaction style operation where both of the next two steps make it or neither makes it.
Thread the new, updated record at the end of the list of records that are in the table
Unthread the old record and close up any opened links needed to rejoin the records that had preceded or followed the now removed record.

At this point, you see the same number of records you started with but they are in a different internal order with the updated record apparently at the end of the list of table records. There is now an unthreaded record "floating" in the memory that was formerly part of the table, contributing to bloat until such time as the next Compact & Repair does its thing.

Is that what you were asking with your question?

As food for thought, considering corruption issues, if you have a power glitch between the point where you were unthreading the old record and rethreading logically adjacent records, this is when a table gets corrupted, because the links between some of its members are now broken.

 

[gemma-the-husky]

@The_Doc_Man

At this point, you see the same number of records you started with but they are in a different internal order with the updated record apparently at the end of the list of table records. There is now an unthreaded record "floating" in the memory that was formerly part of the table, contributing to bloat until such time as the next Compact & Repair does its thing.

Yes, but I assume the now unlinked "floating" record is still in the disc file, and visible to a sequential read of the disc file, but the space occupied by that record might be available to be overwritten. Maybe it's not available to be overwritten and all new records get written to the end of the file, and the space for logically deleted records is only made re-usable by a C&R.

 

[The_Doc_Man]

The space is not available to be overwritten. Windows-based programs that use their standard dynamic memory routines cannot do that.

 

[Pat Hartman]

If you only have 2 records in the database, and then edited the first record, would you now see 3 records, or would you still see 2. Just academic interest.

I have a lot more experience with the internals of mainframe based I/O operations. I am not sure why Jet/ACE would work any differently except in its specific method of allocation of free space within the body of the file. The point of free space is to allow logical records to grow but to still be rewritten within their original physical record whenever possible. Processing pointers becomes very inefficient.

If, as Doc suggested, updated records are always written to the end of the physical file space and left there, updating would always jumble records and you would see this if you opened a table in ds view that had NEVER been sorted. The problem is that once you sort a table, Access remembers that and assumes you always want it sorted so you can never tell with certainty what has happened. So, any testing you do must avoid ever sorting the table itself or compacting the database because compacting the database always rewrites the records in PK order.

I don't know if Jet/ACE are multi threaded but the concept of a table being an unordered set is much more apparent with SQL Server and you will see misplaced records more frequently. I/O always works on physical records not logical records. Physical records are always fixed in length and in the PC world, size seems to be dictated by the device.

 

[The_Doc_Man]

I don't know if Jet/ACE are multi threaded

There is an old article in the forum were we looked this up. JET/ACE may or may not be multi-threaded BUT each session's connection IS single-threaded. I recall that I found an article and linked to it that suggests that AT BEST the innards of JET/ACE could have a separate internal cleanup thread to police its own working space. I may have to do a deep forum search to find that. However, except for that cleanup thread, Access from end-to-end IS synchronous unless you use API or addressing tricks to multi-thread it. There is no inherent syntax to multi-thread a native Access database. NOTE: When the back-end is NOT native Access but rather is an active SQL engine of some type, it certainly CAN be multi-threaded. Then it would depend on the vendor.