make recordset read only

Hi Ray,
Nope that didn't help, see attached.
Untitled.png

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?

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?

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.

One way is to add a calculated field to the query.

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

Select allOpen in new window

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.

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?

@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

Select allOpen in new window

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

Thanks,
Ben

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.

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

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

@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

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

Select allOpen in new window

Next make sure the tables are indexed appropriately.

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

@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

@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

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

@ 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!