LabView DB Tools Select Data VI crashes with Access

[Chris Barrett]

I am using the DB Tools Select Data VI in a manner very similar to the example in the Database Connectivity Toolset User Manual, fig 3-21. I am extracting data from a table in Microsoft Access, using OBDC.
Most of the time the query works fine. Occasionally LabView hangs in the middle of this VI, causing the whole PC to crash and requiring a re-boot. This happens on more than one PC, and happens when running LabView or when running compiled code. A bitmap of my VI is attached.

[JellyBeans]

Few suggestions here

1)Make sure that MSAccess is closed when you are running the VI
2)Try using UDL file to connect to Access and choose the Jet OLE DB Provider 4.0 as driver.
3)Can you try running EXECUTE SQL and FETCH ALL VI in place of select and see if you get the same behaviour!

A Rafiq
NI

[Chris Barrett]

Using UDL instead of OBDC improves the situation. It now runs 57 iterations before crashing, instead of 12. But it's still not acceptable. Attached is a very simple VI which reliably shows the problem.

[JellyBeans]

OK well first all you should not open and close connection to the DB everytime in the FOR loop because that is a lot of overhead. Why not opening the connection once and in the for loop do multiple selects and then in the end close the connection.

Second if you want to run the select command in the FOR loop, replace select with advanced VIs (EXECUTE,FETCH and FREE) to essentially do the same thing. The advantage you get is again less overhead.

Third i wasn't sure what kinda crash you get??error# or message.....

I've modified your VI to reflect the changes and attached it. Hope it would make sense.

A Rafiq
NI

[Chris Barrett]

Your modified VI runs OK, and managed 97 iterations before it crashed. So it's an improvement.
There is no error message. If I have the "lightbulb" on, then it always stops with the progress arrow in the "Select Data" VI. If I then abort LabView within a few seconds (using "Control-Alt-Delete"), the PC carries on OK. If I leave it for much longer, the PC stops responding to anything. I have to power down, and run ScanDisk. On one occasion I had to re-install Windows98. This crash gets more and more destructive the longer you leave it!

[JellyBeans]

What if you leave the ORDER BY string out of the query and just leave "SELECT * FROM tablename", and run the VI thats i sent you. do you still get the same hang?

Try just reading the Variant array (without conversion) just to narrow down to something. Query shouldn't hang Ur PC.

Can you try on someother machine, accessing the same database?

A Rafiq

[Chris Barrett]

I tried running the VI which you sent (with "EXE", "FETCH ALL", and "FREE" VIs).
This crashed after 97 iterations. Pressing the lightbulb showed the "FREE" VI greyed-out. Quickly pressing control-alt-del and ending LabView allowed my PC to carry on (but with no task bar and no Explorer). So I ran your VI again, and this time it crashed after about 30 iterations. Power-down and ScanDisk were needed!

I built a very simple version of my original VI, as you suggested, with the query removed. This also crashed after 97 iterations. A bit map is attached, which also shows the details of the table which is being read from.

I can't get onto another machine at the moment (it needs to keep on working!). Any further ideas?

[Chris Barrett]

I tried running the VI which you sent (with "EXE", "FETCH ALL", and "FREE" VIs).
This crashed after 97 iterations. Pressing the lightbulb showed the "FREE" VI greyed-out. Quickly pressing control-alt-del and ending LabView allowed my PC to carry on (but with no task bar and no Explorer). So I ran your VI again, and this time it crashed after about 30 iterations. Power-down and ScanDisk were needed!

I built a very simple version of my original VI, as you suggested, with the query removed. This also crashed after 97 iterations. A bit map is attached, which also shows the details of the table which is being read from.

I can't get onto another machine at the moment (it needs to keep on working!). Any further ideas?

[JellyBeans]

From the diagram it seems as if you are opening and closing the connection everytime you want to read data. DO NOT put open and close in the while loop. Have just the SELECT or the (EXE,FETCH and FREE) VIs in the while loop.

Try slowing down the loop little bit for troubleshooting purposes!!
A Rafiq

[Chris Barrett]

The OPEN DB and CLOSE DB VIs are only in the while loop for test purposes. In the real application, there is a lot of other activity going on between each CLOSE and the next OPEN.

However, your modified VI (using EXE, FETCH ALL and FREE) has the connection open all the time - and STILL falls over.

Have you tried writing to an Access database using your VI, to see how many iterations it manages on your PC?