Effect of no Primary Key

[Roger Spencelayh]

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

 

[Lynn Trapp]

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?

It's hard to say for sure without knowing the actual data but, yes it could
easily contribute to the problem. The absence of a primary key means there
is no guarantee that the records can be uniquely identified by the database.

 

[Roger Spencelayh]

It's hard to say for sure without knowing the actual data but, yes it could
easily contribute to the problem. The absence of a primary key means there
is no guarantee that the records can be uniquely identified by the database.

Thanks for that Lynn. Is there anywhere you know of on the 'net where I can get
a good overview of exactly what the PK does?

Thanks.

 

[Amanda Payton]

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
spreadsheet, 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]

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.

Thanks for your reply Amanda.

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?

Now here is the problem. Breakpoint on first line of code and step through the
code and it behaves itself, working exactly as planned. Delete the data just
imported, remove the breakpoint and run it, and the problem appears. The strange
part is that on some workbooks it imports one copy of the data, on others 2
copies and on one or two it imports 3 copies. If we then delete all the imported
data and run it again, we get exactly the same duplications as the first run.
When go back in there on Tuesday, I'm going to add an Index to prevent the
duplicates, add a few counter variables and turn off the error handler and see
what happens. That should prove whether it's the code or the table causing the
problem.

The second is that the data record exists more than once in your
spreadsheet, 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.

There are a maximum of 10 records per workbook, so that's easy to check by
looking at the workbook, and it's not the problem. And unfortunately I can't
just import the worksheet as each 'record' spans 2 rows.

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.

I've inherited this system, and wall be adding PK's as appropriate. I guess I
was really hoping that someone knowledgeable would say that kind of duplication
was bound to happen, or couldn't possibly happen, just because there wasn't a
PK.

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....

No, because there's a code check that stops it importing a workbook more than
once.

Thanks again for you reply.

 

[Amanda Payton]

Roger - Have you thought about coming up with a macro in Excel to copy your
2nd line of your record and appending it to the end of the first, and then
deleting the unneeded line?

It's not something I've looked into too far myself, but Access VB can call
and trigger Excel VB and Excel Macros. (and any other office product for
that matter) Since each worksheet /workbook always has 10 records, you can
record a macro to copy and paste your data, and set it to execute 10 times,
then return control to Access, and let Access import the worksheet as data to
append to your table.

Just a thought... Best of luck!

Amanda

 

[Roger Spencelayh]

Roger - Have you thought about coming up with a macro in Excel to copy your
2nd line of your record and appending it to the end of the first, and then
deleting the unneeded line?

A possibility, but I decided to take the easy way and write the import process
out to a log file. Only one set of data is written to the table.

I'm convinced it's a server problem corrupting the tables. When it takes 27
minutes to make a copy of the backend mdb file on the server, 9 minutes to
download it to a local PC and 2.5 minutes to make a copy on the local PC, I
get worried that the server is overloaded, under spec'd or just plain crap.