By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,991 Members | 1,737 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,991 IT Pros & Developers. It's quick & easy.

Duplicates being created in AutoNumber Primary Key...

P: 6
I have an Access 2003 back-end database that is multi-user, but is only interfaced with via a client front-end that the user has on their desktop. The forms are all unbound (it's not a split database, it was designed separate from the ground up) and all transactions happen via ADO recordset (connection to back-end is only opened when retrieving or storing, and is immediately closed when done, trying to overcome laggy network and the inability to have a real multi-user DB).

Records use an auto-number Primary Key in the back-end. Couple of years ago, I had an issue where every now and then (not consistently), a primary key from one record was being duplicated into another record. It wasn't happening at record creation, nor was the actual data in the record being duplicated - just the primary key field. It started after a coding update (that should not have had anything to do with the key field, and in fact it was not making any update to the record but only retrieving a few fields to be used in a completely different back-end), so I removed the update and the problem stopped happening. Still have no earthly idea what in that code could have done it but I was happy it was gone and the update I'd done wasn't important enough to be worth trying to rework anyway.

Now I've added a couple of fields to the back-end and have added the associated retrieval/storage of those to the user form (again, it's completely unbound, I am cycling through each box, reading the data into variables, and only then opening a connection to the back-end to commit it). I made no changes to the code that actually saves the record, past adding the lines needed in the recordset (rsSave.Fields("New Field")=strVariable). And the issue is back.

Again, pretty sure it isn't happening at record creation, and the issue also isn't a broken auto-number seed. What I end up with will be something like this:

12345 John Smith
12346 Bob Jones
12347 Janet Woods
12348 Jerry Black
12348 Rhonda Johnson
12349 Arlen Roberts
12350 Et Cetera

And it'll be clear by other data in the record, that the record for Rhonda Johnson wasn't created even on the same day as the record for Jerry Black - it just somehow got the same ID, at some point when Rhonda's record was being updated (or when Jerry's was). And I'm completely mystified how this can be happening - I don't have any code put in to update the ID field in the first place (no point, and it wouldn't let you do that anyway). I'm not running batch updates (no need), each transaction is a single record only, retrieved by the ID field.

Am using dynamic cursor and optimistic locking; and the problem is definitely random in nature, it isn't happening with every transaction. No idea why adding two fields would start this happening again, but would appreciate any insight anyone might have. I know that Access 'can't' duplicate a primary key, but it *is* doing it. If I tried to code it to *make* it do that, it would throw an error in a heartbeat - not sure why it's not throwing one when it does it itself. Does not impact the database operation either, apparently - where it shows up is when the user pulls selection-box updates into their local copy, then it will give the 'duplicate value' error as it tries to commit that second record with the same primary key field.
Nov 9 '12 #1
Share this Question
Share on Google+
12 Replies

Expert Mod 15k+
P: 31,471
So, you have chosen to try to redo what Access does for you using ADO recordsets, but somehow you're getting duplicates even within an AutoNumber field. This would be a nightmare to work out what you've done wrong even if you had included the relevant code or any technical information really. I'm afraid you're that person who saw the sign saying "That way be dragons" and took that path anyway. That doesn't necessarily make your decision wrong, but I'd guess I'm probably more experienced than you are at this game and it's not something I'd ever do without some extremely pressing reasons. Even then I'd expect to fall over problems I hadn't anticipated. Dealing with timing issues in a multi-user database is no trivial matter.

Unfortunately, a number of people believe they can do a better job of it than Access, until they realise that it's actually doing far more than they originally appreciated. It's only at that point that they realise their choice has taken them so far off the beaten track that very few others have the experience of what they're going through. Enough experience to avoid that track in the first place, but that still leaves you with a very small pool of resources to call upon I'm afraid.

You may get lucky and find someone who can help you with this, but I suspect if you do then they will require some more details as to what you're actually working on. That doesn't mean that you haven't done a good job of preparing your question. Clearly you've put much effort into it. I suspect different information will be required if you manage to find someone who is prepared to work with you on this though.
Nov 9 '12 #2

P: 6
No, trust me - I didn't spend a year re-coding what Access will do natively because I thought I could do better. The environment I have to work with begs for SQL/MySQL or another enterprise-level database, but without going into why we can't have it, suffice it to say that Access is all I have to work with. The network share is serving tens of thousands of users (though the database is only serving a couple hundred, and not all of them at the same time of day), is physically remote and often laggy as heck.

We had a regular split database before, and it was constantly crashing because people would leave it open; if the network share lost connectivity even momentarily or lagged particularly badly, the back-end database would corrupt and frequently badly enough that compact & repair wouldn't fix it (at one point it was a daily event, frequently even several times a day). We tried Replication, but could not get the users to stop making their desktop copy the master when prompted, nor could we completely suppress that prompt. We also had issues with users deleting or changing records when they should not. Those were the issues that only coding a front-end and not allowing anyone into the back-end except through the front did solve. Not maintaining a persistent connection when someone has a form open just displaying information means it doesn't crash like it did when it was a split database, because it isn't keeping the back-end open after it retrieves the info. Controlling what forms can be opened by a user (by using their Windows login and comparing to a user table in the back-end) means we now can control who can change what data or even view what data.

Trust me, I fought the SQL battle hard and lost; even just having Views vs. Queries would have saved a ton of work. I am not at all under-impressed with Access's native abilities, if anything I'm *very* impressed with the level of built-in functionality it has, as well as with the robustness of its VBA environment. It wasn't designed to be an enterprise-level database, but I've been successful (for several years now that it's been in operation) in basically making it one anyway; other than this particular issue which has only resurfaced recently.
Nov 9 '12 #3

Expert Mod 15k+
P: 31,471
I should have paid more attention to that little doubt I had when I first posted. Looking back over the first post now, I recognise some of the clues there that you're someone who knows what they're talking about (which is even more evident in your response).

Nevertheless, we may still require more detailed / technical information if we're even to look at this with you. What you are dealing with is certainly a complicated situation. Let me start by offering a few tips, from one professional to another, that may help with your general approach :
  1. The issue whereby Access ****s up it's stall is well-known and clearly a right royal PITA. The typical approach to dealing with this in such situations is to provide an Access host machine that people can log in to remotely (Using Remote Desktop or similar). This host should be very local in network terms to the BE that it's using for data.
  2. Each user should be issued with their own copy of the front-end which is programmed to check for updates when it opens and automatically cause itself to be replaced by the update if found (I can offer some code tips to help on this if requested). An option to trigger such an update manually is also a good idea I've found.
  3. Generally, where Access only is being used in the system, DAO is a more reliable and native approach than ADODB. ADODB is useful for a more standard approach among various servers, but DAO is better for working with Access exclusively.
  4. Keep-alive. An idea might be to initiate BE contact every so often, triggered by a Timer procedure in a form that would need to stay open for the duration of the session.
  5. Users, and yours seem a prime bunch, seem to manage to kill a session without closing the database. This essentially means that the project execution has terminated even though forms may still be open. Consider handling this by checking values of Static or Public variables when code is instigated that relies on them. I have a standard procedure called SetVars() which tests one of these variables and, if it is unset, goes through setting up all such variables that should be set. Alternatively, you could exit the project after the test if that is safer in your scenario.

All for now (I have to get back to work), but good luck with whatever approach you end up with, and I understand you're already a fair way down the road with your existing approach so will understand any reticence in changing course now.
Nov 9 '12 #4

P: 6
No worries - if I saw someone using the equivalent of a screwdriver to pound in a nail, I'd likely be asking the same sort of questions myself, *grin*. Unfortunately the PTB here either want you to live without driving nails at all, or else want to give you a 1000-lb steam-driven hammer that takes 20 people to operate and two days to set up, just to pound in one nail. So we improvise because neither alternative is acceptable (i.e., there is actually an Oracle-based RDBMS out there that has much of the same data and functionality as our in-house one; but it's terribly user-unfriendly, takes a week-long training class even for the most basic end-user functions. Given that most of my end-users are either mechanic-types or else are senior management types, and neither group is particularly patient with computers to begin can see the dilemma).

#1 I don't think can be done, even a developer is very restricted machine-wise (can't even run a cmd-line traceroute from a local machine if that tells you anything. Nor is there really a 'local' network; definitely not by domain, and even the OU is not strictly local, nor are the IP ranges, that I can tell.) It's pretty much the share, or nothing.

#2 is already in there, each user has their own copy of the front-end and there is an update checker that will force the update if I've put one out that is critical enough to need it (otherwise it is the user's option to update, if the update was just an enhancement that it won't break anything if they don't get it immediately).

#3 Besides just generally knowing it a bit better than I do DAO, I actually went with ADO because I still hold out hope that *someday* we'll be able to have a proper back-end. Migrating the whole thing to SQL Server now would be really simple, the upconverting MS tool works great and all I'd really need to change on the FE's would be the connection string(s). ;)

#4/#5 It does actually do those also; I even had to put in a check for it to make sure the BE is actually *there* (hasn't been corrupted and renamed 'Database' or 'Backup of backup of backup of...', and/or connectivity to the share hasn't been lost) before it attempts to open a connection. It also revalidates the user as well as the important variables at that time, to make sure the user is supposed to be able to do whatever they're doing.

The main issue we've had up to this point has just been connectivity with that share, and all the ranting and raving any of us has done has been met with 'that's just how it is', unfortunately. I was just hoping someone else might have seen Access violate its own hard rule about Primary Keys and know what might cause that. I've temp fixed it by putting in a routine that looks for a dupe PK and copies the second record into a new record (giving it a new PK in the process, which fortunately the nature of the data is 1:1 so doing that didn't mean having to break relationships) and then deletes the old, but of course preventing it from happening at all is still better than fixing it when it does.

I also realize that there may not be an answer other than just to accept that a screwdriver isn't a hammer, and if you're going to use it as one anyway, expect it to break a lot more often than a hammer would - was just hoping someone had seen something similar before and knew what had caused/fixed it, like the broken-seed autonumber issue. :)
Nov 9 '12 #5

Expert Mod 15k+
P: 31,471
I've never-ever seen that happen. I cannot conceive of a situation where that might happen. More than that, I cannot see how it can even be possible, but then I'm afraid your earlier explanation seemed confusing and contradictory to me. That can happen sometimes when trying to convey information using words alone. Very few people manage to say exactly what they mean when they try, and even when they do it is still hard to form a correct picture the other end without confusions creeping in. For instance, my understanding of what you were saying was both :
  1. Two records existed with the ID = 12348
  2. When attempting to write one of these records an error occurred.

#2 implies #1 could not be true. Ultimately then, I'm not exactly sure of the situation you're describing. All I know is that I would never expect two such records ever to be successfully created and added into the table. As far as I'm aware that cannot happen.

I wasn't left with a clear understanding either, of whether your FE is an Access project (of any type) using ADODB or something non-Access which is using ADODB. I doubt it makes much difference in the circumstances, but my experience with using code for this outside of Access is nil, so I'm less sure of what might happen that I'm simply unaware of.
Nov 9 '12 #6

Expert Mod 10K+
P: 12,364
We may need to see the backend to figure out what is actually going on. You can make a copy and delete most of the data if you want, we just need to see some of the offending data.
Nov 9 '12 #7

P: 6
Sorry - FE is an Access 2003 .mdb, that contains forms and a few tables to populate drop-down boxes from, and is run from the user's desktop. BE is Access 2003 .mdb that contains only tables/data and resides on shared network space. FE only 'talks' to BE via VBA, ADO connection and recordsets; everything (forms, controls, etc) is completely unbound and event-driven, and all hand-coded (is not using any native Access code, but instead the generic VBA that works in all Office products and differs little from VB 6). Form text/check/combo boxes are populated by the recordset data and the BE connection is only opened long enough to retrieve the record the user selected and is closed again until the user elects to update that record (which at that point it reads back all the boxes into variables, performs whatever validation is needed on the data and exits if any required fields are missing or data is invalid (date of birth > today, etc), and only then opens the connection, commits the data, and then closes the connection again immediately).

That I'm aware of, there have been no conflicts or duplicates that have occurred during record creation. It seems to be happening at some point *after* the record is created and assigned a (non-duplicated) autonumber record ID. At some point after creation (and I can't tell exactly when/where, because the BE is *not* throwing any errors either when it occurs, or after it occurs), somehow the unique, autonumber ID field from one existing record is getting injected into another existing record (so yes, I am ending up with two distinct records with different data in them, with the same number in the (autonumber) ID field). The BE database continues to operate normally in this condition, it throws no errors at all.

Database contains personnel data, with an autonumber primary key ID field in the back-end DB. There is a similar combo-box source table in the FE copies, that contains a few of the relevant fields to select/retrieve records with (ID which is in a hidden column, last name, first name, MI, etc). The users have a 'sync' button on their FE copy, to pull the current list of names into the table that populates their drop-down boxes (because I obviously can't force updates to their desktops whenever someone is added or removed in the BE by another user; and the loading if I got it to populate strictly from the BE each time the form is opened would be much too great). This is when the error is occurring.

The FE table is not an autonumber field for the ID (because it needs to be the same number as what's in the BE, because that is the field I use in the SQL statement to retrieve/display/update the record they've selected), but it still is set to 'no duplicates' because there shouldn't be any duplicated ID fields. So, when it goes to update the FE table, it sees the duplicate in the BE and throws an error when it's trying to copy the second of the two records into the FE's table, because it's attempting to break the no duplicate rule of the ID field in the FE table. [The fact that it's also violating the same rule in the BE table doesn't seem to matter, the BE just isn't 'seeing' the error when it happens (though it does if you try to force it, meaning if I threw in some code that said 'write a new ID into the ID field', it would either tell me that I can't update that field (because it's auto-number) or else it would throw duplicate-value error if it was in fact a duplicate value. The fact that it's happening _without_ generating any errors in the BE either at the time or afterwards is part of what's so mystifying about it (that, and the fact that *nowhere* in my code is the ID field in the BE ever updated, because again - it can't be, it's an autonumber field). ]

It's got to be some really bizarre data-collision or injection issue for it not to throw an error when it happens nor impact the operation of the BE in any way afterwards either (except to make that second record non-reachable, because it's always going to pick the first of the two records by the chosen ID field. There wouldn't be any modifiers or record-pointer movers [ORDER BY modifier, MoveNext, etc] to hit the second record with that ID, because none would be needed on anything where I'm only expecting to be returning a single record.). The impact to operation is really only seen when synchronizing data to the FE copies. It's really bizarre.
Nov 9 '12 #8

Expert Mod 15k+
P: 31,471
As far as I understand you, this is really a strange situation. It sounds like the sort of problem that you'd need full hands-on to have any hope of resolving. Possibly even a real-time group of FE users to create the issue. This will never be easy to duplicate. Even with your actual project files (The 2003 MDB files) available reproducing this would appear to be very hit and miss. I doubt there is much we can do for you. This is a shame as you certainly have raised a tricky question (and we generally are piqued by them), and you seem to be someone who can understand what is being explained without the need for translating into simplified terms (which is quite pleasant really).

I'm not suggesting that we give up at this stage, and Rabbit has offered to give some consideration to your problem if you post your project, who is a very bright young man from all that I've seen of his posts. I just want to explain how I see things at this stage. The portents aren't too good. That's a problem when you work at a fairly advanced level yourself. There are fewer people who can offer any insights that are actually helpful. We'll do what we can though.
Nov 10 '12 #9

P: 6
Thank you - I must say it's equally refreshing to talk to folks who similarly understand what I mean without the need to simplify it, *grin*. I'm the only developer/database person where I work, the other IT guys just glaze over if I start trying to explain that what is happening is absolutely not supposed to happen at all.

I'm not sure I can upload the project though - I can take a screenshot showing the duped PK and structure when I go back to work on Tuesday, but to upload the whole thing would take an insane amount of sanitizing (at any given time there are at least 3000 records in there, and it's all protected/sensitive personnel data; thus again the need for restricting viewing/updating of it the way it's been done, where there is a boolean token to keep users from opening a form via the navigation pane or from Design view (can only open via a form button, and there is code to check that you're authorized to open that form at that point; form code is protected by a strong pwd and the BE is also protected by a strong pwd that is passed with the FE's connection string and otherwise is only known by the IT folks, not by any of the end-users).

I will play around with it some more next week and see if changing the cursor type and/or record-locking method, and/or SQL statement (I am pretty sure I got lazy on at least one of them and said SELECT * vs. SELECT field1, field2, etc) fixes it. If anything does I'll definitely report back, and if I can't find a fix I'll go for sanitizing it and also recoding it to work outside our specific network, and see if one of you can figure out what's going on. As you say though, I'm pretty certain that the multi-user environment coupled with the random lag on the share is playing a big part in the cause, pretty sure I couldn't ever make it do it if I was the only 'user' in it at the time and I was using it on a local network that didn't have the kind of lag we normally see (they consider ping times under 100ms to be perfectly acceptable; and when you add stateful packet inspection onto that, that ping packets don't go through, you can imagine how bad it can get).
Nov 10 '12 #10

Expert Mod 15k+
P: 31,471
The standard default for a Ping packet is 2,000 ms (2 secs) so that's a fair old lag you have there. Similar to what I used to get from the branches at the company I used to be Networks Manager for. It was there I originally came up with the idea of providing a local PC for remote users to connect into and run as locally. I found out later I wasn't unique either in having the problem or coming up with that solution.

Obviously, you're welcome to post anything that might help in getting to the heart of the problem, and I'm sure we'd like to help where possible. I just feel that getting to the point where we understand the situation in enough detail to be able to help (It is, after all, a pretty complicated situation) without even having hands-on contact with it is a pretty big ask. By that I don't mean you shouldn't ask at all, just that you should be prepared to find we are unable to help much in the circumstances. There are limits to the time we have and doing things remotely is always many times more complicated.

Sometimes though, just airing your problems with those that can at least understand some of what you're struggling with can help. Sympathetic ears can be really helpful in our game I find :-)
Nov 10 '12 #11

Expert Mod 5K+
P: 5,397

Should you decide to clear the tables and post the code, I have a recursive vba that removes all records from all of the access non-system tables. It is based on DAO; however, it should run on your backend just fine... you just have to absolutly promise me that you will make a copy of the backend and use the code only on the copy!!!

I use this code when I'm about to take a new design mode application live so that all of my test records are removed... got tired of clearing one table and running into related records in another and so forth so on etc, so I coded me a tool :)
Nov 10 '12 #12

P: 6
I can write a routine pretty easily to clear the sensitive data - but I'd also have to do a fair bit of recoding in a FE copy as well it to upload it, because it wouldn't work if I didn't. It'd be looking for the backend on the network vs. local machine, plus it would be looking at your Windows login and without also having your own login info in the DB as a user, it wouldn't let you do anything but view the non-sensitive portions of records via the FE copy. Plus remove all the passwords from there is more than one backend that it talks to that I'd have to remove login/connection info from/for. It would actually be a lot of work to sanitize all that, but if the things I can still try don't fix it, it could be worth it.
Nov 10 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.