HUGE file size increase for no reason?

[JennW]

I have as Access 2002 database that 2 days ago was only 1.2 MB in file size. But for some reason the past 2 days it is now 1.99 GB in size. I have checked all of the obvious places for changes such as records, queries, and duplication of tables. I have also run compact and repair, but nothing has changes. I am dumbfounded. I can see no reason for this sudden increase in size and it is too large now. Any suggestions?

thanks,
Jenn

 

[lameid]

I'm still using Access 97... But the old standby to something really weird like that is to import everything into a new file.

 

[JimHorton]

Jenn,
If the compact didn't make it smaller, look for hidden tables or tables with blob fields, etc.
If you run alot of queries, and change the SQL, say, in a loop, Access will save EACH iteration of the sql text, even if you delete the single query that was the original source sql. The way to get around this is to use a Temporary QueryDef in the loop (use set qd = db.CreateQueryDef("","Select Blah From Blah&quot. Also importing and deleting alot of data will do as DOS did with deleted files--not really deleted, just flagged as 'un-useable'.

I found this out the hard way using Access to do a data-load into the backend db, looping through order records date-by-date and changinq the append querie's sql (to around the evil dba's tiny rollback segment allocation). A few hours and a couple million records later, the Access db, with no local tables, was at the 1 gig limit. Compaction brings it down, but it's still a pain. The Temp querydef eliminates this.
--Jim

 

[JennW]

Ok, I figured out that every time I run a make table query it increases the size of the database. Even though it is replacing the existing table with the new one, it still increases the size. When looking at the SQL select statement in the query everything looks fine. Where exactly would I put this query def? Would it go in this statement?

I dont understand why in Access 97 it didnt increase the size of the database but it does in 2002. Undocumented feature perhaps?

thanks for your help.

Jenn

 

[DISI]

Jim,

Paul Again. I am wondering if your suggestion will help me on another issue I have. Mostly this happens with Access 2000. I routinely take large data feeds from a txt file and import it into Access. My Access mdb contains only a link to the txt file, an empty formatted table and an append query (I don't know about Temporary QueryDef). The txt file is about 250 meg. When successfully imported the mdb grows to about 650 meg. 4 times out of 5 I run the append query and my mdb crashes, growing beyond the size limitation. If I persist, eventually it will append successfully. Will your suggestion above help in this situation and if so, can you help me again by being more clear with the code. Thanks.

Your Fan, Paul Paul Faculjak
paul@DataIntegritySolutions.com

 

[JimHorton]

Paul,
I don't think the temp querydef would help here--the issue with that was that the sql text alone was increasing the size (in my case, now that I recall, it was looping record by record, so a million sql statments at possibly 1k per pop will do it).
I'm not sure if there's a workaround for the cases where the importing causes this. Are you importing directly to the destination table? If you're using a temp table, this would be the problem--you delete the records but they're not really 'deleted'. I think regular compaction is the only way here.

Jenn,
It sounds like you have a similar issue as Paul, the Make Table query is increasing the size of the db, but deleting that table and re-running the query won't reduce the size, again compaction is the only way.

Basically, the size of the db wont shrink on it's own, so if you add records or tables and then delete them, you won't see the reduction until you compact.
--Jim

 

[lameid]

I think my comments below are immediately helpful to Jenn and may get Paul thinking about using temp files... Also I have a question for Jim.

My work around to a make table query in a multiuser environment is to create a database with an empty template table and then use the FileCopy statement to move it in code to the client. The frontend has a link to the client copy and I use an append query instead of a Make table query.

Remember I'm still using Access 97 but my understanding of DB bloat is that the database file becomes fragmented like hard drives do (other than what Paul mention which is new to me but leads to a question <see below>). It may help to import everything but the temp file into a new database and then let the frontend create the table. This will force the table at the end of the file and when you delete the table it should allow the new table to be created over it spilling over into empty space instead of saying &quot;that won't fit throw it at the end of the file&quot;. Of course I only had limited success with this as Access 97 seems to save forms when filters or properties are changed in VBA which would but a break in the database.

Jim,
About bloat caused by modifying querydefs... I use querydefs for SQL pass through queries that I modify the SQL property for in VBA. I do this because you can't create a temp querydef in Access 97 and use it as a recordset... Naturally I can't use a literal SQL statement because I need the connect string. When I begin migrating to later versions do you have any ideas for this issue? If not, I'll keep it in the back of my head as I bring myself current.

 

[JennW]

I appreciate everyones help on this problem. I see where after I run the make table query, then run the compact, everything comes back to a reasonable file size. I cant expect my end-users to be running compact every time they execute this query. Since you cant have a macro running or VB executing when compact and repair start, are they any suggestions as to how to make this thing run without user intervention?

thanks,

Jenn

 

[lameid]

If you don't like my idea of the temp file... the next best offering I have is to use a batch file that you shell to which tells the user to wait and compact the database with a command line switch... Will XP support batch files?

Batch file:

@echo off

echo Wait for your Access application to close before
continuing

Pause

Rem Use your appropriate path to Access executable

&quot;C:\Program Files\Microsoft Office\Office\MSACCESS.EXE&quot; /compact %1

Echo Compact complete. If the command prompt window is open you may close it now.


Notes:
REM is a comment line
@ keeps a line from being displayed on the screen

Echo 'echoes the text to the screen or turns on/off command echoing when used with on/off parameters

Pause Pauses the batchfile and displays a message press any key to continue

%number parameter ordinal number passed to batchfile
i.e. if &quot;runbat.bat Fred&quot; is run or shelled etc then %1 = Fred in the body of the batch file

If you name the batch file compactDB.bat (bat is the extension for batch files) then use the following command to execute.

Shell &quot;compact.bat &quot; & currentdb.name

Obviously this requires user interaction. I prefer the temp file.

 

[euskadi]

I've run into this problem in '97 and 2k. A work-around that should slow the database bloat is to create a query that deletes all the records from a table, and change your make table to an append. That way you're deleting the contents of the file and then filling it back up each time. For some reason this seems to help, tho not totally resolve, the issue.

 

[JimHorton]

lameid,
Instead of the querydef recordset for odbc passthrough, you could accomplish the same thing by opening an odbc connections recordset, and change the sql at will.
Dim ws As Workspace, rs As Recordset, cn As Connection
'note that this is Access user/pwd here
Set ws = DBEngine.CreateWorkspace(&quot;tempws&quot;, &quot;admin&quot;, &quot;&quot;, dbUseODBC)
Set cn = ws.OpenConnection(&quot;AnyName&quot;,dbDriverNoPrompt, ,&quot;ODBC;dsn=CBOE_UNIX_1;uid=x;pwd=x&quot

Set rs = cn.OpenRecordset(&quot;SELECT * FROM L_TRADER_B&quot;, , dbSQLPassThrough)

--Jim