KB article withdrawn? Access/Jet provider + ADO.NET + OLEDB: expanding database problem

T

Timo

I've tried to select several newsgroups where this question would be
germane, though the issues don't fit neatly into any one of them.

Please allow me to state all of the salient facts.

I am going to deploy a .NET database application in the field, where I
will have no direct control over it. It is a single-user standalone
database application that uses the JET4.0 oledb provider against an
Access 2000 database. The app consists of an EXE and a single MDB.

As many of you know, Access databases tend to grow quite a lot because
the engine caches data in work tables. After a day or two, adding and
deleting just a few hundred records using ADO.NET, issuing various
queries that involve most of the tables, my 600K starter database can
grow to be well over 3MB. And it probably contains less than 50K of
actual data in the tables!

Access provides a Compact option that reclaims this work area and
shrinks the database. I am trying to track down how to compact the MDB
in DOTNET using oledb, but the KB article seems to have been withdrawn,
which makes me worry that it won't be possible.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306538

I would appreciate any sage advice on how to approach the problem of the
expanding database given the scenario above (but not the kind that tells
me to use MSDE -- the deployment package has to be really small.)

TIA
Timo
 
T

Timo

Doug,
Thanks for the suggestion about the temporary databases. I am sorry I
was unclear -- though only a few records are added per day, the data
must persist. But the tables are queried in various combinations very
often throughout the day.

I *believe* from what I've read that JRO can be called from .NET -- the
examples involved console applications. I did find a copy of Q306287:

"SUMMARY: Neither ActiveX Data Objects (ADO) nor ADO.NET provide the
means to compact or repair Microsoft Access Databases. However, you can
accomplish this task by using the Microsoft HET OLE DB Provider and
Replication Objects (JRO) that was introduced with Microsoft Data Access
Components (MDAC) version 2.1. ADO.NET allows the use of COM-based
object libraries through the Interop layer."

Timo
 
T

Timo

Yes, thank you. After continued searching I found those, though my
intial attempts kept coming back with no hits.
 
T

Tony Toews

Timo said:
As many of you know, Access databases tend to grow quite a lot
Agreed

because
the engine caches data in work tables.
Click to expand...

Not at all sure I'm going to agree with this as being the reason.
After a day or two, adding and
deleting just a few hundred records using ADO.NET, issuing various
queries that involve most of the tables, my 600K starter database can
grow to be well over 3MB. And it probably contains less than 50K of
actual data in the tables!
Click to expand...

This is typical behavior. What is happening, I think, is that
whenever you add a record to a table Jet adds empty pages for new
records and new indexes as well as some working space.

A clients 230 MB after compacting grows by about 15 Mb by the end of
the first day. Then it grows by 2 or 3 Mb over the next few days and
then grows by 100K or 200 kb per day.

Unless they do a large import of a few thousand records. That really
seems to bloat it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DAO IS DED 18

Top