first field in a table

  • Thread starter Thread starter KK
  • Start date Start date
K

KK

Hello,

I'm a complete beginner to Access. I notice in various 'sample' databases
that all the tables in the database include an 'ID' field, (number format),
as the first field, followed by the actual data for that table. I see that
this field is indexed and unique. Why is the 'ID' field so important?

Thanks

KK
 
Hi
Are you familiar with database theory?
Basically, in a database, each record in a table should have a unique
identifier.
The primary key of the table is that identifier. It ensures each record is
retrievable. In Access, it is possible for it to be autonumber,
theoretically making it unique. In other databases it may be a calculated
value, or a composite value.

Here are some previously posted links about normalisation - which will
explain how to determine fields and tables:
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

hth (hope that helps)
Marc
 
To add to what Marc said, when Access sees a field ending in "ID", Access
automatically indexes that field. An index significantly speeds up searches
on that field.
 
PC Datasheet said:
when Access sees a field ending in "ID", Access
automatically indexes that field.
Click to expand...

Could you expand on this please? How does MS Access 'see'? When is the
index created? For example, in a new blank database I executed this in
the MS Access UI:

CREATE TABLE Test
(
1_ID INTEGER NULL,
2_ID INTEGER NOT NULL,
3_ID VARCHAR(255) NULL,
4_ID VARCHAR(255) NOT NULL
)
;

.... but no indexes appear to have been created.

Jamie.

--
 
This is a user-configurable option. In the Access window, select Tools, then
Options, then the Tables/Queries tab, and look at the box labelled
'AutoIndex on Import/Create'.

I have not tested this, but my guess would be that in your DDL example the
option would not apply, because the table is being created by Jet rather
than by Access. The option certainly does apply when the table is created
via the Access UI (assuming, of course, there is something in the
'AutoIndex' list). I have not tested whether it also applies when the table
is created programmatically via DAO or ADOX.

One of the first things I do after installing Access on a new PC is always
to delete the contents of that 'AutoIndex' box. Indexing is much to
important to be decided so arbitrarily, IMHO.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Brendan Reynolds said:
This is a user-configurable option. In the Access window, select Tools, then
Options, then the Tables/Queries tab, and look at the box labelled
'AutoIndex on Import/Create'.

I have not tested this, but my guess would be that in your DDL example the
option would not apply, because the table is being created by Jet rather
than by Access. The option certainly does apply when the table is created
via the Access UI (assuming, of course, there is something in the
'AutoIndex' list). I have not tested whether it also applies when the table
is created programmatically via DAO or ADOX.
Click to expand...

Thaks for the clarification, Brendan.
One of the first things I do after installing Access on a new PC is always
to delete the contents of that 'AutoIndex' box. Indexing is much to
important to be decided so arbitrarily, IMHO.
Click to expand...

I couldn't agree more.

Jamie.

--
 
Back
Top