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
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