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, _






DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _






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




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

Windows 7 - Database size bloating up continuously

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
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.



--



Doug Steele, Microsoft Access MVP



http://I.Am/DougSteele



(no e-mails, please!)
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
If you can write code to import the spreadsheet, you can write code to link



the same spreadsheet.



--



Doug Steele, Microsoft Access MVP



http://I.Am/DougSteele



(no e-mails, please!)
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
Not sure I understand what you're asking for.



--



Doug Steele, Microsoft Access MVP



http://I.Am/DougSteele



(no e-mails, please!)
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
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.



--



Doug Steele, Microsoft Access MVP



http://I.Am/DougSteele



(no e-mails, please!)
Doug,



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



Regards,



HP



India