db FileLen question

D

Drew Richards

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
 
D

Drew Richards

Drew said:
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
 
T

Tim Ferguson

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.

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
 
D

Drew Richards

Tim said:
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?
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.
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.

Thanks for your prompt response.

drew
 
T

Tim Ferguson

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
 

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

Top