Solved

Is Snapshot better than Open Dynaset

Posted on 2008-10-01
4
746 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
  • 2
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now