RE: [MS_AccessPros] Re: Access Locking SQL Server

 

Thanks Duane. You've piqued my curiosity. I've done a little reading about this and will try some of these as I have time.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Pass-throughs are typically much faster and read-only. They support all of the many SQL Server statements and functionality that Access lacks.
 
You would need to use a little DAO code to modify the SQL property of the pass-through to manage the WHERE clause since a pass-through can't resolve the reference to a control on a form.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 5 May 2016 08:21:53 -0700
Subject: RE: [MS_AccessPros] Re: Access Locking SQL Server



Duane,
Thanks for the suggestion. How are pass-through different from Access queries? Here is one of the report queries. What would it look like as a pass-through?
SELECT tblPartMaster.PartNo, tblPartMaster.PMDesc_01, tblPartMaster.PMDesc_02, tblPartMaster.PMUOM
FROM tblPartMaster
WHERE (((tblPartMaster.PartNo)=[Forms]![frmPartMasterLabels3x4]![Combo2]));

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Doyce,
I would use pass-through queries where ever possible. Use them for control row sources as well as form/report record sources.
 
There is some debate about using "WITH (NOLOCK)" in your SQL Server statements but I typically use this for all somewhat static recordsets.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 5 May 2016 06:56:49 -0700
Subject: Re: [MS_AccessPros] Re: Access Locking SQL Server



John,


The form is also set for no locks. The user chooses a part number from a combo box then clicks one of 3 buttons to print one of 3 reports. One prints labels on a laser printer. The other two are two different reports that print to a thermal label printer. The reports have queries as their record source. All of the reports have No Locks. Could the combo box where the part number is selected be the culprit?


Doyce

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Doyce-

Did you check the settings on the form?  That has to be the problem.  Also, you mention a form, but then you say you're printing labels.  Is there code in the form that opens a report to print?  And if you're opening a report, what are it's lock settings?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On May 5, 2016, at 3:34 PM, winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Bill,

Thanks for your response. I've been busy the last couple of days and am now getting back to this. My client settings are for no locks. I have created a work around that seems to be successful so far by creating an Access table and in the load form of the switchboard I put in code to copy new records from the Part Master table to the Access table. We're just printing labels and I only need the part number, description and UOM. So far this seems to be working. Would it have some kind of lock on the record if the user leaves the part number displayed after they click print to print the labels? I ask because once the label comes out of the printer, the user tears them off and leaves the form open with the last part number still displayed.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

Doyce - For the default lock that each form will use when it is created go to File tab>Client Settings>advanced.

For existing forms, open the form in design view. select the form itself. Under the Data tab you will see a Record Locks property.

Did you try relinking the form? Could there be any other open forms like hidden ones that might have a lock on the table?

Bill

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

Bill,

Thanks for responding. There is a primary key in the table. It is the Part No. I believe everything is set to No Locks but I can't remember where to find that. Where is that setting?

Doyce

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

Doyce

I've never seen an Access front end lock up a table in SQL. What's your record locking set to on the GUI? I use "Edited record" for all my locks. But you also need to run down where the lock is staying put. 

Check that the table has a primary key and Access is indexing it. If it has a primary key you should be able to see it in design view. If it doesn't have a primary key or is actually a view you can delete it and link to it again. Access should ask you to choose the field(s) that will constitute a unique row.

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

https://mvp.microsoft.com/en-us/PublicProfile/35852?fullName=Bill%20%20Mosca

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

Hello Pros,

I have an Access 2013 db that is linked to my ERP system SQL server by ODBC. This database is somehow locking up SQL and I can't figure out why. It contains a form where users select a part number in the Part Master table from the SQL DB and it prints labels. When they walk away and leave the program open, I think it locks the record for the part number that used last. When this happens, the SQL server manager shows the status of that PC as being "suspended" and if another user tries to use a function that loops through the part master in our ERP system or tries to do something with that part number like ship it in Part Sales, they get a db timeout error. I have 2 other DBs connected to this same SQL server the same way and they never cause a problem. How can I figure out why this one is locking the server and fix it?

Doyce

 

__._,_.___

---

Posted by: winberry.doyce@roadsysinc.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (12)

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___