limitations to VBA 2

[Zeroanarchy]

Hey, I seem to be having some problems with Access 2003, "
Sorry for the inconvenience". I have been hunting the web for answers and tried everyone. My question is what’s the limitation to an array in Access. Is there one? I have limited the problem down to a section of code that runs an array that needs to handle over 100,000 records. Has anyone come across this issue before or does anyone know if there is a limit to an array size.

I did notice that if I killed a few processes the Array would reach further than previous attempts. What’s interesting is that on some occasions the lot of it works but this is rare, 1 in every 10.

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[jrbarnett]

A few more optimisations:

Replace

DoCmd.RunSQL

with

dbs.Execute

Replace either of the first line items
Mid - Left - Right
with the equivalent string (Rather than variant) values

Mid$ - Left$ - Right$

Replace
If IsNull (condition)

with
If Len (condition & "") > 0

Another optimisation:
Rather than loading data into an array, could you load it into a table, then you wouldn't need to open a recordset within a loop handling the array.

These tables - are they local to your DB, from a linked table? ODBC link to another DB? Maybe further optimisation could be performed there.

One final point: there is no "Close #nFile1" line at the end of the sub and setting to nothing of the various object variables. This won't provide a performance enhancement, but may affect the output file.


John

 

[kjv1611]

Thanks for the notes on the memory. Those are a couple of good ideas (just calculating based on variable type, and putting break points in code AND THEN looking at the task manager). Some good things to remember.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Zeroanarchy]

Thanks Jrbarnett,

I have made the required changes, and I will have to run some tests tomorrow. I noticed you mentioned changing Mid to Mid$, I was not aware you could do this. I decided to check for some examples on the web and I found this useful page.

http://www.aivosto.com/vbtips/stringopt.html

Hope this helps others.

Thanks Kjv1611 for the star

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.