Delete w/o Bloat

DS

unread,

Oct 7, 2008, 3:55:21 PM10/7/08

to

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

unread,

Oct 7, 2008, 4:18:07 PM10/7/08

to

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

DS wrote:
>I'm running SQL statements to delete unwanted records.
>Is there a way to do this without bloating each time a deletion is run?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200810/1

John W. Vinson

unread,

Oct 7, 2008, 6:10:13 PM10/7/08

to

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

John W. Vinson [MVP]

DS

unread,

Oct 7, 2008, 10:32:17 PM10/7/08

to

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

DS

unread,

Oct 7, 2008, 10:35:01 PM10/7/08

to

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

unread,

Oct 8, 2008, 2:20:57 AM10/8/08

to

And Access doesn't reuse this space when records added?
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:9enne4dp74q70qi1u...@4ax.com...

John W. Vinson

unread,

Oct 8, 2008, 1:18:45 PM10/8/08

to

On Wed, 8 Oct 2008 19:20:57 +1300, "Jason" <Ja...@hotmail.com> wrote:

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

Jason

unread,

Oct 9, 2008, 1:16:34 AM10/9/08

to

That's one of the reasons I was considering using something else

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message

news:sqqpe4d814jvcrndq...@4ax.com...

Tony Toews [MVP]

unread,

Oct 10, 2008, 8:59:08 PM10/10/08

to

"DS" <boot...@optonline.net> wrote:

>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

unread,

Oct 11, 2008, 11:59:58 AM10/11/08

to

Excellent Tony! Thank you very much.
DS