Solved

make recordset read only

Posted on 2015-02-05
23
203 Views
Last Modified: 2016-02-11
Hi Experts,

I have the following code in an Access MDB linked to SQL\Access DB's.
Dim rsSchedules As New ADODB.Recordset
    rsSchedules.ActiveConnection = CurrentProject.Connection
    rsSchedules.Source = strSQL
    rsSchedules.Open
    Do Until rsSchedules.EOF
    'My Code here..
    Loop
    Set rsSchedules = Nothing

Open in new window

Now while the above code is running, the entire recordset is being locked, how can I make this read only?
0
Comment
Question by:bfuchs
  • 12
  • 5
  • 2
  • +4
23 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
ID: 40592581
try

Dim rsSchedules As New ADODB.Recordset
    rsSchedules.ActiveConnection = CurrentProject.Connection
    rsSchedules.Source = strSQL
      
      rsSchedules.LockType=adLockReadOnly
      
    rsSchedules.Open
    Do Until rsSchedules.EOF
    'My Code here..
    Loop
    Set rsSchedules = Nothing
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40592882
Hi Ray,
Nope that didn't help, see attached.
Untitled.png
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40592927
Actually I realized that for some reason, not only the records that are part of the recordset are being locked, just the entire table is being locked, and as soon as I close the recordset, everything gets released..any clue?
0
 
LVL 84
ID: 40593249
SQL Server determines when/how to lock the tables, so setting the cursor lock type won't really help much. You can try managing your indexes on the tables involved in the SQL statement to see if you can improve on this, but in general this is not behavior you can do much about.

What's the goal in this exercise? Are you having conflicts or lock issues when you run the code?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40594197
Along the lines of Scott's thinking, what breed of user will be opening this recordset?

If you never want them to edit the recordset, perhaps a workaround would be to create a SQL Server View that sleects that table, and manage the users/roles on that view so that it's readonly.  Then link from Access to that view.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40595128
One way is to add a calculated field to the query.

Set rs = CurrentDb.OpenRecordset("Select *, 1 from tbl")

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
ID: 40597389
Hi Experts,

@Scott,
Basically what we do here is as follows, we are selecting a set of records, looping record by record and uploading it to a Web App.
The problem is that when its a large recordset then the table gets locked and other users are not able to work. (for some reason this only happens when selecting a large qty of records, for example a wide date range selection that contains thousands of records).

@Jim,
I tried creating a view but I'm not sure how to manipulate the permissions to set this as read only, currently all users are connecting as SA, meaning the user/pwd set for the Users DSN is from SA.
Would that require to change all that, or can I create a view that is read only for SA as well? if yes, how can I accomplish that in SSMS 2008, is there a sql command I can execute to make this view read only?

@hnasr,
In Access when I just add that column to the select statement, its still editable.
Besides, the current SQL statement used in the code is anyway read only, as its combined from multiple outer joins in addition to the fact that some are SQL tables and some are Access based.
0
 
LVL 84
ID: 40609627
So your goal is to lock ONLY that block of records that is returned by the Recordset?

If so, can you show us the SQL that's used to create the Recordset?

Also, what version of Access and SQL are you using?
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40611257
@Scott,
1- No, I don't need it to lock any records at all, all I need is to read values therefore a read only recordset is fine.
2- below is the code that constructs the SQL.
strSQL = " SELECT Employeestbl.SocialSecurityNumber as SSN,PatientsAR.AR, " & _
             " PatientsEmployeesSchedule.ID as scheduleid, " & _
             " PatientsEmployeesSchedule.PatientID,PatientsEmployeesSchedule.ProcedureCode," & _
             " pat.BillingID, " & _
             " ft.ReqBillingID, " & _
             " PatientsEmployeesSchedule.EmployeeID, PatientsEmployeesSchedule.To," & _
             " PatientsEmployeesSchedule.From, PatientsEmployeesSchedule.Day," & _
             " Employeestbl.LastName as eLastName, " & _
             " Employeestbl.FirstName as eFirstName, " & _
             " pat.Supervisor, " & _
             " pat.LastName as pLastName, " & _
             " pat.FirstName as pFirstName ,pat.HomePhone,PatientsEmployeesSchedule.Facilityid," & _
             " pat.address, pat.City, pat.State, pat.Zip as pZipCode, " & _
             " pat.HHC, pat.Sex, format(pat.DateOfBirth,'MMDDYYYY') as DOB, " & _
             " ft.abbreviation as FacName " & _
             " FROM ((((PatientsEmployeesSchedule  " & _
             " left outer JOIN Employeestbl ON PatientsEmployeesSchedule.EmployeeID = Employeestbl.ID) " & _
             " left outer JOIN PatientsAR ON PatientsEmployeesSchedule.PatientID = PatientsAR.PatientsID ) " & _
             " left outer join patients pat on pat.ID = PatientsEmployeesSchedule.PatientID)  " & _
             " left outer join Facilitiestbl ft on ft.id = PatientsEmployeesSchedule.Facilityid)  " & _
             " where ft.UpdateSantrax =1 and " & _
             " PatientsEmployeesSchedule.day between #" & dtfrom & "# and #" & dtTo & "#"

    rsSchedules.ActiveConnection = CurrentProject.Connection
    rsSchedules.Source = strSQL
    rsSchedules.LockType = adLockReadOnly
    rsSchedules.Open
    Do Until rsSchedules.EOF
    'My Code here...
    Loop

Open in new window

3- Access version is 2000/2003 and SQL is 2008.

Thanks,
Ben
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 100 total points
ID: 40611372
do you need WITH (NOLOCK) ?

FROM PatientsEmployeesSchedule WITH (NOLOCK)
LEFT OUTER JOIN Employeestbl WITH (NOLOCK) ON PatientsEmployeesSchedule.EmployeeID = Employeestbl.ID
LEFT OUTER JOIN PatientsAR WITH (NOLOCK) ON PatientsEmployeesSchedule.PatientID = PatientsAR.PatientsID
LEFT OUTER JOIN patients PAT WITH (NOLOCK) ON PAT.ID = PatientsEmployeesSchedule.PatientID
LEFT OUTER JOIN Facilitiestbl FT WITH (NOLOCK) ON FT.id = PatientsEmployeesSchedule.Facilityid

Open in new window


Please note, if those tables are in SQL Server then the columns "To" and "From" should be

    , PatientsEmployeesSchedule.[To]
    , PatientsEmployeesSchedule.[From]

and that you should avoid using reserved words as objects

Ben,
I have no experience using Access with SQL Server so the above may be a dumb suggestions.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40611386
No, I don't need it to lock any records at all, all I need is to read values therefore a read only recordset is fine.
But that is just it.  As written your original code produces a fire hose (read only, forward only) cursor.  So I suspect something else is going on.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Author Comment

by:bfuchs
ID: 40611426
@PortletPaul,
I tried Adding With (NOLock) after the EmployeesPatientsSchedule table and getting the error "Syntax error in JOIN operation", actually not all tables used here are sql driven, some are Access tables but EmployeesPatientsSchedule is.

@Anthony Perkins,
What do you think it possible is?
According to Scott McDaniel's first comment, that recordset could still be locked by SQL.
In general, all I do here is read a record compose a XML and submit it to a Web server App.
And it works fine with small recordsets, only when selecting large a recordset that it gets locked, and that happens immediately.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40611603
@PortletPaul,
It looks like you got it!
I created a view with (nolock) and used that view in the SQL instead of the table.
Actually this does not turn it to read only, however it solves the problem.
Still conducting some tests, will keep you posted.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40613118
@PortletPaul,
I realized something interesting, that when I open the recordset with a months data (using nolock option) then its fine, but when I select 2 months of data then on the server I see the same processid blocking itself dozens of times, and this happens as soon i open the recordset, can you explain to me why?
See attached.
Untitled.png
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 40613347
You do realize you are blocking yourself and this happens because your query is attempting to use parallelism.  So either disable that (MAXDOP = 1) or what you really should be doing and that is optimize the query.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40613364
Start by using SSMS with a query like this:
DECLARE @dtFrom datetime = '20140101',
    @dtTo datetime = '20150217'

SELECT  e.SocialSecurityNumber AS SSN,
        a.AR,
        s.ID AS scheduleid,
        s.PatientID,
        s.ProcedureCode,
        pat.BillingID,
        ft.ReqBillingID,
        s.EmployeeID,
        s.[To],
        s.[From],
        s.[Day],
        e.LastName AS eLastName,
        e.FirstName AS eFirstName,
        pat.Supervisor,
        pat.LastName AS pLastName,
        pat.FirstName AS pFirstName,
        pat.HomePhone,
        s.Facilityid,
        pat.address,
        pat.City,
        pat.State,
        pat.Zip AS pZipCode,
        pat.HHC,
        pat.Sex,
        REPLACE(CONVERT(varchar(10), pat.DateOfBirth, 101), '/', '') AS DOB,
        ft.abbreviation AS FacName
FROM    PatientsEmployeesSchedule s
        LEFT OUTER JOIN Employeestbl e ON s.EmployeeID = e.ID
        LEFT OUTER JOIN PatientsAR a ON s.PatientID = a.PatientsID
        LEFT OUTER JOIN patients pat ON pat.ID = s.PatientID
        LEFT OUTER JOIN Facilitiestbl ft ON ft.id = s.Facilityid and ft.UpdateSantrax = 1
WHERE   s.[day] >= @dtFrom

Open in new window


Next make sure the tables are indexed appropriately.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 40613367
I just fixed the following lines:
        LEFT OUTER JOIN Facilitiestbl ft ON ft.id = s.Facilityid
WHERE   ft.UpdateSantrax = 1

As written that is not an OUTER but an INNER join.  If you really want a LEFT join then you should write it like I did.  If not, then why not write it for what it really is:  an INNER join.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40613489
Hi Anthony,

So either disable that (MAXDOP = 1)
Do you think that itself could solve the problem?

What is the syntax to use that? is it a database setting?

Re optimizing the query, the main issue is that one table is an Access table (PatientsAR).

Perhaps I should try removing this table and see if that problem goes away?

Next make sure the tables are indexed appropriately

I attached the sqlplan of the query the way you revised (without the access table), can you see from there what other improvements can be done?

While the table is still in Access, its not possible to use REPLACE(CONVERT(varchar(10), pat.DateOfBirth, 101), '/', ''), unless I create a view that include this field.

About the Inner join suggestion, you're absolutely right.

Thanks,
Ben
PatEmpSched.sqlplan
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40615203
@Anthony,

For testing purposes, I ran the code without the access tables, also removed the formatted column and changed the join for an inner join, however nothing helped, the same issue occurred.
Therefore I guess the problem lies in the parallelism issue..

Please let me know how to proceed.

Thanks,
Ben
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 40615625
You really should attempt to optimize the query by refactoring and adding appropriate indexes.

At the very least you may want to check out the execution plan you posted.  Notice how it is suggesting you are missing an index.  That is no to say that you should blindly follow the suggestion, but it is certainly something to consider.  I would be more inclined for an index on EmployeeID + Day.

You also may want to verify that you have indexes on the following:
PatientsEmployeesSchedule.PatientID
PatientsEmployeesSchedule.FacilityID
Employeestbl.ID
Patients.ID
Facilitiestbl.id + Facilitiestbl.UpdateSantrax

I would migrate the PatientsAR to SQL Server and make sure there is an index on PatientID

Notice also how you have Parallelism all over the place.  That per se is not a problem, but it may explain your blocking.
The syntax for MAXDOP is just place this at the end of your query:
OPTION (MAXDOP 1);

Inspect the Execution Plan with and without.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40615802
@Anthony,

1- What is the rule of indexing re multiple columns, when is an index appropriate for a single column and when does it need to be based on multiple columns?
I see here you're suggesting EmployeeID + Day and Facilitiestbl.id + Facilitiestbl.UpdateSantrax, why for example its not enough the fact that each of those is an index by itself? (Except for UpdateSantrax that wasn't indexed).

2- Re solving the blocking issue, I think very likely the MAXDOP option will solve the issue, however i must first convert the table in order to test that, as if the task is done by Access then it will not recorgnize that command option.

3- What about if I use a view instead of table PatientsEmployeesSchedule, and include that MAXDOP option there (The way i already did for NOLOCK option), would that help?

4- About the execution plans, i am not familiar with that, is there something specific i should be looking for?

Thanks,
Ben
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40617858
@Anthony,
I made some research and found out that its not possible to have that option MAXDOP saved within a view.
Therefore I am trying now the following,
Created a Pass-Through query in access with those options (with (nolock) and OPTION (MAXDOP 1)).
then I use this query in my vba code instead of the table.
So far this eliminates 90% of the problem.
I realized in activity monitor every once in a while, the blocking issue raises for a split second, then it goes away..
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40620438
@ All experts,

In the end after extensive testing I came to the following conclusion.

1- Using Pass-Through query versus linked table or view, is the solution for this problem.
2- Adding NOLOCK option helps the recordset open much faster.
3- However I am using the other two options as well (MAXDOP & adLockReadOnly).
4- The recommendations Anthony posted re optimizing is worth to implement regardless.

Therefore I guess will accept all posts as possible solutions...

Thanks to all participants!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now