RE: Effect of no Primary Key

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Amanda Payton (AmandaPayton_at_discussions.microsoft.com)
Date: 10/22/04 

Date: Fri, 22 Oct 2004 12:43:02 -0700

Roger -

Lynn is right. It's hard to say without seeing the table itself.

I've had problems with multiple data entries myself before... (I program for
a company that has programs that sound like they work in a similar manner.

Your duplicate data is probably coming from one of two places ... The first
is that there is a malfunction in the code sequence that is importing the
data - and it's grabbing a record more than once. Is there a variable
somewhere controlling a loop that changes unexpectedly? Are you getting the
same number of multiple copies of records for EVERY record in a set? Do you
see any patterns to what records are being duplicated and which aren't?

The second is that the data record exists more than once in your
spread***, and access is just happily cutting and pasting - ignorant of the
fact that it has duplicate data in it's "hands". From what you have said -
this sounds less likely. (easy to check... either look at it directly - or if
there are copious amounts of records - make a seperate database, import the
entire workbook as a table, and run a "find duplicates" query on it. if you
have duplicate records in your source, you'll see them.

As far as primary keys are concerned... having an index of any kind makes
refrencing records easier and faster (though the database gets larger).
Their main purpose is to keep related information together, and to make sure
that when you tell Access "go find records related to "XXXXXXX" - it has
something difinitive to look for.

Frankly - this really sounds more like a coding problem. I don't know how
familiar you are with the debug window and the watch window.

If you open up the code module and click in the far left margin, a red line
and a little stop sign will appear - highlighting the corresponding line.
When you run code - either from the debug window, or from the form -
execution will pause when it gets to the line(s) you have highlighted, and
will only continue when you push the play button. This is useful for
checking the values of variables, and the path that Access takes through the
code sequence.

Additionally, if you use the Watch window (the other filetab on the debug
window), you can have Access keep a running display of what variables have
what values... you can then see if a variable goes out of bounds, or
increments a counter one too many times - etc.

Other questions worth thinking about - if you execute the code repetitively
- do you get more duplicates with each execution? I.E. first run, get 2 sets
of duplicates, 2nd run, get 3 or 4, etc....

Feel free to holler back, and I'll see if I can help you narrow down your
problem.

Amanda
"Roger Spencelayh" wrote:

> I've inherited an Access 2000 database, and the client is having a few
> problems with it. The first is that it is very slow, and secondly,
> we're getting data added multiple times from some code which imports
> data from an Excel workbook.
>
> There are 2 tables contributing to the problem. The first contains
> information about the Workbook - PK is the workbook name, and the other
> holds information read from the workbook - a maximum of 10 records from
> each workbook.
>
> The data is read in from code which reads the contents of a folder
> using the FileSystemObject, and writes the file name plus a couple of
> fields from the book into the first table. It then reads some other
> data from the book, and writes the file name, an ID from 1 to 10, and
> other information from the book into the second table.
>
> Run the code by hand in debug mode, and all is fine. Let it run
> normally from a button on a form, and it may add the detail once,
> twice or three times.
>
> The crunch is that this second table has no primary key. Could that
> contribute to the problem? What is the effect of not having a primary
> key?
>
> I will make FileName + ID the PK, but I'm interested in the real world
> effect of having no PK
>
> --
> Roger
>
>

Quantcast