<div>
&lt;&lt;what would cause this and how do I resolve it?&gt;&gt; 
 
&lt;&lt;run queries and VB code via macro to update Table 3&gt;&gt; 
 
&nbsp;Have you ever not had corruption after running the last set of updates on table 3? &nbsp; If not, I would suspect an Access bug. 
 
&nbsp; 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. 
 
&nbsp;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.
</div>

<div>
I'm running on Access 2007. &nbsp;There are several supporting DBs because of the volume of data being managed, however Table 3 resides locally in the problem DB. &nbsp;The DB runs on a desktop PC, because it does not process on the network. &nbsp;The database has been in existence for overa year &nbsp;(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. &nbsp;I have also created a new DB and moved all the objects into it (to be sure it wasn't a corrupt DB). &nbsp; 
 
I am happy to provide more details if necessary. &nbsp;Appreciate any help.
</div>

<div>
OK, first, take a look at help, then about and see what build Access is. 
 
&nbsp;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? 
 
&nbsp;And is it now always crashing after this process, even after you've imported into a fresh DB? 
 
&lt;&lt;but it is only in the last couple of months that we are experiencing the crash on compact at this same point.&gt;&gt; 
 
&nbsp;Any idea how much that table has grown since it worked and does not now? 
 
&nbsp;It sounds to me like your dealing with an Access bug or running out of a resource. &nbsp;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. &nbsp;Assuming you get no errors during the process correct? 
 
5. Does the update process errors? &nbsp;In other words, you don't do a DoCmd.SetWarnings false and then do a bunch of updates. 
 
Jim.
</div>

<div>
Version 6.1.7601 SP 1 Build 7601 
 
Table 3 uses information from Table 2 for it's updates. &nbsp;Table 2 is also local. 
 
the DB always crashes after (not during) updating, when we try to compact. &nbsp;creating new DB did not help. 
 
I cut table 3 from 550K records to 400K two weeks ago. &nbsp;it's still a big table, however, with 120 columns. &nbsp;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. &nbsp;Free Disk space is 321 GB. 
 
we do not get errors during processing (anymore). &nbsp;we were getting &quot;memory reference&quot; errors previously. 
 
because of the number of queries run during processing, the warnings are set OFF. &nbsp;however, last week we ran each query that is involved with updating Table 3 individually with warning ON and no errors popped. &nbsp;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. &nbsp;my DB uses Adobe to export PDFs (not until after table 3 is updated). &nbsp;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? --&gt; not sure how to do this!
</div>

<div>
sorry for the long delay - I am looking at migrating the process to SQL Server also. &nbsp; 
 
both TMP and TEMP appear when I do Run--&gt;CMD--&gt;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. &nbsp;Is it likely that the Use Transaction change will eliminate this issue?
</div>

<div>
<div class="content wysiwyg-content">
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). &nbsp;The issue is that the database always crashes when I compact at a certain point in the processing. &nbsp;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. &nbsp;After Table 3 is updated I cannot import or export it between databases without a crash. &nbsp;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. &nbsp;After doing this I can compact the database sucessfully and proceed to the next processing steps. &nbsp;However, the next week I experience the same behavior. 
 
what would cause this and how do I resolve it?
</div>
</div>

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?

Access DB Crashing on Compact

Microsoft applications include a variety of software programs, including development and digital authoring programs (Expression and Media Center), educational programs, Internet software, including Essentials, Skype and the Live family, anti-virus, productivity applications and suites like Office, Excel, Word, Outlook, Access and PowerPoint, video games and server applications such as Exchange, SharePoint, IIS and Virtual Server.

Microsoft Applications

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.