Is Snapshot better than Open Dynaset

I have an  Access front end and a SQL backend.  I have a lot a mix and match code trying to transition from VBA to SQL.  Is one of these statements better/faster/more efficient than the other if my backend is SQL?

Set rs2 = CurrentDb.OpenRecordset("tblMTTestRooms", DB_OPEN_DYNASET)
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM tblMTTestRooms", DB_OPEN_SNAPSHOT)
BobRosasAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
I would suggest you use Dynaset to be SURE you always getting the latest data.  Snapshots can be misleading.  And of course, you cannot modify the data.

mx
0
 
BobRosasAuthor Commented:
Thanks for your quick response.  What if, based on the users input, I recreate the table before each use?

The reason I'm asking is because of an EE suggestion, I'm trying to rewrite my code and make it more compatible with SQL.  The commet from EE was....
       Every time you query SQL Server, all you are doing is retrieving all the records to the client and if    
       on top of that you select DB_OPEN_DYNASET you have in effect locked all those rows.  

For the entire question you can check out the following link but I'm guessing I'm still not on the right track.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_23740550.html
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I can't really speak for SQL Server, sorry ... I missed that.  In Access/JET, rows are not locked when retrieving data using Dynaset.  Can't see why they would be in SQL Server either, but ... I really don't know.

mx
0
 
BobRosasAuthor Commented:
I appreciate your input.
Thanks
0
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.

All Courses

From novice to tech pro — start learning today.