LabVIEW, MSAcess, and Database Toolkit (Exclusivity Problem)

[DL84]

Hi all,

I have recently developed an application that's used for testing production products and sends the final test data to a MS Acess database via the database connectivity toolkit.  We've run into a problem with it though.  This program needs to be able to run at all times, its a high flow production line.  During production though, we have a large group of engineers that need access to this data.  Its set up so that only myself and one other person have write access to the directory containing the database, everyone else can only open it as read only.

The problem I'm running into is that whenever the database is open by one of the two engineers with write access OR any one of the other people just going in to review data as read only - the production tester isn't able to save records their creating in LabVIEW.  No warning or notice is given to the user in form of error to indicate the data wasn't stored.

I'd like to get this problem fixed but the last time I tried to use the Username/Password inputs on the connectivity VI I ended up locking up access and we finally resorted to just making a new database.  Given the amount of data in there now, you could say I'm a little "gun shy" about randomly fooling with the security settings again without a good idea of how to accomplish fixing this.

Basically I just want for that database to be opened exclusively by labview, and prempt anyone who is logged in (aside from one of us with write access...its a small enough group we are fine with just closing the test program for a few minutes while we make changes)

Any ideas on how to do this?

[Dennis_Knutson]

The best recomendation I can give is to get rid of Access. It's really not suited for the kind of 'access' rights you seem to need. Fine for desktop applications but not much more. I went through the same thing. While waiting for IT to install a SQL Server database, Access was used temporarily to do some testing. After a day of constant problems with too many users trying to connect at the same time (some doing reads and some doing writes), we all just gave up. Once SQL Server was installed, all of the problems went away. If you don't want to spend the monely on SQL Server/Oracle, check out SQL Server Express or MySQL.

[DL84]

Hi Dennis,

Our IT guys tend to push SQL also.  How much work is it to switch over to the other?  

You know I'm not sure if its worth it at this point, at most we only have 1-2 people on the database at once.  The test computer is always on which brings that number to 1, and it only happens maybe once a day that someone looks at the database and only to check some values in 1 record - then they close out.  Aside from the tester, no one else is in there for more than 1-2 minutes tops maybe once every other day.  Its just during that time if the user submits his report in that 1-2 minute window - we don't find out about it until someone needs to view that record.  

A worst case scenario though is if someone gets in for their 1-2 minute window and gets a call or gets distracted and forgets its open 😕

I did try some quick research....it looks like one recommendation might be to make a "front end" basically another database with a form linked to the data stored in the tables of the original one.  That way anyone viewing doesn't go into the storage database itself.  I haven't tested this yet though...one question comes up -if they are in this front end thats linked...I wonder if that is considered a "constant connection" to the storage database and we'd still run into the same problem with the storage one "thinking" its open anyhow...

I dunno this database stuff is a bit loopy imho.

[Dennis_Knutson]

Since you are using the database toolkit, the transition for you should be next to nothing.

I've never used the toolkit, just LabSQL. I seem to remember setting a connection timeout in either ODBC setup or in my code. If the usage is a short as you say, I think you can trap an error an retry.