FileLen(Currentdb.Name) not updating when code is running

[Marco123]

Hi all,

I am having problems using the filelen function to determine the size of a database while the code is running. I have an Access database that is performing numerous calculations 1000’s of times with tables/temporary tables etc. The problem is that eventually Access exceeds it’s 2gb limit before the model has finished running. (Unfortunately, I am restricted in that I have to use Access and not something more sophisticated like SQL Server etc, and Excel would take too long). In order to prevent Access exceeding it’s 2bg limit, I have tried using the filelen function:

If FileLen(Currentdb.Name) > etc etc Then

Whilst this works absolutely fine whilst stepping through the code (F8), and going through each loop step by step, it fails to update if I let the code continue running at it’s normal speed. It will keep on running even when the current database size is larger than the criteria specified in my IF statement. I have tried using DoEvents but this has been to no avail.

Any ideas? Perhaps a delay in the code, though not too long as it has to loop 1000’s of times, so it would be delaying for each loop.

Thanks

 

[PHV]

What about using the DoEvents function in your code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya Marco123 . . .

As always ... we know nothing until we can see the code and/or SQL! ... So ... post the code!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Marco123]

The code is actually a rather large piece of code.

The weird thing is, though, that it actually works on my home PC (as opposed to my work PC). The only differences being that my home PC has a higher spec, and is not running a monster SQL query that connects to several linked oracle tables before the loop (which contains the filelen function), where as on my work PC it is. Obviously at home I do not have access to the oracle tables and have therefore used a copy of the data already extracted, instead of running the SQL query.

Not sure what else could be the reason as to why it works on one PC but not the other as nothing else has changed…

 

[TheAceMan1]

Marco123 . . .

Try incorporating the following:

[blue]   Dim DQ As String
   
   DQ = """"
   If FileLen(DQ & Currentdb.Name & DQ) >[/blue]

Be sure to incorporate as shown!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Marco123]

Thanks but unfortunately it didn't work.

I think the issue is definitely PC related as it works absolutely fine on my home PC, but I just can’t get it to work on my work PC. Looks like I’ll just have to work around it….