speed of VB applications versus Access VBA applications

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Due to installation issues, I've had to convert a VB application to an Access
VBA application. The resultant Access application process the data much too
slowly for our requirements. Any suggestions or references on speeding up
Access apps would be greatly appreciated.

A little background on the situation. We're taking *.csv data files that
are about 45,000 lines long (close to 3 MB in size), reading the line of
data in, doing some validation on each line, processing specific values in
the data, and writing out the new lines in a new *.csv file. Under the VB
app, the entire process takes only about 2 minutes; however in Access, it
takes about 1 hour to process the data.

Any help would be appreciated.

~Ernest
 
What sort of processing are you doing? There's no intrinsic reason for such
a difference between VB and VBA.

Can you possible import all of the data into a temporary table, do your
processing on the temporary table (ideally using SQL statements), then
export that temporary table back out?
 
You could put some displays in the VBA code to try to isolate where the time
is being spent, then try to optimize that part of the code. Is it being spent
 
eleong said:
Due to installation issues, I've had to convert a VB application to an
Access
VBA application. The resultant Access application process the data much
too
slowly for our requirements. Any suggestions or references on speeding up
Access apps would be greatly appreciated.

A little background on the situation. We're taking *.csv data files that
are about 45,000 lines long (close to 3 MB in size), reading the line of
data in, doing some validation on each line, processing specific values in
the data, and writing out the new lines in a new *.csv file. Under the VB
app, the entire process takes only about 2 minutes; however in Access, it
takes about 1 hour to process the data.

Any help would be appreciated.

~Ernest
Click to expand...



You haven't posted any code so it's hard to say but this does not sound
right. Why not comment out the processing tasks so it only reads each line
and does nothing with it? How long does that take? Now amend it, so it
reads the lines and simply writes an exact copy of each line with no
processing at all. How long does that take?
Perhaps now it will become clear where the bottleneck lies - in the read /
write operations or the processing. Then, provided you knew this, you could
post back with specific code and ask how to optimize it. For example, if it
was the processing each line which was slowing everything down, ask: How can
I speed up the following function?

Private Function ProcessLine(strLine As String) As Boolean

' Attempts to change the line from the old to new version
' Returns True if successful

End Function
 
Back
Top