Speed of working on Access tables

[pauladams777]

I found the subject a tough one to adequately summarise.

Basically, I am writing a C++ application that needs to write content to an
Access database. The database already exists, so I don't need to create the
database or tables at runtime.

All I need to do is open tables, create records, populate fields and save
the records. I don't query, delete, restructure or even read existing
records.

Currently, I am using ADO to access the Jet 4.0 engine, but I cannot believe
how slow this is compared to other databases I work with.

My question is therefore in two parts.

What methods can I use to do the work on the Access database that I have
described above?

What methods are the fastest?

I have read that ODBC is faster than ADO, but don't know anything about
ODBC. I have also gained the impression that it is a "dated technology" -
possibly even deprecated.

Any comments?

 

[Cindy M.]

Hi =?Utf-8?B?cGF1bGFkYW1zNzc3?=,

You'd probably get a better discussion in an Access-dedicated newsgroup, but...

Generally, working with DAO - the Jet Engine's "native language" - will be
faster than any other method. ODBC and OLE DB providers both, if I remember
correctly, interface internally with DAO, adding an extra "layer".

FWIW, it might be faster to run INSERTINTO and UPDATE queries than opening
tables. Might be worth a test.

I found the subject a tough one to adequately summarise.

Basically, I am writing a C++ application that needs to write content to an
Access database. The database already exists, so I don't need to create the
database or tables at runtime.

All I need to do is open tables, create records, populate fields and save
the records. I don't query, delete, restructure or even read existing
records.

Currently, I am using ADO to access the Jet 4.0 engine, but I cannot believe
how slow this is compared to other databases I work with.

My question is therefore in two parts.

What methods can I use to do the work on the Access database that I have
described above?

What methods are the fastest?

I have read that ODBC is faster than ADO, but don't know anything about
ODBC. I have also gained the impression that it is a "dated technology" -
possibly even deprecated.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[pauladams777]

Hi Cindy,

Thanks for the info. Can you suggest "an Access-dedicated newsgroup"?

I got here from a suggestion to my "which group?" query about ADO and the
Access runtime.

One thought I had about your suggestion (INSERTINTO) was that this may
require the Access Runtime (or full Access), while the methods I current am
using (ie. ADO) only use the support that comes build in to Win2k, WinXP and
Win2k3.

As this product is intended to be fairly widely distributed, I am trying to
avoid having to bundle ART with it. Do you know whether INSERTINTO is
supported by ADO / DAO without ART?

 

[Cindy M.]

Hi =?Utf-8?B?cGF1bGFkYW1zNzc3?=,

What ADO supports depends on what the OLE DB provider for the database
provides. When you use an ADO (or DAO) connection no runtime need be present;
that's the whole point of providing data access standards. I know the DAO
interface allows you to use SQL SELECT, INSERTINTO and UPDATE types of SQL
statements; and I'm pretty sure the Access OLE DB provider does, as well (just
been so long since I've used it - OK, just found a small example I used in a
book - it DOES work).

Thanks for the info. Can you suggest "an Access-dedicated newsgroup"?

You have two basic possibilities: there's a DATA.ADO newsgroup
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.data.ado&
lang=en&cr=US

...As well as any number of Access-specific groups. I don't know which
web-interface you're seeing, but starting from the one you're seeing from the
above link, there's an entry on the left "Office Solutions and Development".
You'll find Access in that list, with a whole slew of newsgroups. The Access
folks tend to be a bit DAO-centric, but the good ones know the ADO interface,
as well.

I got here from a suggestion to my "which group?" query about ADO and the
Access runtime.

The "Where do I ask" forum? Yeah, if someone gets to an Office question before
I do, the answers tend to be a bit undifferentiated. Most of those folks don't
know much about the newsgroup structures. I just count myself lucky that
they've stopped sending everything to the VSTO forum

One thought I had about your suggestion (INSERTINTO) was that this may
require the Access Runtime (or full Access), while the methods I current am
using (ie. ADO) only use the support that comes build in to Win2k, WinXP and
Win2k3.

As this product is intended to be fairly widely distributed, I am trying to
avoid having to bundle ART with it. Do you know whether INSERTINTO is
supported by ADO / DAO without ART?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail