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

[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

 

[CT]

Will this do?

http://support.microsoft.com/default.aspx?scid=kb;en-us;306287
http://support.microsoft.com/default.aspx?scid=kb;EN-US;230501
http://support.microsoft.com/default.aspx?scid=kb;en-us;230496

 

[Otis Mukinfus]

You can use DAO from .NET to compact and repair the database.


Otis Mukinfus
http://www.otismukinfus.com

 

[Douglas J. Steele]

Afraid I can't comment about why the KB article is missing, but why not
create temporary databases that you can delete once you're done with them?
Then you don't have to worry about bloating. While it doesn't deal with
..Net, Tony Toews has an article about doing this at
http://www.granite.ab.ca/access/temptables.htm

I haven't done any work in .Net yet: does it still support JRO? If so, see
whether http://support.microsoft.com/?id=230501 helps.

 

[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

 

[Timo]

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

 

[rlfine]

Timo,

I have been programming with ADO, OLEDB and the Jet database engine for
several years - so I am quite familar with the growth of the Jet
database file. I am not aware of growth as a result of "work tables" -
in fact, I was questioning the following claim:

the create/delete/create/delete/create/delete scenario is terrible - the
database will grow way beyond the minimum required size.

However, I have seen nothing that suggests that the
insert/select/insert/select/insert/select scenario in a VB/VC++ app with
a Jet 4.0 database using ADO or OLEDB or DAO will cause any Access
Bloat!

Make sure you understand the difference between an Access application
and an application that uses Jet as the backend database. If yours is
of the first type, then you may see substantial "BLOAT" in a multi user
environment -- as for the later, I would suspect NOT!. I assume that
you are using a server side cursor and have the locking mode set to 0!

regards
roy fine

 

[Tony Toews]

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

because
the engine caches data in work tables.

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!

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