Table filling with #'s (1 Viewer)

Larnu · Nov 5, 2013

Database consistantly corrupting, and primary key lose

Hi All,

I came back yesterday, after having a nice weekend, to find that work had somehow managed to do a number on the Access 2003 database we use. I'm not sure what they've done, but when i had a look at one of the tables, it was filled with "deleted records", or really odd data. The first table i looked at, had about 50 rows of data prior to the first record, just filled with #'s on any compulsory fields. When I got to the bottom, There were more, as well as random entries with random alphanumeric strings in. The random entries had some really odd ID numbers as well, with one having an autonumber over 100,000,000 (the newest genuine record is only at about 143,000)!

I compacted and repaired the backend to fix, however, and they weren't tehre all day. I've come in this morning though, and after a couple hours they're back, and now keep on reappearing.

(Please see my second post for further details)

Does anyone have any ideas?

Thanks.

James Dudden · Nov 5, 2013

Can be caused by a lot of things but I find the most common cause is users pasting into text and/or memo boxes. Access often can't cope with strange characters pasted in and you end up with corruption. Could this be happening?

One other thing - after doing the compact and repair it would be worth looking hard to see if there are any particular records that were affected and then delete them as they may cause problems again later.

Larnu · Nov 6, 2013

Thanks james.

Some users may copy and paste, but there shouldn't be any special characters in there. The fields also have validations on them, and the special characters would often be outside of the allowed range.

I've had a stab at a bunch of thigns to try and get the database undercontrol, and I'm pretty much stumped now. The database seems to be corrutping sometimes within 10 minutes of a repair (it's never done this before, and we've been using it for over a year).

I've also noted that the primary field on some tables is simply disappearing, meaning that recordsets can't then be amended. So far, I've tried the following to no avail.

Repair and compacting the front & back end
Changing the location of the back end, and giving out a new front end with the new data links to everyone, just incase someone is using an old fe version (no front end changes have been made in about a month, and the issue began on Saturday).
Updating the front end to 2002
Updating the back end to 2002
Making the front end into an MDE file and redistributing
Allowing only 6 agents to use the system for period, and checking stability (the system lasted 3 hours this time before breaking)

I really have no idea what to do anymore. The only thing i've read they might suggest is that a form is corrupted (unsure why it is now, and wasn't 4 weeks ago), and to remake. Unfortuatnely the steps are for a simple form, and replies on having identified which. I have a lot of forms, and some have a several hundred lines of VBA.

Any further help is greatly apprecaited.

Thanks all.

James Dudden · Nov 6, 2013

When I say special characters I mean hidden characters that could be formatting or something say in Word which Access can't translate and thus the error. I would probably look further at this to find out exactly what users are copying/pasting and from where. One way to avoid these characters is to copy and paste the text first into Notepad.

Larnu · Nov 6, 2013

Anything they're copying would be from internet explorer, and would only be specific data provided from our ecommerce site. Although i can understand that characters that Access may disagree with could be an issue, why would this suddenly crop up now, when since September last year has never happened?

If this is the cause, hwoever, then combatting is going to be pretty difficult, due to the amount of people using the database. We can often have 100 people putting details through it, and there's a lot more people in the team. I'm sure we all know that it's impossible, no matter how many times you tell them, to get users to not do what they're not supposed to do.

Thanks.

Solo712 · Nov 6, 2013

Larnu said:
Thanks james.

Some users may copy and paste, but there shouldn't be any special characters in there. The fields also have validations on them, and the special characters would often be outside of the allowed range.

I've had a stab at a bunch of thigns to try and get the database undercontrol, and I'm pretty much stumped now. The database seems to be corrutping sometimes within 10 minutes of a repair (it's never done this before, and we've been using it for over a year).

I've also noted that the primary field on some tables is simply disappearing, meaning that recordsets can't then be amended. So far, I've tried the following to no avail.

Repair and compacting the front & back end

Changing the location of the back end, and giving out a new front end with the new data links to everyone, just incase someone is using an old fe version (no front end changes have been made in about a month, and the issue began on Saturday).

Updating the front end to 2002

Updating the back end to 2002

Making the front end into an MDE file and redistributing

Allowing only 6 agents to use the system for period, and checking stability (the system lasted 3 hours this time before breaking)

I really have no idea what to do anymore. The only thing i've read they might suggest is that a form is corrupted (unsure why it is now, and wasn't 4 weeks ago), and to remake. Unfortuatnely the steps are for a simple form, and replies on having identified which. I have a lot of forms, and some have a several hundred lines of VBA.

Any further help is greatly apprecaited.

Thanks all.

Hi Larnu,
one other thing you may want to try is to copy your table into a temp, then killing the original table and rebuilding it from the temp (INSERT INTO...SELECT). Most of what appears to be data corruptions in fields/indexes of a table are not cleared by the repair/compact utility.
They would be fixed, in most cases, by rebuilding the table.

Best,
J.

Larnu · Nov 6, 2013

Thanks Solo.

I'll give this a try. I won't be able to till late hours tonight/early hours tomorrow, as there's over 300,000 records, so I'd rather not stress the db, or server, during peak hours.

If anyone has any other ideas in the mean time, I'll still be grateful.

Cheers!

Larnu · Nov 6, 2013

Not sure if this helps, just booted it up and there's a table "MSysCompactError", with the following entries:

ErrorCode ErrorDescription ErrorRecid ErrorTable
-1206 Could not find field 'Description'. tblRequest
-1053 Could not find field 'Description'. tblRequest

tblRequest doesn't have a field Description.

Thanks.

Larnu · Nov 7, 2013

So, the database was stable all morning, until just now (07:00-13:00). I've got another of those entries in the table as well (below). Does anyone have any info?

ErrorCode ErrorDescription ErrorRecid ErrorTable
-1605 Could not find field 'Description'. tblRequest

Thanks.

James Dudden · Nov 7, 2013

Definitely sounds like corruption so take a backup and then import your tables into a new backend and then compact it and hope that message doesn't re-appear.

Larnu · Nov 7, 2013

Exactly what I'd just done, but it died almost instantly. Really stuck.

James Dudden · Nov 7, 2013

Did you only import the actual tables i.e. not the MSysCompactError table?

Larnu · Nov 7, 2013

Yep, I left the one's that are normally hidden off. I created a brand new file, then selected File > Get External Data > Import, then selected all and removed excluded the tables mSysAccessObjects, mSysAces, MsysObjects, MSysQueries, and MSysRelationships from the selected.

Larnu · Nov 7, 2013

I'm going to try using the old be as a backend, and then starting a new back end, with the majority of import being structure only and seeing what happens. Hopefully it'll highlight if it's a form issue, or old data. If it's old data, the new back end should at least work fine. Not the ideal solution, but may be a work around.

James Dudden · Nov 7, 2013

Maybe try these first:
Tony Toews has an excellent web page on database corruption.
Http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
Http://allenbrowne.com/ser-47.html

Table filling with #'s (1 Viewer)

Larnu

Registered User.

James Dudden

Access VBA Developer

Larnu

Registered User.

James Dudden

Access VBA Developer

Larnu

Registered User.

Solo712

Registered User.

Larnu

Registered User.

Larnu

Registered User.

Larnu

Registered User.

James Dudden

Access VBA Developer

Larnu

Registered User.

James Dudden

Access VBA Developer

Larnu

Registered User.

Larnu

Registered User.

James Dudden

Access VBA Developer

Similar threads

Users who are viewing this thread