Using autonum field as ID but not as Primkey

[Zakraket]

A bit of a story, bare with me

I do support for a small company that has a few Access-databases built over the last 15 years, but the developer suddenly left the company just over a year ago (and just dropped everything) :banghead:

That's not the problem (nor MY problem ). I just jump in, try to figure out problems and fix them for the company

These databases are deployed to customers of my client (they sell them as software)

The apps are quite complex in the sence of the processes they support (financial administration for insurancecompanies) and in the way they are build (not very smart and full of high risk/potential problems)

At one of my clients customers one of the databases tends to corrupt itself, but the cause is very hard to track.

What happened last year:
- dec-2013: 2 days after I started support (without any knowledge of the apps and the programmer gone) the database got corrupted
- a simple maintenance by MSAccess seemed to solve the problem
- dec-2013: after a few weeks the database got corrupted again. Again compacting seemed to solve the problem
- this happened one or two times more untill march. At that moment compacting seemed to work at first but with every major action in the app (a lot of data-edits) the database would get corrupted again, so now the database was rendered useless
- I started searching for the problem and found out that multiple tables had problems: unreadable data/records, null-values in autonum-fields, double value in Autonum-fields, "##############" in almost every other field, extremely high numbers for financial fields (normally <€100 but now values of 400.000.000.000.000.000 and more
- I managed to fix the tables (loosing some data) and went searching in the backups when the problems started. This went back to november-2013 (4 months!) where I found the first backup without any corrupt records. Backups only are kept 3 months but there were some older copies
- after a very teadious proces I was able to restore all lost data, correct the whole financial system (balance sheets etc ) and the problem seemed gone.

However, a month ago (jan-2015), my client called me and he mentioned that again in december (2014, one year later) he had compacted the database on his customers system because it wouldn't start.
I told him he should have mentioned this to me and went investigating the customers database: again corrupt records with the same problems as a year before.

Because I was busy on other projects I was not able to look into the problem at that time, but took some backups (going back to nov-2014)

Last week the system broke down again (halfway feb-2015) so it seems like it's exactly the same "process" as last year: some kind of problem somewhere around end-november/start-december (client doing some kind of finacial action? Investigating)

Anyways....long story. I noticed that a lot of the tables have autonumber "ID" fields but most tables don't have a Primarykey setup, no proper index, and even allow duplicates!! :banghead:

I'm wondering, are there cases in which Access could insert null or "wrong" values in a autonumberfield that is not setup as a primary key.

When I try to read the data in the corrupted records (f.e. by exporting to excel) it seems that in some cases the data is shifted a couple of fields, which would indicate a bug in the software.
This could explain the empty IDs, or maybe even corruption of the database when it goes beserk with these values of billions and maybe tries to insert these values in the wrong columns. Or maybe these billions are just interpretations of different values being inserted (like entering a data in a num-column resulting in a number)

I would normally expect errors from access, but since the dear programmer almost ALWAYS uses "on error resume next", most errors are just ignored, don't seem to occur, and because he has put on "error resume next" in almost every 10th codeline it's not even easy to just "enable" errors: if I just comment out all "on error resume next" all kinds of errors start to rise and none of the program works anymore :banghead:
To work my way to the errors I'm looking for I would first have to build proper error-handling for the whole program. And then hope that error that might cause these problems accidentally occurs again...

Fucking amateur!:banghead:

I'm trying to find out if the program has such huge bugs that certain function are actually causing the corrupting in the database (or that the corruption is "naturally" occuring ).
Because the backups only go back three months I have a gap of almost 1 month between the oldest backup (18-12-2014, corrupt) and the copy I manually downloaded a while ago (25-11-2014, no corrupt).
So there's a gap of 4 weeks in which it's not clear what actions/functions the customer has started that might have caused the corruption.
And because the proces/timing is almost exactly like last year I believe it's not just coincidental corruption...

I have created a small access-app that tests the database for certain problem, so at least I can quickly scan the database to see if the corruption starts to creep in again.

For now, I need to recover data and reset the whole financial balance again, but it would be nice to find a trace of an actual cause...

(I have never experienced this kind of recurring corruption in my 20 years of Access-programming)

 

[spikepl]

How are these db's used?

An Access db file is disassembled and reassembled, which is why WAN and WiFI usage can corrupt a backend, when the reassembly goes wrong because of data dropouts. And any warnings/cautions/restrictions tend to get forgotten with time.

 

[Zakraket]

They are used in a LAN, with separated frontend and backend both on a networkshare, cabled. I know that networkproblems can corrupt a database, especially during datamutation and it might be the cause here.
It's just that I think this "pattern" is a bit suspicious

 

[CJ_London]

I would also look at the customer - if the same application is deployed across a number of clients, you would expect the same issue to arise elsewhere.

Perhaps this customer is doing something in a different way from everyone else or using a feature no one else is using.

Are the backends mdb or accdb? is your clients customer using 'anticipated' access versions? If using .mdb, is replication being used?

 

[Zakraket]

No, the exact same app is not used elsewhere. In the course of years most customers use different adaptations of the same program (probably one of the reasons why the programmer left: situation becoming difficult to manage), so there are no other customers using the exact same app as the "problem"-customer.

FE and BE are in mdb format, used with Access 2010. No replication. Just 2 users using the same FE from the networkshare (1 concurrent user 95% of the time)

I have set the time backups are saved to 1 year, so that if by november/december this year the problem starts again I can at least trace every backup to see what action is taken by the customer (the application makes a backup before every major data-action)
Also I with the access-app I created I can quickly scan a database for these specific problems (I might even run this app 24/7 on the network and ocasionally - once a day/week f.e.- automatically scan the database for problems)

Also have sent the customer a list of the data that was lost in the last corruption (72 records) to see if they can track what they might have done with that specific data in the period nov/dec 2014, so hopefully they can recall something.

I think I should investigate setting proper UIDs/Primkeys on the tables, but I suspect doing this will raise all kinds of errors/problems in the program and I would only do this if I get the hours paid...

 

[jdraw]

They are used in a LAN, with separated frontend and backend both on a networkshare, cabled. I know that networkproblems can corrupt a database, especially during datamutation and it might be the cause here.
It's just that I think this "pattern" is a bit suspicious

I also think the "pattern" seems suspicious. Do the users have separate copies of the FE on their own PCs? You say separated frontend and backend both on a networkshare but each user should have their own copy of FE.

I have heard of frequent, corruption issues with BE on WAN, and even more with wifi. The solution recommended by many is to move to SQL Server (even free express edition).
To quote one colleague

Better still, just go to SQL Server. This is free, easy and way powerful, and will absolutely prevent the corruption issues.

On Error Resume Next is not error handling (it's a cop out at best). The free MZTools for VBA has a facility to add some error handling code. It requires opening each procedure and a few key clicks, but it's quite consistent and certainly much better that "resume next".
It's quite amazing how many people attempt to use RDBMS without having Primary Keys on tables. It's like they learn Access, without any concepts of database.
It would seem the application you have has a number of things that could contribute to corruption. I still think the "pattern" is highlighting something.

I can't see that company remaining viable in the financial/insurance area when "mysterious corruption" is occurring, repeatedly.
Good luck with your investigation.

 

[Zakraket]

No seperate FE locally, it's not setup like that. Could easily be done by the way, but I'm only troubleshooting
Every hour I spend is too expensive for client and customers up to now probably problems like these will convince them otherwise

Going to SQL has crossed my mind too but atm I don't have the time (and probably there's no budget for it atm ). Should discuss it with client though..

You guys have never seen autonumfields (not Primkey) been filled with blanks (or nonsequencel values) in a "normal" procedure?

 

[CJ_London]

You guys have never seen autonumfields (not Primkey) been filled with blanks (or nonsequencel values) in a "normal" procedure?

blanks no, nonsequential clarify - sort it and it is sequential (but delete a record and you have a gap). Autonumbers can be set up to be random rather than sequential - I sometimes do that to discourage reliance on the autonumber field as being used for anything other than a record identifier

 

[Zakraket]

I mean inserting a value other than the next value the autonumber would create (not always sequential indeed)

Because some of the corrupt records (2) have a value in the autonumberfield of 12 digits while the table generates a 6 digit value at the moment, looks like an insert by the program (but might also be gibberish caused by the corruption)

 

[RainLover]

(I have never experienced this kind of recurring corruption in my 20 years of Access-programming)

How can you have 20 years experience without this type of problem before. Are you working alone and making all decisions.

There appears to be more to this than meets the eye.

What version is this 97 or 2003 or what.

How big is the back end.

Why is the back end not mde. Does this reside on the server. Relink all tables.

Why is the front end not mde. Do these reside on the Client's machine. Is only one person using each front end.

if you are getting corruption you should be getting it more often. Like up to say 6 times a week not on a yearly basis.

Are you sharing a code Library.

You should not need to compact and repair very often. Once a year should be enough.

Is there any code in the Back end.

is there any Data in the front end.

You should create a new blank database and import all objects into that. Do not export. Do one set of objects at a time. Like tables then queries.

Do a Decompile of every Database.

Every table should have AutoNumbar as the primary key without fail. No blanks.

That should be enough for you to start looking. Go slowly and do it right.

Hope this helps. I am sure I have missed a few items but I have not had corruption for years.

If you have anything else different please let us know. It may hold the answer.

 

[Zakraket]

Never said that I never seen corruption, I have seen enough in different occasions (also not saying that I'm an absolute expert btw... ) although databases I have built have never corrupted (knocks off on wood) and I have built them for 10+ concurrent users in networks and are still running fine after 5-10 years (although speed is often a problem ).

I have only not seen a situation in which corruption occurs like this with something that resembles something of a pattern in which it seems the software itself is the problem. I could recompile/decompile etc but I'm suspecting it will take untill end of this year before the problems start again

To be clear, this is software I have NOT developed and which is not documented at all. Around 20 customers are working with around 7 applications in almost 20x7 versions (one of them now going corrupt)
Also I am not working fulltime on these applications simply because I'm only hired on hourly basis when there are problems and simply because I have better things to do. I'm not being paid to sift through the software so I only know 20% of the code/functionality
This is an as-is situation in which the responsible parties don't have (or want to spend...) thousands of euro's on someone working fulltime. They should however....

To answer some questions:
- Access 2010 client, FE is mde (2003), backend is mdb (2003)
- FE is used by 2 users (95% 1 conc.) and resides on the networkshare in the same dir as the BE
- the BE is always relinked automatically on startup
- no code in the backend
- 70 tables, 150 forms in the FE with pages and pages of bad and inefficient code

Fixing things like error-handling, setting proper PrimaryKeys etc will only be viable if the problem become so large that either my client (or his customers) want to spend money on improving instead of extingishing fires. The customers won't because they will think the software is bad, my client won't because he won't get paid by his customers

Appreciate the thinking/tips btw

 

[RainLover]

- FE is used by 2 users (95% 1 conc.) and resides on the networkshare in the same dir as the BE
- the BE is always relinked automatically on startup

That was good feed back thank you. It is also a good argument for making notes.

Are you saying that the FE is shared. This is not good. I would have one on each of the user's C:\Drive.

I see no need to relink on startup. I could give a bunch of reasons for my opinion but I don't see that that will serve any positive purpose.

Sorry I was not of more help.

 

[CJ_London]

Rainlover asked the question re the size of the db, but I cannot see an answer

if it has reached 2gb then compacting a mdb will not solve the problem (this was fixed for accdb's), data needs to be copied into a new db.

Perhaps the customers db is reaching this limit around the end of the year?

 

[Zakraket]

Haha, not by any means. It's around 35mb after compacting, and I don't believe it ever gets more than 75/80 during the year (the backups are never larger than this)...

But, you never know...however I don't have a way to check this atm.

Now that I mention the backup, the backups are made from the client itself (procedure built by former programmer), it disconnects the backend, copies it, then reconnects. I know copying a Access-file that's in use is not very smart, but that seems not to be the case here.
Also, this backupmethod is used in almost every other program he made and that are in use with al other customers.

 

[jdraw]

zakraket,
I think you are missing a key point that Rain and I have tried to make. If the FE is sitting on a network share, it should be copied to each user's local PC. So that each user has their own copy. Sharing the same FE is not a recommended practice.

 

[RainLover]

zakraket,
I think you are missing a key point that Rain and I have tried to make. If the FE is sitting on a network share, it should be copied to each user's local PC. So that each user has their own copy. Sharing the same FE is not a recommended practice.

Very Very important. Never share a front end. I have seen a situation where there was a separate database that held a lot of code. the idea is that you change it one place and that flows down to all Database and all users.

Again do not do this. no sharing. the only thing to be shared is the back end where the Data is held. that is all.

 

[RainLover]

More thinking on my part.

What do you do when the Database becomes corrupt. How do you go about fixing this.

Set up a new backup system. Back up every file first thing in the morning, at least twice during the day and also at the end of the day's trading.

When corrupt do you do anything to the front end. You should not need to do a thing other than delete the Front end and replace it with the Master copy that you have never used and is stored somewhere people cannot touch. Don't forget to put it on the C:\Drive or D:\Drive if you prefer. Relink.

I may be repeating myself at times but I am simply writing what comes to mind.

Your Opening Statement. ID not a Primary Key. This requires looking at but first can you explain what you mean. It is a bit misleading.

 

[Zakraket]

With the corrupt database:
- first compact (will not open without it)
- then iterate all tables:
- "read" all data form all tables from all records/fields (do...loop, put every fieldvalue in a variable, thus reading every table//record/field)
- copy every table (transfer to different database). After a succesfull copy, delete the table. Both actions will fail on a table with problems
- delete records that still have problems (bad autonum, double autonum, empty fields (that should be filled), strange values that remain after the above
- compare the problemtables to older backups to see what data has been lost

I have built a small (very basic) access-app that does this for me. It will track down all (most) problem with records, fields and tables. When I come over new problems I adjust the code to cover these.

As far as the backup goes: I described it before (no problemo), but the application (FE) itself has a backupprocedure which disconnnects the backend and then "filecopy" it with timestamp to a backup-directory and then reconnects the backend.
For every major financial functionality a new backup is created. This means 10+ backups a day
I don't see problems in this, every other way of making backups will mean copying the backend whilst it's probably in use...

Normally I would use a loop all tables "transferdatabase" to use the Access-engine to make a backup. Might look into changing the backupprocedure, but as stated before, this procedure works with every other app from my client for other customers, so far without problems.

zakraket,
I think you are missing a key point that Rain and I have tried to make. If the FE is sitting on a network share, it should be copied to each user's local PC. So that each user has their own copy. Sharing the same FE is not a recommended practice.

Tnx. I know this (and normally use local FE-clients, also because a networkclient is slower).

I will move the FE to local.

It might be one of the causes, however I have seen databases with 10+ concurrent users in a shared database - in this case not a split FE/BE - without any problems (although I NEVER set it up like that myself!)

About the primary keys (it's in the story but I suppose its a bit too much text):
When trying to track all broken records in the database I noticed that a lot of tables have an autonumber "ID"-field which is used (functionally) as a key but not (technically) setup as a primarykey.
Also, former programmer did not setup any tablerelations (which would be impossible to properly do anyway without proper PKs)

As you can imagine, it will be very hard to implement this in a filled database with around 70 tables. Setting up relations will most likely be impossible due to all kinds of data-inconsitency that creeps in in a database without proper relations. Setting up the fields to be PrimKeys will probably also not go without problems (possibly generating error which are not handled, with financial balancing sheet problems as a result. Would require the entire FE to be tested)
It's possible, but timeconsuming, at which point money comes into play, and also the fact that this would have to be rolled out to all other customers (to at least keep things a bit consequent)

 

[RainLover]

Have you had an experience with decompile.?

I am thinking that you should get a blank NEW database.

Import your objects but not all at once. Start with all tables then Queries etc.

After each import do a decompile. Test then move onto Queries etc until you have done them all. Don't forget the back end needs the same thing doing.

 

[RainLover]

Just in case you did not realise this but we are trying to fix that which is corrupt.

We need to find the cause of corruption. Decompile may help us with this.

Also don't forget about the hidden and system tables.

What industry are we operating in. Reason is that I got caught once with overhead cranes in the workshop.