datAdrenaline
AWF VIP
- Local time
- Today, 09:28
- Joined
- Jun 23, 2008
- Messages
- 697
Hello Pat ...
I will first start off and state that in this discussion I want to make sure that it is known that I enjoy technical discussion like this and the tone in my mind whilst writeing is simply conversation ... with no dis-respect or malice or any other negative connotation attached ... I just wanted to get that out there so those reading (and you) know that if my commentary if read as negtative, it was/is not meant to be ...
...
----
>> Keep in mind that jet does not reuse waste space when records are deleted or moved. This space is not recovered until the database is compacted. <<
Yep, I know that bit of information very well, but it is good information to add.
>> You can't make that as an unequivical statement.<<
Well ... yes we can, no matter what the number, or size of the records, or how many records have been deleted/added, or how many times the db is compacted. A characteristic of a dbOpenDynaset recordset is that it will be in PK order if an ORDER BY is not specified in the SQL statement that retrieves those records, and a PK exists. A Table shown in datasheet view is merely a visualization of a dbOpenDynaset recordset, so the records will be revealed in PK order through the datasheet view. Also, a forms recordset defaults to dbOpenDynaset too. Also, rememember that a TableDef and Form object (as well as other objects) have an OrderBy property (not to be confused with the ORDER BY clause of an SQL statement), and for the records to be visulized in PK order, this property must not be set on the respective object, if it is, then Access applies that OrderBy request before the records are visualized (but - I am pretty sure - after they are retrieved). When you use a DAO recordset object variable in VBA, you will get the same result; the recordset will be in PK order IF no ORDER BY clause is used, and the recordset type is dbOpenDynaset (which is the default, if it can be acheived).
It is important to note that the default characteristic of PK ordering for a dbOpenDynaset recordset has nothing to do with physical order of the records on the hardrive, thus the indifference to compacting, adding, deleting, etc ...
...
Now ... with respect to any other recordset type WITH OUT an ORDER BY clause: we are in 100% agreement in the sense that the ordering of records of in the recordset can not be gaurenteed unless an ORDER BY clause is present (or an OrderBy property of an object is set and applied). But I will add that the order of the records in a dbOpenSnapshot recordset appears to be consistent between compactings. Even when I forced a record to return the "Record is too large" error, the order of dbOpenSnapshot recordsets remained the same through a variety of edits and deletions prior to and after my 'tracking' record, as well as edits on the tracking record itself. But even with that consistency shown and the rigors I sent my data through, I can not express the same level of confidence that I have declared with the dbOpenDynaset/PK order scenario.
....
Conclusion:
- A dbOpenDynaset typed recordset will be in PrimaryKey index order if an ORDER BY clause has not been specified.
- A non-dbOpenDynaset typed recordset will be in an order that is unknown order if an ORDER BY clause has not been specified. However, the order seems consistent between compactings.
- A dbOpenDynaset typed recordset WITH OUT a PrimaryKey index, will follow the same ordering as a non-dbOpenDynaset recordset.
-----------
The emperical data of this post was gathered with a table having the following structure:
tblLineStops
------------
LineStopID (dbLong)
HMIIdentifier (dbText/50)
ProcessID (dbLong)
LineStopEffectID (dbByte)
StartTimestamp (dbDate)
Duration (dbCurrency)
TotalStopTime (dbCurrency)
EventID (dbLong)
ComponentID (dbLong)
CauseID (dbLong)
Notes (dbText)
BelongsTo (dbLong)
IsMajor (dbBoolean)
GroupLeaderID (dbText/10)
DateMajorClosed (dbDate)
TerminalID (dbText/20)
CreationDate (dbDate)
DateComplete (dbDate)
Field1 (dbText/255)
Field2 (dbText/255)
Field3 (dbText/255)
Field4 (dbText/255)
Field5 (dbText/255)
Field6 (dbText/255)
Field7 (dbText/255)
Field8 (dbText/255)
Field9 (dbText/255)
Field10 (dbText/255)
Field11 (dbText/255)
Field12 (dbText/255)
Field13 (dbText/255)
Field14 (dbText/255)
Field16 (dbText/255)
Field17 (dbText/255)
Field1 through Field17 were used to modify a records' total byte size. If Field1 - Field16 had 255 characters, Field17 could NOT hold 255 characters --- the 'Record is too large' error was typically raised after about 25 characters in Field17.
I will first start off and state that in this discussion I want to make sure that it is known that I enjoy technical discussion like this and the tone in my mind whilst writeing is simply conversation ... with no dis-respect or malice or any other negative connotation attached ... I just wanted to get that out there so those reading (and you) know that if my commentary if read as negtative, it was/is not meant to be ...
...----
>> Keep in mind that jet does not reuse waste space when records are deleted or moved. This space is not recovered until the database is compacted. <<
Yep, I know that bit of information very well, but it is good information to add.
>> You can't make that as an unequivical statement.<<
Well ... yes we can, no matter what the number, or size of the records, or how many records have been deleted/added, or how many times the db is compacted. A characteristic of a dbOpenDynaset recordset is that it will be in PK order if an ORDER BY is not specified in the SQL statement that retrieves those records, and a PK exists. A Table shown in datasheet view is merely a visualization of a dbOpenDynaset recordset, so the records will be revealed in PK order through the datasheet view. Also, a forms recordset defaults to dbOpenDynaset too. Also, rememember that a TableDef and Form object (as well as other objects) have an OrderBy property (not to be confused with the ORDER BY clause of an SQL statement), and for the records to be visulized in PK order, this property must not be set on the respective object, if it is, then Access applies that OrderBy request before the records are visualized (but - I am pretty sure - after they are retrieved). When you use a DAO recordset object variable in VBA, you will get the same result; the recordset will be in PK order IF no ORDER BY clause is used, and the recordset type is dbOpenDynaset (which is the default, if it can be acheived).
It is important to note that the default characteristic of PK ordering for a dbOpenDynaset recordset has nothing to do with physical order of the records on the hardrive, thus the indifference to compacting, adding, deleting, etc ...
...
Now ... with respect to any other recordset type WITH OUT an ORDER BY clause: we are in 100% agreement in the sense that the ordering of records of in the recordset can not be gaurenteed unless an ORDER BY clause is present (or an OrderBy property of an object is set and applied). But I will add that the order of the records in a dbOpenSnapshot recordset appears to be consistent between compactings. Even when I forced a record to return the "Record is too large" error, the order of dbOpenSnapshot recordsets remained the same through a variety of edits and deletions prior to and after my 'tracking' record, as well as edits on the tracking record itself. But even with that consistency shown and the rigors I sent my data through, I can not express the same level of confidence that I have declared with the dbOpenDynaset/PK order scenario.
....
Conclusion:
- A dbOpenDynaset typed recordset will be in PrimaryKey index order if an ORDER BY clause has not been specified.
- A non-dbOpenDynaset typed recordset will be in an order that is unknown order if an ORDER BY clause has not been specified. However, the order seems consistent between compactings.
- A dbOpenDynaset typed recordset WITH OUT a PrimaryKey index, will follow the same ordering as a non-dbOpenDynaset recordset.
-----------
The emperical data of this post was gathered with a table having the following structure:
tblLineStops
------------
LineStopID (dbLong)
HMIIdentifier (dbText/50)
ProcessID (dbLong)
LineStopEffectID (dbByte)
StartTimestamp (dbDate)
Duration (dbCurrency)
TotalStopTime (dbCurrency)
EventID (dbLong)
ComponentID (dbLong)
CauseID (dbLong)
Notes (dbText)
BelongsTo (dbLong)
IsMajor (dbBoolean)
GroupLeaderID (dbText/10)
DateMajorClosed (dbDate)
TerminalID (dbText/20)
CreationDate (dbDate)
DateComplete (dbDate)
Field1 (dbText/255)
Field2 (dbText/255)
Field3 (dbText/255)
Field4 (dbText/255)
Field5 (dbText/255)
Field6 (dbText/255)
Field7 (dbText/255)
Field8 (dbText/255)
Field9 (dbText/255)
Field10 (dbText/255)
Field11 (dbText/255)
Field12 (dbText/255)
Field13 (dbText/255)
Field14 (dbText/255)
Field16 (dbText/255)
Field17 (dbText/255)
Field1 through Field17 were used to modify a records' total byte size. If Field1 - Field16 had 255 characters, Field17 could NOT hold 255 characters --- the 'Record is too large' error was typically raised after about 25 characters in Field17.
Last edited:
