Re: Access DB grows too fast.

From: Nate Clark (ntclark_at_attglobal.net)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 22:55:19 GMT

Hi Bill,

Thanks very much for the response.

I'm not deleting and/or updating rows.

These are pure inserts where no like rows existed before.

I have tried everything I can think of and still get the same result. If
   I try to insert these rows using SQLPrepare/SQLExecute from C, I get
about 40MB of something "extra" for every few MB of new data that I load
  and can only get rid of this extra space by Compacting (an automated
compact is not practical after import as I expect that alone will wipe
out the performance gain I get from going to C (which, BTW, is pretty
significant).

All of this makes me wonder if there is something different between the
ADODB.Connection made in the VB side and the SQLDriverConnect connection
I get on the C side.

I looked but cannot find a way to obtain the connection handle from the
ADODB.Connection so I could pass that connection to the C Code. If I
could, I could prove or disprove that this difference (if any) is the
culprit (or if I've caused some kind of "multi-user" situation by
creating an "extra" connection to the db)

William (Bill) Vaughn wrote:
> Nate,
> Unlike SQL Server (or most other business-class databases), Access/JET
> does not recover space lost when rows are changed or deleted. Any alteration
> of the database results in the original row being marked for deletion and
> the new row (even if it's exactly the same) being added. Add a row, you get
> one more row. Change the row and now you have an additional row. Delete a
> row and the row remains. It's only after you compress the database are these
> extra rows removed and then only in the process of locking down the file and
> copying only the "active" (undeleted) rows.
>
>

Relevant Pages

  • Re: Does Access bloat file size?
    ... contributes to the size (other than deleting the objects one by one, ... compacting between each deletion and noting what difference there is) ... 6.6 MB is miniscule for an Access database. ... I have since added more queries and some tables ...
    (microsoft.public.access.gettingstarted)
  • Re: How do I delete a database?
    ... Don't edit the global.asa unless you understand DB connections ... just delete it (after deleting the DB connection in Toils Site Settings Database) and let FP create a new one when you File Import ...
    (microsoft.public.frontpage.client)
  • RE: Deleting database at runtime
    ... Open a connection to the master database, ... If im deleting the databse using 'DROP DATABASE ...
    (microsoft.public.dotnet.framework)
  • Converting a database
    ... I'm compacting a database that is currently 1.5gig and ... have hopefully reduced the size by deleting a large number ... of image files on the database. ...
    (microsoft.public.access.setupconfig)
  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)