Physical record number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to read the physical record number of any given record?

If I can prove that a sertain number of records were entered all at one
time, I can fix a little problem I am having. I can't say more about the
problem.

Scott Burke
 
There is no such thing as a physical record number in Access. However, you
can create procedures to create an audit trail in your database that
captures username, computer, primary key value, old value, new value,
date/time, etc.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AuditTrail.mdb" which illustrates how to do this. There is
another called "AuditTrail2.mdb" which extends the functionality of
AuditTrail, but it would be worthwhile to figure out the first one first.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
There are no record numbers in Access tables. I generally add an autonumber
primary key to all my tables so I have a unique identifier. I still wouldn't
refer to this autonumber as a "record number".
 
Scott Burke said:
Is there a way to read the physical record number of any given record?

If I can prove that a sertain number of records were entered all at
one time, I can fix a little problem I am having. I can't say more
about the problem.
Click to expand...

Jet, the database engine that is native to Access, has no physical
record numbers. It may be possible -- though I wouldn't know how -- to
inspect the database file using a low-level tool and determine whether
records are on the same page, but even then you wouldn't know for sure
whether that meant they were entered in succession.

If the table in question has an autonumber key field set to return
consecutive numbers, you could look at the sequence of autonumbers as a
hint. But that could be spoofed, since it's possible to manually insert
a record with a specific autonumber.
 
No physical record number?????
I could see why queries dont have them or at lest there own version.

OK let try it this way.
Can I see the "Natural order" of the table?

Surely there must be a way that Access mark's each record. Just to keep
track of it. If I index a field, lets say [name]. Then I search for
"Scott". How does Access know that the first record with "Scott" in [name]
is record 53. How does the index know?

Like the Delete field. You can't see it, but it is there.

Is there any Microsoft programmers here that are willing to tell us some
secretes.
We wont tell Bill your name. :)

Scott Burke
 
Scott said:
Is there any Microsoft programmers here that are willing to tell us some
secretes.
We wont tell Bill your name. :)
Click to expand...

I'm not a MS programmer, but can try to explain a bit more about
physical ordering.
Surely there must be a way that Access mark's each record. Just to keep
track of it. If I index a field, lets say [name]. Then I search for
"Scott". How does Access know that the first record with "Scott" in [name]
is record 53. How does the index know?
Click to expand...
The first record with "Scott" in [name] isn't really record 53: it's
just record 53 in the particular ordered view of the records that
you're looking at - e.g. sorted by ID. If you sorted the records by
[name] or [favourite colour], that same record might be record 125.

What confuses the issue is that, if you don't specifically ask for a
particular sort order, Access will display rows in a table in a
particular order - usually, if I remember right, ordered by the primary
key, if there is one. This default order is not the physical order of
the records as stored on disk, or the order in which the records were
entered. (although, as someone's pointed out elsewhere, if you have an
AutoNumber field, sorting by this _will_ show the order records were
entered, barring deliberate attempts to spoof this).

These two orders (physical on disk, order of time entered) are not even
necessarily the same. SQL Server is explicit about this, allowing you
to create a "clustered" index on a table, which is actually a physical
sort order. If there's a clustered index, the record you enter today
could end up close to the beginning of the table's space on disk,
depending on its values in the clustered index columns. AFAIK Access
doesn't allow you to specify the physical order of rows in a table
(anyone know better?).
The consequence, in both Access and SQL Server, is that there's no
guarantee that the last record (in the physical order on disk) was the
last entered.

You're right that there's a way Access marks each record to keep track
of it - as you say, it must do just to know where it is. But the
"where it is" has nothing to do with the order of records you see on
screen, or the order in which records were entered; and it's not
visible to any part of the Access application that we mortals outside
MS can see.

Of course you can add your own "date entered" and/or "date updated"
columns to a table, and update them whenever a record is
entered/updated - but it sounds as though it's too late for that
solution to be of any use to solve your problem. Maybe if you post
some more details of what you're trying to do, some of us here might
have some ideas?

cheers


Seb
 
Hi Sebt. That explains it a little better.

As for my problem. We sent three people to Adult ed classes. One of them (
no name's ) has deveopled the MOTO: I have a cirtificate. I have the right
to access/edit records manually.

Even tho the records do have an "Enter date" field. I was hoping to show
that all the records were entered at one time. That is why the commission
report is not showing his/her work. Some past commission reports that is.

That is why I wonted to show the physical record layout. If I can show that
person "X" manully entered his orders today. That is why they don't show up
in last week commission report even tho the "Enter date" would have made them
show up.

Showing The boss the physical records positions would have proven that.
there should be at lest two hundredd record's differance between his records.
If he had used the inhouse software.

I am dealing with a fairly Access smart person. It would be nice to have a
system level controll. Something a user can NOT change. like Physical
records numbers.

hooo well.... I guess I am just going to have to change the software and
add my own security. Some were some how.

Thanks for your time.
Scott Burke.
 
Another option, instead of (or in addition to) the Autonumber approach
suggested by several others is to have a DateEntered field on your table,
and set its default value to Now(). Don't allow people the ability to update
the tables directly, and you should be able to track what you're trying to
accomplish.

Recognize that whenever you compact a database, Access will (likely) reorder
the records based on the primary key, so just because your records are 34,35
and 36 today doesn't mean they will be tomorrow (unless you're using an
Autonumber primary key)
 
If you don't want people to edit tables directly, you must

1. set up User-Level Security
2. deny all permissions to all tables to everyone but administrators
3. base all forms on saved, Run With Owner's Permission(RWOP) queries.
4. grant permissions to forms and RWOP queries appropriately

Unless you do these things, a motivated user can subvert any timestamps
or autonumbers that you try to use.

Search the Security group for "RWOP" for a better description of the
lock-down process.

HTH,

Kevin
 
Back
Top