Access DB grows too fast.

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

Date: Mon, 28 Jun 2004 16:58:18 GMT

Hi All and thanks for the help.

I have an application that needs to store a large # of records in
multiple tables.

Application is written in VB.

The problem that I am having is that after insert of these records (test
case is 900 K records), I get a '.mdb that is nearly 1 GB in size. I
then compact this database and get it down to about 155 MB in size. The
only activity in getting that huge size is the insert(s) of the large #
of rows.

It seems that Access is wasting huge chunks of space during the import
of these files and/or I am doing something wrong or not doing something
I should be.

I have tried 2 types of "bulk inserts" (BTW I cannot find an actual bulk
insert for Access. Is there one ?)

1. Declaring an ADODB recordset using sql such as:

    SELECT * FROM Table WHERE ID = -1

Which is guaranteed to be empty when the rs opens (ID can't be < 0 ),
and then adding to that recordset (.AddNew) and then updating it every
32 K records or so.

2. Writing my data out to a flat file and then calling C code that uses
the ODBC API SQLPrepare technique to create a parameterized SQL
statement which I then use to load data from the flat file.

(Preliminary results - method 2 much faster)

Both methods give me this huge database after this import.

Further, and somewhat worrisome, after I get about 2+ million records in
the database, I start getting "Invalid Parameter" errors all over the
application (My fields are Long Integer which should provide me with
values in a very large range - well above 2+ million anyway)

I see from reading my own post that indeed I haven't tried single
"INSERTS". This is something I will try now but I'll go ahead and post
the question as I'm not really confident that is something that's going
to fix this problem.

Relevant Pages

  • Re: Access DB grows too fast.
    ... "Nate Clark" wrote: ... > ADODB recordset causes the huge Access database. ... Writing my data out to a flat file and then calling C code that uses ... >> the ODBC API SQLPrepare technique to create a parameterized SQL ...
    (microsoft.public.data.ado)
  • Re: Access DB grows too fast.
    ... "Nate Clark" wrote: ... > ADODB recordset causes the huge Access database. ... Writing my data out to a flat file and then calling C code that uses ... >> the ODBC API SQLPrepare technique to create a parameterized SQL ...
    (microsoft.public.data.ado)
  • Re: Access DB grows too fast.
    ... does not recover space lost when rows are changed or deleted. ... It's only after you compress the database are these ... Writing my data out to a flat file and then calling C code that uses> the ODBC API SQLPrepare technique to create a parameterized SQL> statement which I then use to load data from the flat file. ...
    (microsoft.public.data.ado)
  • Re: Need Help deleting record from text file
    ... In many circumstances retrieval of data from a "home grown" data file will actually be faster than from a general purpose commercial database. ... save his data to disk using a "flat file" unindexed arrangement. ... In fact I never mentioned him saving his data to disk in any format at all. ... Admittedly a nice "ready made" database does make it relatively easy for people to store and retrieve data fairly rapidly without needing to write too much code or to worry about the methods used by the "black box" to perform such feats, and I certainly would not suggest that they should not be used, but I think that for many tasks a "home grown" data format would be as good as, and in some cases better than, a standard database. ...
    (microsoft.public.vb.general.discussion)
  • Re: Just say no to threads [Was: Software architecture]
    ... they knew there was going to be a database in the app. ... Now my colleague just spent a couple/three weeks designing an SQL ... Turns out they have a flat file of sample information, ...
    (comp.object)