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

[Inspired]

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

 

[DCrake]

I have no idea what you are doing but I am sure there must be a better way of doing it. Why do you need to use make tables can you not use delete and append queries?

Have you used compact and repair.

 

[gemma-the-husky]

you could try putting doevents immediately before the filelen function. that might help

 

[Inspired]

The weird thing is, after further investigation, is 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…

 

[Inspired]

What I was planning to do was have another master database run the database that does all the calculations (the secondary database). When this secondary database reaches a certain size (from the IF statement), it would jump to the end of the code in the secondary database, and the master database would then close, compact and repair, and then re-open the secondary database to continue running.

Any ideas for a more efficient method than this?

 

[DCrake]

Compacting and repairing a database accross a network is not at all adviseable especially ones that are very large. Access needs to have double the size of the original mdb as free memory so that it can perform the C&R. You will most likely bring the network down if you try to compact an mdb over say 1 gig.

Is it not possible to copy a snapshot of your oracle data etc to your local pc and run the number crunching locally.

 

[Inspired]

Compacting and repairing a database accross a network is not at all adviseable especially ones that are very large. Access needs to have double the size of the original mdb as free memory so that it can perform the C&R. You will most likely bring the network down if you try to compact an mdb over say 1 gig.

Is it not possible to copy a snapshot of your oracle data etc to your local pc and run the number crunching locally.

Both the master and secondary databases can be on the C drive to run, the network is only used when running the initial SQL query to extract information from the linked Oracle tables.

 

[DCrake]

You say "Can" does this me Yes they are? or Yes they can be?

Also at what point is the system hanging? Whilst performing the Oracle SQL or after that locally?

 

[Inspired]

You say "Can" does this me Yes they are? or Yes they can be?

Well, ideally, they would both be run over a network but, failing this, they will have to be both run on the C drive. Currently, for testing they are run on the C drive.

Also at what point is the system hanging? Whilst performing the Oracle SQL or after that locally?

Essentially, the issue is occurring locally after running the SQL query. For some inexplicable reason, on my work PC the filelen(currentdb.name) function is not updating during code, where as on my home PC it does. The only differences, as detailed above, are that when running the model at home - instead of at work - my PC has a higher spec and does not initially have to run a large SQL query to extract data. At home, I have already extracted sample data into tables before running the model (while obviously commenting out the SQL query that extracts the data).

When I get back to work tomorrow I will investigate….