Solved

Access DB Crashing on Compact

Posted on 2013-05-15
6
295 Views
Last Modified: 2013-08-13
I have an Access 2007 database that averages about 1.2 GB (it fluctuates as it goes through weekly processing steps, so we compact it throughout the process).  The issue is that the database always crashes when I compact at a certain point in the processing.  Here's an overview of the process:
run queries and VB code via macro to update Table 1
compact database
run queries and VB code via macro to update Table 2
compact database
run queries and VB code via macro to update Table 3
attempt to compact database but it crashes

I suspect Table 3 to have some sort of corruption since the crash occurs after updating that table.  After Table 3 is updated I cannot import or export it between databases without a crash.  The only work-around I have found so far is to create (from scratch) an empty shell with the same design as Table 3, export the contents of Table 3 as a csv file (because it's 500K records), and import the contents of the csv file into the shell table.  After doing this I can compact the database sucessfully and proceed to the next processing steps.  However, the next week I experience the same behavior.

what would cause this and how do I resolve it?
0
Comment
Question by:deefel
  • 3
  • 3
6 Comments
 
LVL 57
ID: 39168922
<<what would cause this and how do I resolve it?>>

<<run queries and VB code via macro to update Table 3>>

 Have you ever not had corruption after running the last set of updates on table 3?   If not, I would suspect an Access bug.

  If you have, then I would suspect something in the environment that's giving you a problem when you do a large number of updates.

 Fill in some more detail (Access version, format of the DB, is the DB split, what's running where, etc) and we'll see if we can't get to the bottom of it.

Jim.
0
 

Author Comment

by:deefel
ID: 39169089
I'm running on Access 2007.  There are several supporting DBs because of the volume of data being managed, however Table 3 resides locally in the problem DB.  The DB runs on a desktop PC, because it does not process on the network.  The database has been in existence for overa year  (I recently inherited it) and has had lots of crashes during processing over time at various places in processing (I believe caused by DB size), but it is only in the last couple of months that we are experiencing the crash on compact at this same point.

I have already tried limiting the amount of data being processed and that eliminated most of the crashes during processing.  I have also created a new DB and moved all the objects into it (to be sure it wasn't a corrupt DB).  

I am happy to provide more details if necessary.  Appreciate any help.
0
 
LVL 57
ID: 39169257
OK, first, take a look at help, then about and see what build Access is.

 It's good table 3 is local, as that eliminates quite a few things, but just as a double check, when this process runs against table three, it involves no other tables from anywhere?

 And is it now always crashing after this process, even after you've imported into a fresh DB?

<<but it is only in the last couple of months that we are experiencing the crash on compact at this same point.>>

 Any idea how much that table has grown since it worked and does not now?

 It sounds to me like your dealing with an Access bug or running out of a resource.  But a couple of other basic checks/questions:

1. Make sure your not virus scanning MDB/ACCDB files.

2. Drop to a command prompt and type SET; do TMP and/or TEMP point to a valid drive and directory?

3. What's the free disk space on the drive where the DB is and for the TEMP directories (if different)?

4.  Assuming you get no errors during the process correct?

5. Does the update process errors?  In other words, you don't do a DoCmd.SetWarnings false and then do a bunch of updates.

Jim.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:deefel
ID: 39169369
Version 6.1.7601 SP 1 Build 7601

Table 3 uses information from Table 2 for it's updates.  Table 2 is also local.

the DB always crashes after (not during) updating, when we try to compact.  creating new DB did not help.

I cut table 3 from 550K records to 400K two weeks ago.  it's still a big table, however, with 120 columns.  it needs normalizieng, but that's a separate issue. :-)

we just moved the DB to a new, more powerful PC to see if memory was the issue.  Free Disk space is 321 GB.

we do not get errors during processing (anymore).  we were getting "memory reference" errors previously.

because of the number of queries run during processing, the warnings are set OFF.  however, last week we ran each query that is involved with updating Table 3 individually with warning ON and no errors popped.  i suppose it's possible there is bad data in there...but i haven't seen any.

i did decompile/recompile the VB code last week.

with all of the issues this DB has had overtime, the only thing i have found that may give a clue (and may not) is that Access 2007 and Adobe do not play well together.  my DB uses Adobe to export PDFs (not until after table 3 is updated).  not sure if this could be possible just by having both applications installed (seems far-fetched).

Drop to a command prompt and type SET; do TMP and/or TEMP point to a valid drive and directory? --> not sure how to do this!
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 75 total points
ID: 39169444
<<Drop to a command prompt and type SET; do TMP and/or TEMP point to a valid drive and directory? --> not sure how to do this! >>

 Start, then in Run, type CMD.  You'll get a black dialog box.  Type SET and hit return.  You'll see TMP and/or TEMP listed along with the rest of the environment variables.

<<we do not get errors during processing (anymore).  we were getting "memory reference" errors previously.

because of the number of queries run during processing, the warnings are set OFF.  however, last week we ran each query that is involved with updating Table 3 individually with warning ON and no errors popped.>>

  Sound like an Access bug then, but what I'd like you to do is take each Insert, update, or delete query and set the "Use Transaction" property to no.   Hopefully you don't have too many to modify.   Make sure you have a backup.  With use transaction set to no, you can't rollback in the middle of a query.

  It does however greatly reduce the amount of resource that JET is using because it breaks up the query into multiple steps rather then trying to do it all in one shot.

 The other thing (and you may want to do this first) is that your behind in updates.  Install SP2:

http://www.microsoft.com/en-us/download/details.aspx?id=5

 Try those two things first and report back.

<<with all of the issues this DB has had overtime, the only thing i have found that may give a clue (and may not) is that Access 2007 and Adobe do not play well together.  my DB uses Adobe to export PDFs (not until after table 3 is updated).  not sure if this could be possible just by having both applications installed (seems far-fetched).>>

  As long as your not loading a COM add-in when Access starts, then no.  See below of checking and disabling if there are any.

Jim.

Access
1.Click the Microsoft Office Button , click Access Options, and then click Add-Ins.
2.View the add-ins and application extensions that are categorized as follows:

Active Application Add-ins - Lists the extensions that are registered and currently running in your Office program.

Inactive Application Add-ins - Lists the add-ins that are present on your computer but are not currently loaded. For example, smart tags or XML Schemas are active only when the document that references them is open. Another example is the COM add-ins that are listed in the COM Add-ins dialog box. If the check box for a COM add-in is selected, the add-in is active. If the check box for a COM add-in is cleared, the add-in is inactive. To learn how to open the COM Add-in dialog box, see the section called Turn off or manage the installed add-ins.

Document Related Add-ins -Lists template files that are referenced by currently open documents.

Disabled Application Add-ins - Lists add-ins that were automatically disabled because they are causing Office programs to crash
0
 

Author Comment

by:deefel
ID: 39205046
sorry for the long delay - I am looking at migrating the process to SQL Server also.  

both TMP and TEMP appear when I do Run-->CMD-->Set.

I do have SP2 installed.

It sounds like a good idea to set the Use Transaction Property to NO, and I will do so, but there are hundreds of queries to modify so I have not done it yet.

For the last two weeks the process has worked as expected except for the crashing on compact after creating Table 3.  Is it likely that the Use Transaction change will eliminate this issue?
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now