best ADO properties for my .adp recordsets

I have 8 computers on my access2007 front end , connected to my 2008 sql backend. Most times the users are working on the same patient chart (medical office) and changing data on the same forms. I have an initialize module that creates the same connection and a function that returns the recordset for just about everything. I am getting more and more write conflicts and I find the ado cursor type, location, and lock very confusing. Any advice as to the best way to set up the recordset function?

Public Function GrabRst(strSQL As String) As Recordset
Set GrabRst = New Recordset
With GrabRst
    .ActiveConnection = CurrentProject.Connection
    .Source = strSQL
    .LockType = adLockPessimistic
    .CursorType = adOpenDynamic
    On Error GoTo cont
    .Open                   ' Grabrst.state=0 if closed  =1 if open
End With
Exit Function
cont:
MsgBox "Unable to open a recordset using the statement: " & strSQL
End Function


then throughout the VBA I do this all the time:

private sub whatever()
dim rst as recordset
set rst=grabrst("select * from dbo.patients where [visit#]=" & lngVisit)            'this is one example of a select
<work with rst>
set rst=nothing
end sub

any advice what would be a good cursor loc, type, lock, etc?
I have noticed that rst.updatebatch works all the time.
but like i said i am getting many more write conflicts ( i know that I update the recordsets programmatically and that I have to update the forms before updating the rst...still wondering if i should alter the recordset properties in the GrabRst function.)
Brant KerseyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brant KerseyAuthor Commented:
it would be great if someone could explain the settings for cursor locattion, locktype, cursor type in easily understandable terms, and the best time to use them. i just dont get the pessimistic, optimistic, adUseClient vs Server, keyset vs dynamic, etc, etc, and when and which work best. The msdn and other sites just dont explain them well to me. Thanks.
0
Vadim RappCommented:
1. If you are not going to update the records in your recordset, open it as adOpenForwardOnly,adLockOptimistic. If you are going to update them, see #3 below.

2. Cursor location should be always adoUseClient, if you reuse currentproject.connection. The reason is because this oledb provider is not real ADO, it's modified ADO specially for Access, and it does not support server-located cursors, database transactions, and some other things.

3. Generally, the best practice is not to interfere with Access own handling of the data. All data manipulations should be done by Access, rather than by your recordset. If you want full control on the data, use Visual Studio, that's what it's for. If you use Access, let Access work with the data, especially update it. Not following this and manipulating the data behind Access back is likely to result in very extensive troubleshooting of very mysterious issues that will occur, including crashes of Access.
0
Vadim RappCommented:
...if you want, I can explain the things you asked, about cursor location, lock type, and so on, but the point is, if you are using Access, the more you know and the more you use all these things, the more problems you will create for yourself.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vadim RappCommented:
We see you awarded grade C, the lowest possible. Can you explain? The grading tips are here .
0
BitsqueezerCommented:
Hi Vadim Rapp,

The reason is because this oledb provider is not real ADO, it's modified ADO specially for Access, and it does not support server-located cursors, database transactions, and some other things

Can you prove that with a link? I've never heard that before and I can say from my daily work that the opposite is true: If you use ADO with a form you need to use OLEDB to get an updatable form, moreover, you are forced to use adUseServer, otherwise the recordset will be read-only. adUseServer doesn't mean that the cursor is really on the SQL Server, in case of OLEDB it means that the driver holds the cursor, not Access.

It's also wrong that you get problems or crashes if you change the data with VBA ("All data manipulations should be done by Access") or assign a recordset directly to a form - Access is able to use the recordset in the same way as if you use RecordSource instead - in fact, it does nothing else itself: It opens a recordset based on the RecordSource and works with that. The only thing which Access is not able to automatically do is if you open a recordset using a stored procedure with parameters, Access cannot assign the parameters dynamically if you press F5 in such case (other as if you use RecordSource and InputParameters instead, we are talking about ADP). But that can be catched using AUTOKEYS macro for F5 and reassigning of the SP again in a VBA function.
I use that method in my current project (a big ERM system based on A2010 ADP and SQL Server 2008 R2) and and there is no issue.

@Brant Kersey:
If you use a pessimistic lock that means that you lock a record which is changed by the user it is locked until the user finishes saving the record. That's a lock type usually only seldom used. If you use an optimistic lock no lock is set on the current record, two users could work on the same record, the lock is made in the moment the record will be saved, so the last user "wins". If user one begins changing of the record (the pencil sign in Access) Access saves the current state of the record before it was changed. If user two does the same but is faster writing it, Access will show a write conflict to user one when he saves the record to warn the user one that another user has changed the record since it was downloaded by user one. User one can then decide to overwrite anyway or cancel his own changes. So normally optimistic lock is the way to use (when you need updating records).
if you want to read only then of course any lock is wrong, also optimistic lock. For that case, use adLockReadOnly.
in the procedure to get the current state of the record described above you can make the life easier for SQL Server and Access if you add a timestamp column to each table you use with Access which you want to update. In that case only this column is compared when trying to write the record, otherwise a field-by-field compare must be done. In case of ACCDB/MDB this is additionally a must, because there JET/ACE is in between and comparing each field can cause write errors because of rounding problems. So in general: Simply add a timestamp column to each table, that's in all cases a good idea.
in case of BatchUpdate it is normal that you don't get any write conflict. The reason is that the recordset is not bound online to the backend table, the changes are written locally to the recordset only while editing them. But you'll get a write conflict at the time when you start BatchUpdate as ADO does a synchronization with the backend table(s) now and it does the same checks as described above, only for all records you send at the same time. You get a collection of errors back which you must check so the user can decide which record to overwrite or not. This method is seldom used in Access, mostly because the most people doesn't know it and you need to do more programming. But Access supports that without any problem, it can update the in-memory table like any other table. The big advantage of this method is that you can work offline with the records after downloading them, you don't need an active connection to the backend until you want to run the UpdateBatch method. This recordset also supports to add records with an auto key, it uses a surrogate negative ID for this purpose. Of course this can also create write conflicts if the record the user wants to add has unqiue data defined on the server where another user has inserted a record with the same unique data in the meantime.

Cheers,

Christian
0
Vadim RappCommented:
> Can you prove that with a link?

Not sure what link. Easy: simply look at the connection string in an ADP, you will see that the provider is not SQLOLEDB (sqloledb.dll) but Access own, implemented by MSCDM.DLL (Microsoft Client Data Manager).

> you are forced to use adUseServer, otherwise the recordset will be read-only. adUseServer doesn't mean that the cursor is really on the SQL Server, in case of OLEDB it means that the driver holds the cursor, not Access.

run this:

Dim rs As New Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorLocation = adUseServer
debug.print rs.CursorLocation

you will see not 2 but 3. But if you create and use ADO connection with SQLOLEDB, then it will become 2.

Access connection object also has transaction-oriented methods like begintrans/rollbacktrans, but if you actually try to use them and see what happens (by using Profiler), you will see that nothing does.

"you are forced to use adUseServer, otherwise the recordset will be read-only" - certainly not. Are we talking about the same thing - Access Data Project? what version of Access are you using?

> It's also wrong that you get problems or crashes if you change the data with VBA ("All data manipulations should be done by Access") or assign a recordset directly to a form

Well, it's based on more than 10 years' experience of working with Access ADP's. You may disagree, but I don't think you are in position to say "it's wrong".

> Access is able to use the recordset in the same way as if you use RecordSource instead - in fact, it does nothing else itself: It opens a recordset based on the RecordSource and works with that.

Of course, and when you start doing the same independently from Access using your own separate recordset, or even form's own, you interfere with it. It's better to ask Access to do data manipulation, for example to use not

recordset.update

but

docmd.saverecord
or
Dirty=false

In Access 2002 ADP reliably crashed whenever your code even touched me.recordset, even to read the value. In 2003 it was fixed, but it's still better to leave the objects Access creates to Access.
0
Brant KerseyAuthor Commented:
Jim, if you see the first answer from vadim after my initial question it was sparse, and didnt actually answer my question, which was tell my about this ado locktype, cursorloc, and cursor type and why my initial function, which i included in the initial question was starting to have trouble with write conflicts. I explained my business model and hoped someone would have some advice to why I would use one set of properties over another another. the first answer was over a day late, and I inquired about that. This is not hard...I just wasnt happy with "just let access do the work" as answer. But now, this is great, Christian put his two cents in, got my brain working, then Vadim came back with a better explanation, and , even though I find the AdO properties confusing (mostly because the providers pick whatever the hell they want ) I am beginning to understand the problem due to the added banter between experts. Christian is the man, I still have his With Events publication pinned to my desk, but in this case Vadim was really closer to my problem, its just that he didnt say what or why at first and I didnt get it at that point. It turns out, I was beginning to manipulate all my data behind the scenes with my recordsets , and the open forms never got a chance to SAVE (good advice there Vadim) and everytime we went to a different computer, the underlying table had been changed, but the open forms were lagging. So heck with the ado properties, dont think its the real problem...
I still am not sure if i have to run aduseclient because it does seem my rst may be locked, i started using a fast command with execute ( more update, delete, and insert, and then SAVING the access forms each time after...and things are improving.
0
Vadim RappCommented:
The reason I did not explain all that is exactly in the answer that you accepted: however well you understand server-side locking etc., this is not how Access works.  I offered to explain, if you are interested in the theory, but warned that it's not applicable to Access because its oledb provider does not support it.

Here's good article on the topic:
Understanding Cursors and Locks
0
Vadim RappCommented:
...the biggest problem with server-based cursors, and the chief reason why they were all but abandoned, is the following scenario: the user starts edit, imposes server-based lock on the record, then leaves for the lunch, or for the day, or the application crashes, leaving the lock in place. Result: server restart, because even if you try to kill transaction, it won't work and his transaction hangs forever in "killed/rollback" state.
0
Vadim RappCommented:
Brant, so, were you able to sort all this out and make progress?

>I still am not sure if i have to run aduseclient because it does seem my rst may be locked

to summarize all the above - as long as you are reusing Access connection, thus using Access OLEDB provider rather than "full" SQLOLEDB, aduseclient is your only option.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brant KerseyAuthor Commented:
that is exactly what I did, then I went through all my code and every time I changed table data with VBA i simply added the docmd.saverecord and all the write conflicts disappeared. i let the ADO and the aduseclient alone (and even the locktype and cursortype, because as I tested it with the immediate window using rst.support I noticed the settings changed to suit the situation, not my VBA settings anyway). your one sentence "let access do the work" was the key, but I needed the extra thought process. At first I was frustrated with your answer because I thought the problem was in the recordset settings, so I thought it was an unhelpful response..but ultimately it wasnt at all.
I feel bad for getting on Jims case but when you are under a time pressure to get your program working his joke was as bad as a lead zeppelin. He needed to say in his comment that it was a JOKE, then that would have been fine. I thought he was serious, so I was pulling my hair out as to why I was paying for this service. All good and working now...thanks Vadim.
0
Vadim RappCommented:
Actually, Led Zeppelin was not bad at all :-)

Glad you made it work!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.