Database size bloating up continuously

[excel_hari]

Hi,

I have created a database with a form having a single command button, a
query doing some simple join/union and finally 2 code modules.

The code for form is

Option Compare Database
Option Explicit

Private Sub Command0_Click()

Call LookupData

End Sub

The code for the first module is

Option Compare Database
Option Explicit


Sub LookupData()

'CurrentDb.Execute "Delete * from OSRM_Table "
'CurrentDb.Execute "Delete * from ISSM_Table "

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"OSRM_Table", TestIt("OSRM"), True, "Report 1!"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"ISSM_Table", TestIt("ISSM"), True, "ItemMaster!"

DoCmd.TransferSpreadsheet acExport, , "Specialized Query",
BrowseFolder("Select a BASE Folder") _
& "\OSRM_Table_SpecialData", True

CurrentDb.Execute "Drop table OSRM_Table;"
CurrentDb.Execute "Drop table ISSM_Table"

DoCmd.Quit

End Sub

The code for second module is just a copy paste of Testit function from
access mvps site and browse function from www.cpearson.com.

The code for Query is

SELECT "Special" As Category, OT.*
FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU
Cd]=IT.PIN
WHERE (IT.[ISSD Special]="X")
UNION ALL SELECT "Not Special", OT.*
FROM OSRM_Table AS OT left JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU
Cd]=IT.PIN WHERE (((IT.[ISSD Special]) Is Null));


Basically, am using DB just to perform some lookup operation. If you
notice the first module above, then you will see that am dropping the
tables I created after my results get outputted. Inspite of dropping my
tables my DB size keeps on growing up from 124 MB to 230 MB to finally
336 MB right now. It seems each time I use this code (on the same set
of excel files) my DB size also keeps on increasing (am doing some
tweaking and fine tuning, so exporting the same excel files. How to
overcome the same?

Please guide me.

regards,
HP
India

 

[Pieter Wijnen]

Compact the database on a regular basis
With newer versions you can tick off under tools /options to automatically
perform this on Close

HTH

Pieter

 

[Nikos Yannacopoulos]

One problem with Access is, it does not automatically release unused
storage space when you delete data or objects; so, every time you import
you add to the database size, while the opposite doesn't happen when you
delete after outputting. This explains the constant bloat. To reclaim
the unused storage space, you need to run a compaction. You can do that
either manually (Tools > Database Utilities > Compact and Repair
Database), or you can set the database to auto-compact every time you
close it, by checking the box next to Tools > Options, tab General,
Compact on Close.
The latter assumes that (a) your Access version is 2000 or newer (this
functionality was introduced in A2K), and (b) the database file in which
the import and deletion happens is opened directly.
As regards (b), in the case of a split database, you should store the
temporary table(s) in the local front end, which will actually be
compacted on closing; setting the Compact on Close on a back end that is
accessed through a front end will not do the job, as the compaction
fires only if the file itself is opened directly.

HTH,
Nikos

 

[Douglas J Steele]

As the other have told you, compacting your database will reduce its size.

However, is it really necessary to repeatedly import the data? Since you're
strictly using the default settings when you import, can you not link to
Excel? That way, you'll automatically get any changes that were made to the
spreadsheet.

Try changing the acImport in your two TransferSpreadsheet statements to
acLink.

 

[excel_hari]

Pieter, Nikos and Douglas,

Thanks a lot for the suggestion. I have enabled the option "Compact on
Close" and now it is just 329 KB!!.

My Access version is 2003 and the file is in 2000 format.

Doug - Am repeatedly exporting same data files just to check whether
the small tweaks am making in code would give me same results or not. I
(actually my colleague is the one who needs this DB to be set up, am
helping him so that I can use this stuff probably in similar scenarios
in my future tasks) will be getting my regular excel data from some
other source, so the name of the file, location etc would be different.

Regards,
HP
India

 

[Douglas J Steele]

If you can write code to import the spreadsheet, you can write code to link
the same spreadsheet.

 

[excel_hari]

Doug,

Thanks for persisting with me.

Actually I got the code for importing spreadhseet (Windows API dialog
box) by copying from access MVPS website which has the TestIt function.
Could you please let me know as to how is it possible to specify name
change, location through code. Iam not actually in need of this here
(because of nature of DB which is to perform just Lookups and then go
to sleep) but I can probably use this in my future tasks.

Regards,
HP
India

 

[Douglas J Steele]

Not sure I understand what you're asking for.

 

[excel_hari]

Doug,

You asked me to consider using the acLink instead of acImport. While am
importing, the excel file could be in a different location and
different file name as compared to last time. I thought you said that
acLink can handle this, but I coudlnt understand as to how changes made
in a newer set of file/location would reflect in the corresponding
table in DB.

Regards,
HP
India

 

[Douglas J Steele]

As I said, to link rather than import, you replace the one keyword in your
TransferSpreadsheet statement.

If you need to change where the linked spreadsheet is located, delete and
relink is easiest.

While this may not seem significantly different than what you're already
doing, the fact that you're linking means that the data won't actually
existing in your MDB. A linked table adds little to the size of the MDB.
While deleting a linked table and relinking will cause some bloat
(approximate 5 Kb), it'll be significantly better than what you've currently
got.

 

[excel_hari]

Doug,

Thanks for your patience. I have finally understood your point.

Regards,
HP
India