Delete w/o Bloat

[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

 

[Chris O'C via AccessMonster.com]

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

 

[John W. Vinson]

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.

 

[DS]

I wish I could. But when these records are being deleted other people are
on the network accessing the back-end.
Thanks
DS

 

[DS]

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.

 

[Jason]

And Access doesn't reuse this space when records added?

 

[John W. Vinson]

Exactly. That space is marked as used and not reused until you compact.

And Access doesn't reuse this space when records added?

 

[Jason]

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.

 

[Tony Toews [MVP]]

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
--
Tony 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/

 

[DS]

Excellent Tony! Thank you very much.
DS