?
Solved

Is Snapshot better than Open Dynaset

Posted on 2008-10-01
4
Medium Priority
?
804 Views
Last Modified: 2010-04-21
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
Comment
Question by:BobRosas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 200 total points
ID: 22617685
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
 

Author Comment

by:BobRosas
ID: 22618056
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
 
LVL 75
ID: 22618926
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
 

Author Closing Comment

by:BobRosas
ID: 31502111
I appreciate your input.
Thanks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.
Suggested Courses

771 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