[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Is Snapshot better than Open Dynaset

Posted on 2008-10-01
4
Medium Priority
?
818 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

656 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