 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / October 2008Delete w/o Bloat
I'm running SQL statements to delete unwanted records. Is there a way to do this without bloating each time a deletion is run? Thanks DS If you use Jet or ACE, when you delete records they aren't removed until you compact the db. You can upsize to SQL Server tables and the data files will stay the same size unless you put autogrow or autoshrink on, or run code (dbcc shrinkdatabase) to compact the data files, or use enterprise manager or another data manager, etc. A lot of the same administrative work under different names. Don't you think it would be easier just to compact when you're done deleting rows? Chris Microsoft MVP >I'm running SQL statements to delete unwanted records. >Is there a way to do this without bloating each time a deletion is run? I wish I could. But when these records are being deleted other people are on the network accessing the back-end. Thanks DS >I'm running SQL statements to delete unwanted records. >Is there a way to do this without bloating each time a deletion is run? >Thanks >DS Unless your SQL is very peculiar, deleting records will leave the size of the database unchanged. It won't *cause* bloat. The only thing that it will *not* do is free up the space formerly occupied by the deleted records. If you want to free up that space, then yes, you will need to compact. If you are using a temp table or a "scratchpad" table (routinely filled and emptied) consider putting it in a separate backend. You can create a new .mdb file for the table at the start of the process, and Kill it when done.  SignatureJohn W. Vinson [MVP] Thanks John. I am using these tables to update and move records. I like the idea of a seperate database! Maybe "bloat" was a bad word, but having a lack of another, it's what I was stuck with. This update and delete doesn't happen enough at this point but I was a little worried in case it is being used a lot. Thank you once again. >Thanks John. I am using these tables to update and move records. I like >the idea of a seperate database! See the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app. http://www.granite.ab.ca/access/temptables.htm Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Excellent Tony! Thank you very much. DS And Access doesn't reuse this space when records added? >>I'm running SQL statements to delete unwanted records. >>Is there a way to do this without bloating each time a deletion is run? [quoted text clipped - 14 lines] > .mdb > file for the table at the start of the process, and Kill it when done. Exactly. That space is marked as used and not reused until you compact. >And Access doesn't reuse this space when records added? >> [quoted text clipped - 16 lines] >> .mdb >> file for the table at the start of the process, and Kill it when done. SignatureJohn W. Vinson [MVP] That's one of the reasons I was considering using something else > Exactly. That space is marked as used and not reused until you compact. > [quoted text clipped - 19 lines] >>> .mdb >>> file for the table at the start of the process, and Kill it when done. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.