Re: Access DB grows too fast.

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 16:36:15 -0700

I expect that there's some "modifying" going on. I don't recommend
SQLPrepare for SQL Server as all of the more recent drivers/providers don't
need it. If I were you I would switch to MSDE where the operations can be
more closely monitored and it's designed for more throughput. 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Nate Clark" <ntclark@attglobal.net> wrote in message
news:40E0A1EE.9000501@attglobal.net...
> 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: My Book is Incomplete Regarding SQL Commands ;-(
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... Create a Connection object and pass in a ConnectionString to ... manage the database table you reference. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: (Newbie)Application Roles
    ... level: the database itself. ... SQL Server accommodates these needs through the use of application ... the user's connection through a specific application. ... the connection permanently loses all permissions applied to ...
    (microsoft.public.sqlserver.security)
  • Re: Help! Set up Windows Group to access application
    ... The connection string for a trusted connection is: ... --add login as database user ... SQL Server MVP ... If you have troubles with finding your ways in Crystal Reports, ...
    (microsoft.public.sqlserver.security)
  • Re: My Book is Incomplete Regarding SQL Commands ;-(
    ... Visual Studio but not to VS 2003. ... Create a Connection object and pass in a ConnectionString to address ... manage the database table you reference. ... Yep, my latest book can help too, but mostly if you're targeting SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)