NI Home > Community > NI Discussion Forums

LabVIEW

Showing results for 
Search instead for 
Do you mean 
Reply
Member
Scotty Parker
Posts: 17
0 Kudos

Parallel access of database

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.

Sincerely
Parker
Trusted Enthusiast
rolfk
Posts: 4,717
0 Kudos

Re: Parallel access of database

Scotty Parker wrote:

> 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.

Rolf Kalbermatter
Rolf Kalbermatter
CIT Engineering Netherlands
a division of Test & Measurement Solutions
LabVIEW ArchitectLabVIEW ChampionLabVIEW Instructor
Member
Scotty Parker
Posts: 17
0 Kudos

Re: Parallel access of database

Thanks for the advice.

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.

Parker
Trusted Enthusiast
rolfk
Posts: 4,717
0 Kudos

Re: Parallel access of database

Scotty Parker wrote:

> 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.

Rolf Kalbermatter
Rolf Kalbermatter
CIT Engineering Netherlands
a division of Test & Measurement Solutions
LabVIEW ArchitectLabVIEW ChampionLabVIEW Instructor
Active Participant
Brian_Beal
Posts: 932
0 Kudos

Re: Parallel access of database

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.
Active Participant
ryank
Posts: 312
0 Kudos

Re: Parallel access of database

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 :smileyhappy:. 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.

Regards,
Ryan K.
Active Participant
Brian_Beal
Posts: 932
0 Kudos

Re: Parallel access of database

Ryan-

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!
Active Participant
ryank
Posts: 312
0 Kudos

Re: Parallel access of database

Yes, the approach you use to opening a connection is exactly the approach to take to get around that problem. Not sure what to tell you about the other one except to maybe try it with a different DB.

Regards,
Ryan K.
Member
Scotty Parker
Posts: 17
0 Kudos

Re: Parallel access of database

Thanks for all the feedback guys.

I am using UDL for connection between remote systems. ODBC DSN just will not work with remote systems.

if your scenario is correct about multiple access from different systems being succcessful, I'm pretty sure this ain't a problem with the Access database. Anyone encountering the same problem as I am?

Ryan, can you attach your own "Open Database Connection" VI. According to your posts here, I presume the problem lies in the way the program tries to make a connection?

Parker
Member
Scotty Parker
Posts: 17
0 Kudos

Re: Parallel access of database

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!

Parker