Unable to get data from MS Access due to locked DB

Issues related to label design (working with databases, data processing, RFID encoding etc.) and printing (from NiceLabel Express, NiceLabel Pro, NiceForm and NicePrint)

Moderators: Georges, milos, NiceLabel Support Team

Post Reply
snekoza
Newcomer
Posts: 2
Joined: Tue Apr 23, 2019 9:49 pm

Unable to get data from MS Access due to locked DB

Post by snekoza » Tue Apr 23, 2019 10:28 pm

Hi, i just started using NiceLabel for printing data from MS Access database, that is on shared network folder, however only the machine that updates the DB and NiceLabel is connected to it. Whenever I start the DB trigger I get the first label, then the database is locked and stays like this for long period of time, while the data I need to use as trigger and print it keeps coming every 2-5 minutes. Due to DB being locked it cannot be updated in real time and I am unable to print the label for each product that comes out. Am I missing some simple setting or this is more complex issue? I even tried to use file´s timestamp as trigger, getting printed every second or third label, as the DB lock releases sometimes even after 10 minutes. I was surprised I couldn´t find any solution nowhere I looked, please let me know if there any way to make it work.

Thank you in advance to everyone.

User avatar
dado
NiceLabel Development
NiceLabel Development
Posts: 691
Joined: Tue Sep 12, 2006 12:50 pm
Location: Kranj, Slovenia
Contact:

Re: Unable to get data from MS Access due to locked DB

Post by dado » Wed Apr 24, 2019 7:18 am

Hi

Printing from MS Access database should work on network drives the same way as on local drives.
I have a sample here where we use .accdb file on a network share. And when a new record is added the Database Trigger executes and something gets printed. And after the print the Automation updates the record in the database with another value.
In our sample user1 from computer1 is adding the data to the .accdb file. Automation runs under user2 on a computer2. And both users and computers are in the domain.

I am curious how do you know that the database is locked and that is causing the trigger not to read it?
What if you test all of this on a computer drive, does it work then?
When you connect the trigger to the file... do you select connection type "Microsoft Access" or maybe ODBC, Ole DB...?
If you use "Microsoft Access" then if you click on the "Advanced Setup" there you have a tab "Advanced" and under that tab you will find the "Access Permissions". What do you have selected there?
Also it would be good to test all this on another computer/network to see if thing are working in another environment.

Regards, Damjan

snekoza
Newcomer
Posts: 2
Joined: Tue Apr 23, 2019 9:49 pm

Re: Unable to get data from MS Access due to locked DB

Post by snekoza » Wed Apr 24, 2019 11:42 pm

Hi Damjan,

thank you for your reaction. I will try to describe my problem more closely:
We produce styrofoam blocks, new block comes out of the mold aprox. every 5 minutes. All the parameters (Number of the block, weight, presure, operator´s name, type of material...and so on) from each block are saved by blockmold SW (that we have no control of it, it came with the machine) into Access DB file with .mdb extension. I use MS Access connection, triggered by increased number of the block, which indicates that new row of records was saved, I want to print variables from that last saved record - the parameters that were used to produce that block. When triggered, another file of the same name with .ldb extension appears in the same folder of the DB (MS Access lock file - the file is used to keep track of all users that are currently accessing the database.), this stays in such locked state much longer than the new record of the newly produced block comes out, but the records are not updated nor (logically) triggered to print new label for the new block. I did tested it also locally with the same result. The Advanced configuration - AUTHORIZATION of the connection is set to SHARE DENY NONE, I tried also FILE trigger, exactly the same result. Whenever I stop the trigger automation and close it, the locked file .ldb disappears.

My next step would be setting ODBC connection to the DB, however I am surprised I am the only one to have such problem.

Thank you once again for any suggestion.

Regards, Michal

User avatar
dado
NiceLabel Development
NiceLabel Development
Posts: 691
Joined: Tue Sep 12, 2006 12:50 pm
Location: Kranj, Slovenia
Contact:

Re: Unable to get data from MS Access due to locked DB

Post by dado » Fri Apr 26, 2019 8:48 am

Michal

I went through our code and what we do is once we open the connection to the Access database file we leave it open. This is because of the optimisation for any future readings of the data from the same database. It would be to slow to always reopen the connection if the user would quickly add large portions of data to the database. And opening the database with Share permissions is the correct way so that other users can share the data in the database.
We do close the database but that happens around every 5 to 7 minutes in case it is not used. But if you use database trigger the database is constantly used for fetching the data so there would be no database closing.

Is there any way that the application you use exclusively locks the database and therefore cannot open the .mdb file because NiceLabel already opened the connection?
Does updating work if you use MS Access to add a value to the .mdb file?

Regards, Damjan

Post Reply