Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

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)
0
BobRosas
Asked:
BobRosas
  • 2
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now