Error with incrementing key

  • Thread starter Thread starter Jake
  • Start date Start date
J

Jake

At work we have an application that uses an Access database. It is used to
log support requests from emails. The problem is that logging a request
manually can take a long time because you have to copy and paste parts of an
email.

I wrote a macro in Outlook that would automate this process. I simply
select the email I want to log and click a button for the macro on the
toolbar. It works fine but another user has been experiencing problems
trying to log requests manually while I use my macro. The application keeps
trying to create a record with the same key. The application complains and
the user tries again. He eventually manages to log a request after a few
attempts.

I'm not sure what type of database connection the application creates - DAO
or ADO - but its probably DAO. My Outlook macro uses ADO. I am using
optimistic locking so I am a bit surprised by this problem. It seems the
internal table key is not getting incremented. Does the database have to be
compacted? Any ideas how I can solve this?
 
Without seeing your database, my guess is that the
original developer used some sort of custom number
sequence for the (Primary?) Key for the Record but didn't
take into account the multi-user environment.

It is likely that when the other user starts creating a
new Record, a number from the custom number sequence (not
Access AutoNumber) is allocated according to existing
Records in the Table but no Record is actually created
with this number. When you jump in with your Macro, the
same process will be repeated with the same initial
conditions (since the other user hasn't actually added the
new Record into the Table) and you get allocated with the
same number. Since your Macro will be a lot faster, your
new Record will be saved. However, the other user will
have problems saving his/her new Record with the same
number.

You will need to get the original developer (or at least
someone who knows Access) to check it out. Hopefully, you
have the MDB file rather than the MDE file as the
canonical Text component of the code has been removed from
the database and you cannot modify the design of the MDE
file.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top