This one has me stumped. I have searched and searched and not quite
found the right answer. I am using a VB .dll to interface with an Access
2000 database. The database is accessed many times using DAO while
running my application. Of course, over time, the database grows and
grows until it gets to be 2GB and then it crashes. I have .Compact
method built into my .dll to perform a Compact operation. However, I
wanted the .dll to track the db file size and Compact it if it got to a
certain size:

If FileLen(Me.FullPath) / 1024 > 131072 Then
Me.Compact
End If

Now, here is the problem. When the code runs at full speed, the OS does
NOT update the file size so the FileLen function does not return the
"true" size of the database. If I break into the code and debug step by
step (slowly), the db file size catches up to the OS. I even insert a
DBEngine.Idle and a DoEvents in the code for good measure:

Do While Not .EOF
myDb.IdleEngine
DoEvents
SysCmd acSysCmdSetStatus, Format$(FileLen(myDb.FullPath) / 1024,
"0,000")

' MORE CODE GOES HERE

.MoveNext
Loop

I realize that I could put a counter in my Do loop and compact every
x,000 loops. However, there has to be a way to do this cleanly by
determining the 'real' database size.

In my DAO code I already explicitly .Close all of my QueryDefs,
Recordsets, etc. and then set them equal to Nothing - so don't suggest
that I do that.

Thanks in advance for your help,
drew richards

Drew Richards wrote:
> This one has me stumped. I have searched and searched and not quite
> found the right answer. I am using a VB .dll to interface with an Access
> 2000 database. The database is accessed many times using DAO while
> running my application. Of course, over time, the database grows and
> grows until it gets to be 2GB and then it crashes. I have .Compact
> method built into my .dll to perform a Compact operation. However, I
> wanted the .dll to track the db file size and Compact it if it got to a
> certain size:
>
> If FileLen(Me.FullPath) / 1024 > 131072 Then
> Me.Compact
> End If
>
> Now, here is the problem. When the code runs at full speed, the OS does
> NOT update the file size so the FileLen function does not return the
> "true" size of the database. If I break into the code and debug step by
> step (slowly), the db file size catches up to the OS. I even insert a
> DBEngine.Idle and a DoEvents in the code for good measure:
>
> Do While Not .EOF
> myDb.IdleEngine
> DoEvents
> SysCmd acSysCmdSetStatus, Format$(FileLen(myDb.FullPath) / 1024,
> "0,000")
>
> ' MORE CODE GOES HERE
>
> .MoveNext
> Loop
>
> I realize that I could put a counter in my Do loop and compact every
> x,000 loops. However, there has to be a way to do this cleanly by
> determining the 'real' database size.
>
> In my DAO code I already explicitly .Close all of my QueryDefs,
> Recordsets, etc. and then set them equal to Nothing - so don't suggest
> that I do that.
>
> Thanks in advance for your help,
> drew richards

Somebody has to have figured this out. Anyone? Please help ......

drew

Drew Richards <(E-Mail Removed)> wrote in
news:NZCre.1813$(E-Mail Removed):

> Drew Richards wrote:
>> ... The database is accessed many times using DAO
>> while running my application. Of course, over time, the database
>> grows and grows until it gets to be 2GB and then it crashes.

There should be no need for this in a well-designed database. Have you
investigated why the mdb is bloating so fast? It is generally a result of
database objects being created and destroyed. If you are creating lots of
temporary tables, you might consider either using the same table and
emptying it rather than DROP and SELECT INTO. An even better and long-term
safer method is to create a new mdb file and make the tables in it, and
then erasing the entire file afterwards.

>> Now, here is the problem. When the code runs at full speed, the OS
>> does NOT update the file size so the FileLen function does not return
>> the "true" size of the database.

I am not an OS guru, but is seems to me that this would be expected
behaviour is the file is opened by a process that might me writing or
reading it. You may be better off asking in a vb or a windows OS group
rather than Access: it does not sound much like a database problem.

>
> Somebody has to have figured this out. Anyone? Please help ......

My guess is you are better avoiding the problem than programming round it.
Best of luck


Tim F

Tim Ferguson wrote:
> Drew Richards <(E-Mail Removed)> wrote in
> news:NZCre.1813$(E-Mail Removed):
>
>
>>Drew Richards wrote:
>>
>>>... The database is accessed many times using DAO
>>>while running my application. Of course, over time, the database
>>>grows and grows until it gets to be 2GB and then it crashes.
>
>
> There should be no need for this in a well-designed database. Have you
> investigated why the mdb is bloating so fast? It is generally a result of
> database objects being created and destroyed. If you are creating lots of
> temporary tables, you might consider either using the same table and
> emptying it rather than DROP and SELECT INTO. An even better and long-term
> safer method is to create a new mdb file and make the tables in it, and
> then erasing the entire file afterwards.
>

The only thing I am creating temporarily is QueryDefs and Recordsets. I
wonder if it would be any better if I hard-coded in a new .SQL string of
an existing query instead of creating a temporary QueryDef?

>
>>>Now, here is the problem. When the code runs at full speed, the OS
>>>does NOT update the file size so the FileLen function does not return
>>>the "true" size of the database.
>
>
> I am not an OS guru, but is seems to me that this would be expected
> behaviour is the file is opened by a process that might me writing or
> reading it. You may be better off asking in a vb or a windows OS group
> rather than Access: it does not sound much like a database problem.
>

I agree. However, based on the documentation, I thought that the
..IdleEngine method would cause the OS to 'catch up' with the database.

>
>>Somebody has to have figured this out. Anyone? Please help ......
>
>
> My guess is you are better avoiding the problem than programming round it.
> Best of luck
>

Once again, I agree. Although I have made great strides over the past
few years, database design is not one of my specialties.

>
> Tim F
>

Thanks for your prompt response.

drew

Drew Richards <(E-Mail Removed)> wrote in
news:jIDre.1820$(E-Mail Removed):

>> Have
>> you investigated why the mdb is bloating so fast? It is generally a
>> result of database objects being created and destroyed.
>
> The only thing I am creating temporarily is QueryDefs and Recordsets.
> I wonder if it would be any better if I hard-coded in a new .SQL
> string of an existing query instead of creating a temporary QueryDef?
>

Recordsets are purely memory structures and will not affect the mdb at all.
Creating querydefs probably would cause bloating, although I have not
experimented with them. What advantage do you get from using qdfs rather
than just passing the SQL to a recordset or report etc?

It doesn't really answer your original query, though: sorry!

Best wishes


Tim F