Importing huge amounts of data, how to

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I have designed an Access based application which holds
summarized information from a larger database (many Gb).
The summary base will be between 500 Mb and 1 Gb, so
Access seems a good choice as the application will be
deployed to several disconnected computers. They will
only use it for reading, no updates.

Every month the database needs to be updated with new,
updated and summarized information in the various tables.
Now, I am wondering how I can best perform these data
imports. For the tests for database size I just used the
import function that is part of Access (file menu
import). However, when it comes to the real imports I
need to check for existing records in the target base and
update these, sometimes replacing data, sometimes adding
figures to existing field values. The intrinsic import
function of the Access does not seem capable of handling
that.

This seems to leave me with ADO or DAO though VB, or is
there any other loader utility or method that I could use?
Also, if I use ADO, which would be the better way to go
about it?

Consider that my data file will have several millions of
rows. For each row I will need to search for an existing
record and update it, if it exists. If it doesn't the
record needs to be added. I have only just started
working with ADO, and mostly with recordsets. However,
the process of creating a new record set (to query for an
existing record) for each row in my datafile does not
seem very effective performance wise.

Does anyone know of a good method?


With kind regards,

Frank
 
Frank said:
I have designed an Access based application which holds
summarized information from a larger database (many Gb).
The summary base will be between 500 Mb and 1 Gb, so
Access seems a good choice as the application will be
deployed to several disconnected computers. They will
only use it for reading, no updates.

Every month the database needs to be updated with new,
updated and summarized information in the various tables.
Now, I am wondering how I can best perform these data
imports. For the tests for database size I just used the
import function that is part of Access (file menu
import). However, when it comes to the real imports I
need to check for existing records in the target base and
update these, sometimes replacing data, sometimes adding
figures to existing field values. The intrinsic import
function of the Access does not seem capable of handling
that.

This seems to leave me with ADO or DAO though VB, or is
there any other loader utility or method that I could use?
Also, if I use ADO, which would be the better way to go
about it?

Consider that my data file will have several millions of
rows. For each row I will need to search for an existing
record and update it, if it exists. If it doesn't the
record needs to be added. I have only just started
working with ADO, and mostly with recordsets. However,
the process of creating a new record set (to query for an
existing record) for each row in my datafile does not
seem very effective performance wise.

Does anyone know of a good method?


With kind regards,

Frank
Click to expand...

I'm not sure whether or not the temporary storage required for import
process might exceed the capacity of the .mdb file format, even though
the size of the actual data did not. I have no experience to answer
this question one way or another, but I'd suggest you keep an eye on
this as you test various techniques.

That consideration aside, my first approach to this would be to access
the source tables as linked tables, and use update and append queries,
rather than recordset processing, to update the target summary tables.
The queries would join the linked tables with the target tables to see
whether a matching record exists.
 
Frank

Would it possible to split the database into a frontend
and backend and simply replace the whole backend
each month?

Or do your monthly updates only consist of a much
smaller number of records, ie only those that need to
be added to, or amended in, the existing large
number of records?

Does the table have a Primary Key field? If it does,
seeking records should be very fast.

Geoff
 
Back
Top