VB6 DAO Problem - itterative process causing delays


I'm going round the twist with this...

I have a VB6 application which is using DAO to access a database
(Please - no lectures on ADO, I know what I'm doing with DAO!!)

Ok, problem is this....

importing a large csv file (189000 lines, 7 fileds per line)

Import this into various tables fine - takes a a couple of minutes...
the problem is when I then process these tables.

The progam is basically split into 2
Process A:
table name held in RECORDSETtables

      For each table ( 0 - 6)

            for each day (1 - 365)

                  call RoutineB(table,day)

            next day

      Next Table




Routine B (sTABLE as string, sDATE as string)

set RecordsetTABLE = db.openrecordset(open table Day&date,
      if not error
            table exists
            exit routine
            ' don't need to process as data already exists
            'create table to store data in later
      end if

set RECORDSETdata = db.openrecordset(select data from sTABLE with
Specified sDate)

for each record in RECORDSETdata

      long and itterative calcualtions.
      store data in ARRAY(48,30)

next RECORDSETdata
close RECORDSETdata
with RecordsetTABLE
      for each line in ARRAY(48,30)
            .add new
            .fields(1-48) = ARRAY(line,1-48)
      next line
end with


EXIT ROUTINE B back to calling routine A

Ok - the problem is, that as it processes each day, the time taken
increases from less than 1 second perday to over 7 seconds for the
same amount of processing - ie each day contains the same (more or
less) amount of  data

PLUS, when I quit the program, it takes an absolute age to return back
to the design environment.

So - the question is this: why is this happening and what can I do to
prevent it? When I am processing 8 tables with 365 entries in each
(2920 days in total) it gets stupidly long (a couple of hours) to

I'm not using  ODBC so I can't use the .StillExecuting to check on the
opening of the recordsets, though I have a feeling thtt it is this
which is causing the "lag" - opening and closing the tables - thhogh I
couldbe wrong.

I wondered if this was a limitation of the Jet database (mdb file) and
thought about using dbase V instead - but can't find how to use
CreateDatabase for dbaseV - I can open one fine - but I can't  create
one. - any help on this please?

Should I erase the array one the data is saved?

Is ADO the only option?

Please help - this is driving me potty!!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I do not think the problem is DAO (or ADO since I think you will see the same result).  I think the problem is Access itself.  You may want to look into ADOX for some compact and repair features to help you out.

ictisAuthor Commented:
Thanks - but to compact and/or repair after each RoutineB will kill it completely!! What about using a different format - dbase 5??
IMO you will still have the same problem.  In all honesty I think you may need to switch databases.  

The thing about your application, is that to really test something like that you will actually need to do it, because on a smaller scale everything will work fine no matter the format.  The only way to know for sure if the problem will persist with dbase 5 format is to try it.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ictisAuthor Commented:
Kinda makes sense :-)

So whatch you gona do?

I disagree with both of you.  I process large files all the time into an Access db and don't see any slowdown.  Granted I use ADO, but via the OLEDB Jet Provider 4.0

As far as your code goes, the only thing that jumped out at me is that you are inserting via the Recordset object.  Try inserting via Database.Execute "Insert (fld1,fld2...) values (v1,v2...)
This may help because the recordset object is very heavy duty, it already holds data you've already inserted, so it slows you down.  Using the Database.Execute method you will avoid the recordset all together.  I suspect this will save some significant chunks of time and memory.

Other than that...
...if you are using DAO 3.5, try to do the same task in DAO 3.6
Furthermore, if you suspect that the size of the file is a problem (btw, what is the size of the file when it starts getting slow?), create 7 separate databases, treat then as tables and see if your speed problem goes away.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Is RecordsetTABLE referring to a table which is not a parameter of Routine B? If not, just leave it open the whole time rather than opening and closing it within the function. You shoudl get a fairly good speed increase if you do that.

If it is the same Recordset tables that is in Routine A, then your psuedo code should look like this:

Process A:
table name held in RECORDSETtables

   ** Open RECORDSETtable

     For each table ( 0 - 6)

          for each day (1 - 365)

               call RoutineB(table,day)

          next day

   ** Close RECORDSETtable

     Next Table


ictisAuthor Commented:
Looks like robdogg10 is going to get the points !!

database.execute ("INSERT INTO....")

Seems to be taking a constant 1 second per day now to calculate and store as opposed to 1 second raising to 8!!

ictisAuthor Commented:
Still using DAO this drastically improves matters. Now all I need to do is streamline my code for the calculations to imrpove on the 1 second per day :-)

File size doesn't appear to be a problem

BTW Its 3.51 I'm using...

Time to revisit some of my other code I think... :-)

Thanks again - points well earned
DAO 3.6 resolved some file size issues, IIRC, you might want to look into it.   But speedwise, DAO 3.51 is pedal to the metal.
ictisAuthor Commented:
Thanks - I'll try changing the reference.
ictisAuthor Commented:
Thanks to everyone else for their contributions
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.