Opening and closing Access database in a loop causes an Error.

[jacy]

I am loading test conditions from an Access DB in a multiple nested loop. The loops successively drill into the DB. ei Temperature, Humidity, Power. Consequently the DB is opened and closed numerous (2000) times. The Errors returned are(-2147467259) Unspecified Error or (2147024882) System Resources low. I have disabled result recording in the edit sequence properties dialog. I do see a constant memory consumption, but of 128MB, it never gets below 40MB. I have enclosed the example sequence file I am using.

[Bob Rafuse]

Jacy,

"jacy" wrote in message
news:50650000000800000082350000-1007855737000@exchange.ni.com...
> I am loading test conditions from an Access DB in a multiple nested
> loop. The loops successively drill into the DB. ei Temperature,
> Humidity, Power. Consequently the DB is opened and closed numerous
> (2000) times. The Errors returned are(-2147467259) Unspecified Error
> or (2147024882) System Resources low. I have disabled result recording
> in the edit sequence properties dialog. I do see a constant memory
> consumption, but of 128MB, it never gets below 40MB. I have enclosed
> the example sequence file I am using.

I've seen problems with OLEDB (which I assume TestStand used behind the
scenes) with Access and SQL where rapid opening/closing of the sam
e source
(database) can generate errors. I don't know for sure, but I assume that
the changes from the last close are not fully propogated before the next
open is processed.

Getting back to TestStand, if all the tables you're querying are in the same
database, then you should just open the database once at the beginning and
close it at the end. Then do seperate table open/closes between the
database open/close.

Bob.

[Scott_Richardson]

You may want to consider using the Microsoft Jet 4.0 Provider instead of the ODBC driver. I found that in general this path to use a Jet database is faster and tends to have fewer problems. In the past I reported some memory leaks to Microsoft and they tended to be in the ODBC driver more than the OLE-DB Provider for Jet.

Jet has an algorithm to use available memory and it is
(((Total RAM on machine in MB - 12MB) / 4) + 512K).

Scott Richardson - NI

[Mark_Ireton]

As mentioned in another answer, it would be better to just open and close your connection once. I've attached a modified version of your sequence file that opens the database in the Setup section of Main Sequence then passes the database handle (aka reference) as a parameter to your sub-sequence. The connection is closed in the Cleanup section, which is handy since Cleanup generally runs in the case of any errors. This should really speed up your application since getting a connection is typically slow, especially if you are going to a shared database server.