Primary Keys yes or no?

[Lovespar]

I have inherited a database and after examining the tables I have discovered
that there are no primary keys. I do have an Autonumbered field as a "Record
Number"
this database has only 5 tables and none are related. Most of the data is
kept in one huge table. Could this be the reason it takes 3-4 minutes to
open? there are only 145 records in the main table.
the other 4 tables look to be set up so that drop downs can be put on the
main form.
Would anything be hurt is I add a primary key to the Auto number field?

 

[Larry Linson]

_Something_ is likely "not quite right" if it takes that long to open an
Access database (no matter what the version, even if you have a slow
machine) with that few records in the main table. Have you done a compact
and repair? Make a backup copy first, then compact and repair.

If you don't have primary keys, you likely also do not have indexes, and
those affect performance, but, again, with 145 records, it should be
instantaneous. Autonumber is not something that is good to show to users,
because it is not necessarily consecutive numbers; it is intended to be
unique, used for internal purposes, such as joining records with related
records in another table. It seems unlikely to hurt if you make the "Record
Number" field a PK, but unlikely to help, either, with so few records.

Let us know how much the size changes and if you see an improvement in your
open time after you compact and repair. When you do, it would be helpful to
know what version of Windows you are running, what version of Access,
whether the tables and user interface are in the same database or if the
tables are in a separate database that is linked, and some characteristics
of your computer...

Larry Linson
Microsoft Office Access MVP

 

[Arvin Meyer [MVP]]

No. It never hurts to have a primary key. If the other 4 tables are set up
as combo boxes (drop downs) can be put on the main form, they are related,
and should have a primary key. There should be a foreign key for each field
in the main table. It shouldn't take 3 to 4 minutes to open a form in
Access. With 145 records, it should open instantly.

 

[Tom Wickerath]

I have inherited a database and after examining the tables I have discovered

that there are no primary keys.

Pretty much *all* tables should have a primary key. You can set the
Autonumber field as your primary key. This way, your primary key is
considered "meaningless", so you should never have a reason to change it's
value.

Most of the data is kept in one huge table. Could this be the reason it takes
3-4 minutes to open? there are only 145 records in the main table.

You have what is euphamistically known as an Access spreadsheet. A form
bound to a table with only 145 records should open fairly fast, although
there are many variables that can come into play, including, but not limited
to:

1.) Have you done a Compact and repair recently?
2.) Is the database located on a server (ie., is there a network that
separates you from your data)? If there is a network, is it a fast LAN (Local
Area Network), or are you attempting to open the database over a slow WAN
(Wide Area Network)?

Using a wan with ms-access? How fast, how far?
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

3.) Do you have Name Autocorrect enabled? Do you have SubDatasheets set to
[Auto]?

4.) If your database is located on a server, has it been split into two
databases, commonly known as a "Front-End" (FE) application file and a
"Back-End" (BE) data file? If it has been split, does each user have their
own copy of the FE on their local hard drive? And, if it is split, has a
persistent connection been established?

5.) What about the hard drive itself? Have you recently cleared out
temporary files and other junk, and then performed a defragmentation?

Here is an article written for Multiuser applications, but it is good to
follow these practices even for single-user applications:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Lovespar]

No. It never hurts to have a primary key. If the other 4 tables are set up
as combo boxes (drop downs) can be put on the main form, they are related,
and should have a primary key. There should be a foreign key for each field
in the main table. It shouldn't take 3 to 4 minutes to open a form in
Access. With 145 records, it should open instantly.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



I will try the compact and repair to the database, I want to split it as right now people are entering data into the table directly. I will work thought the other suggestions and see if anything helps. thanks

.

 

[Arvin Meyer [MVP]]

thanks

It's never a good idea to allow anyone to enter data directly in a table.
That said, you still need to split it immediately, and put a table link from
each front-end to the server. Make a datasheet form, if you have to, but do
it now before you experience corruption.

 

[david]

3 or 4 minutes to open may indicate that your Anti-Virus software
is scanning it when it opens. Exclude .md* files from your AV.
There are no md* viruses, so it won't hurt.

10 or 15 minutes to open indicates that your AV is scanning md*
files on your file server, so it probably isn't that - unless the file
is actually quite small.

Autonumber fields are indexed by default, so it is quite unusual
to find an Auto number field that is not indexed. So that is not
likely to be the cause of the problem, and so making the Autonumber
field a primary key is not likely to make anything faster.

Primary Keys have an important function in Access. You need
primary keys to have updatable queries in Access. You don't
sound like you have any data-entry queries, your users are
either using the Form or using the Table.

Since all the data is in one huge table, you don't really need a
primary key index on that table. In Access, you really need
primary key indexes when you break that huge table into
smaller tables, and then assemble it back using a data query.

(david)

 

[Armen Stein]

You have what is euphamistically known as an Access spreadsheet. A form
bound to a table with only 145 records should open fairly fast, although
there are many variables that can come into play, including, but not limited
to:

Also, are many of the fields of the Memo data type? If so, Access is
working extra hard to retrieve and display them, as memo text is not
stored in the record itself, but rather in another area that the
record merely points to.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tom Wickerath]

Hi David,

You bring up a good point that the antivirus software might be scanning
files on the network server, although I would tend to believe this would take
much less time (like 3 to 4 minutes) versus the 10 to 15 minutes that you
mentioned. Of course, this all depends on the speed of the network that one
is using, but if it is a typical LAN in a business setting, then I'd think
the time would be much less.

Autonumber fields are indexed by default, ...

Only if:
1.) You set the field as a primary key, OR
2.) You named the field "ID", "Key", "Code" or "Num" AND you have the
default configuration that automatically slams an index on fields with these
names. I recommend removing these field names, so that Access is not silently
changing your table design:

http://www.access.qbuilt.com/html/gem_tips.html#AutoIndex


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________