Compacting Access application db causes Run-Time error 3326

Question

I have an Access application whose DB has become bloated because it has not been compacted. However, when I run a compact on the db and then try to run the application, I receive a run-time error 3326, making the application unusable. I have tried all suggestions found on this site and others but nothing seems to apply to my situation or resolve the problem. Because of the size of the db, the application runs extremely slow, to the point where the app is almost, but not quite, unusable. We are trying to run a business using this application and are at our wit's end, because there is no commercial application that does what this app does. Please help.

Answer 1

Did you try the Access decompile?

I'm guessing you have some minor corruption in the database. If you are using memo fields, can you inspect them to ensure you have no funny characters there?

Beyond that, maybe you can send me your contact information and perhaps we can figure something out. tom 7744 at cox dot net

Answer 2

3326 = This Recordset is not updateable.

I'm assuming it is updatable before compact. YesThis is certainly unexpected behavior and you may need professional help to debug this further. I'd like to hire someone to resolve the problem, but we've had no success finding someone to help.

In addition to Daniel's suggestions, did you decompile the application? Google for "Access decompile" and you will find plenty of leads.

How many concurrent users do you have? 3

Large databases don't have to be slow. Sometimes this is a function of an inexperienced database designer who did not put the indexes and other features in place to make it fast regardless of size. Sometimes it's a function of an inexperienced application designer who did not put the criteria forms and other features in place to select fewer records to work with.

Sometimes you have outgrown Access as the back-end database and need to upsize to SQL Server. The DB is not that big, it's just bloated from use and from not being compacted since it was implemented. The programmer didn't know Access very well. Then he skipped town and refused to answer our calls and emails, leaving us stranded.

Answer 3

I run through the standard troubleshooting checklist.

Does your Code compiles? Yes

Is MS Access, Windows both fully updated? Yes

Have you tried importing everything into a new blank database and see if the problem persists? No. This is an application that's split into front end and back end. I don't know how to import into a new database.

How big is your database at present? Uncompacted it's about 250MB. Compacted it's 25MB.

Your are compacting it when no one else is using the database, right? Yes. I copied the db and compacted the copy.

Is your database split? Yes

After Compacting, can you edit the tables? Yes Can you edit the data through your underlying query?Yes  Is it only the form that poses a problem? The error appears in the VB code when a field is being initialized:

If Me.OpenArgs <> "Existing" Then

    [CustomerID].Value = 1   <<<Error occurs here.

    [INum].Value = 0

    [CreatedBy].Value = TempVars!CurrentUser

End If

Before compacting the db, this code ran fine without the error.

---

Daniel Pineault - 2010-2013 MVP

http://www.cardaconsultants.com

http://www.devhut.net

Answer 4

3326 = This Recordset is not updateable.

I'm assuming it is updatable before compact. This is certainly unexpected behavior and you may need professional help to debug this further.

In addition to Daniel's suggestions, did you decompile the application? Google for "Access decompile" and you will find plenty of leads.

How many concurrent users do you have?

Large databases don't have to be slow. Sometimes this is a function of an inexperienced database designer who did not put the indexes and other features in place to make it fast regardless of size. Sometimes it's a function of an inexperienced application designer who did not put the criteria forms and other features in place to select fewer records to work with.

Sometimes you have outgrown Access as the back-end database and need to upsize to SQL Server.

Answer 5

I run through the standard troubleshooting checklist.

Does your Code compiles?

Is MS Access, Windows both fully updated?

Have you tried importing everything into a new blank database and see if the problem persists?

How big is your database at present?

Your are compacting it when no one else is using the database, right?

Is your database split?

After Compacting, can you edit the tables?  Can you edit the data through your underlying query?  Is it only the form that poses a problem?

---

Daniel Pineault - 2010-2013 MVP

http://www.cardaconsultants.com

http://www.devhut.net