I need to have parallel access to a database from two different application.
The first application is a TestStand program that will open this database and then write into the database. This open and write process will occur continuously as new data are needed to be logged inside the database.
The second application is a VI that will fetch data from this database. This VI will open the database and read data from it. This VI will also run continuously as it attempts to extract any new data entered into the database. As you can see, there is a potential problem. MS Access allows only one access at any time and therefore for my two applications to open and access the database at the same time has been causing me problems.
I have tried opening the database and then freeing the database after every data entry but still it won't work very well.
Any suggestion on the best method to go about doing this? I only intend to use LabVIEW features. I'm using especially the database connectivity toolkit. Thanks for any help.
> As you can see, there is a potential problem. MS Access allows only > one access at any time and therefore for my two applications to open > and access the database at the same time has been causing me problems.
Well, "real" databases don't have such a problem. They allow concurrent access by multiple clients.
> I have tried opening the database and then freeing the database after > every data entry but still it won't work very well. > > Any suggestion on the best method to go about doing this? I only > intend to use LabVIEW features. I'm using especially the database > connectivity toolkit. Thanks for any help.
The only way if you want to stay with the database as it is, will be to actually relay the database access either from tests tand to your LabVIEW app or vice versa. I would assume both ways to be a pitta. If you would relay from Teststand through LabVIEW you couldn't use the build in Teststand logging feature and would have to do everything yourself which is probably not a very fine solution. The opposite would require you to somehow pass retrive the data through Teststand which seems not very simple either.
What do you mean by a "real" database? And so, how do I configure MS Access such that it can handle multiple clients. I know this could be done but I don't know how to.
I've had some success while testing the execution on one local machine, with the two applications and the database inside the same machine.
However, my scenario requires two different machines. One machine will contain Teststand and the database while the other the VI that will fetch data from the database. This is when the problem occurs.
When I run my VI on the remote computer while Teststand is also logging into the database on the other computer, my VI was able to capture data from the database. However, TestStand halts to a run time error.
Wh y is this happening? I know I'm quite close to making it work but it just seems to me that there's no way that two clients can access a database at the same time.
> What do you mean by a "real" database? And so, how do I configure MS > Access such that it can handle multiple clients. I know this could be > done but I don't know how to.
Well, I don't consider MS Access a real database. SQL Server, Oracle and such are databases, but MS Access is just an office application which happens to have some database functionality.
I have no experience with MS Access. I know that I can do what you want without any problems in SQL Server and other database applications, but they are also in a different price range than Access.
> When I run my VI on the remote computer while Teststand is also > logging into the database on the other computer, my VI was able to > capture data from the database. However, TestStand halts to a run time > error.
This really seems to be a problem with Access then. I could possibly understand if you had problems when both applications are on the same computer as that might mean, that the ODBC driver has a limitation and can only serve one application at one time. But not working from different computers seems to hint that MS Access itself can't handle multiple connections at the same time, and the ODBC driver takes care of that when you access the database from multiple applications on the same computer.
Not that I really want to defend Access, but this should not be a problem for it. I am currently using a network database that takes data from 4 different VI's(on different machines) and multiple Access users, all at the same time. There is an occasional blip that I haven't figured out yet, where the database claims to be locked by a user, but that is rare.
Are you using a file DSN? You can set up the database for multiple users. I believe that the instructions are in the book that came with the Database Connnectivity Toolset. If you can't find them, let me know and I will see how I did it.
The blip you are seeing is probably this: http://support.microsoft.com/default.aspx?scid=kb;en-us;307640 It may also be what Parker is describing when he says the DB app isn't working "very well". Basically, you can't access an Access DB too often. Microsoft's "resolution" is to just keep trying till it works . Not exactly an elegant solution, but it should probably serve for most apps, if you get an error then just wait a bit and try again until you don't.
That probably is it. I learned a while back to use my own "Open Database Connection" VI. It is just a loop that tries to open the connection, once per second for 10 seconds, until it is successful or it doesn't work. That greatly reduced the number of errors, but there is still an occasional problem. I have seen the .ldb file become corrupt for some reason, and I have seen where the data doesn't get written to the table, or should I say can't be seen by Access, but I am able to query that table for the data(one time) and it is there. It is then gone forever!
Brian, how did you make the Access database accessible to multiple users. I know there's an option in Access itself stating whether to have the database "shared" or "exclusive". I always had it set to "shared". I also set the option to "No locking".
I can't seem to find my Database Connectivity Toolset book now.Someone removed it from my desk!