Full Version: Difference Between Me.recordsource And Me.recordset
UtterAccess Forums > Microsoft® Access > Access Forms
I am starting to delve into ADODB after converting my back end to SQL Server. Before, I bound some of my forms using the Me.Recordsource property. Now, I want to use ADODB and my research has turned up the use of Me.Recordset at the following URL: http://support.microsoft.com/kb/281998. Is Me.Recordset the same as Me.Recordsource? What's the difference between the two?
Sorta. Recordsource accepts only a string which can be a name of table, query or a SQL statement. Recordset property returns an Recordset object and you can also set a new Recordset object to the Recordset property which also changes how form is bound and Recordsource will be also changed as well (but I've not looked at what it reports when you bind a ADO Recordset).
ote that Form's Recordset property can accept either DAO Recordset or ADO Recordset; whenever you use mdb/accdb file format, you're using DAO by default and the only way to use ADO is to well, bind a ADO recordset to the form. With a adp file,everything is ADO by default.
You can also use Form's Recordset to do same programmatic manipulation of form's recordset, which I typically use in lieu of running an action query (which can conflict since it is a separate lock). However, be aware that when you manipulate Recordset instead of RecordsetClone, the UI will change based on your maninpulation; if you do a MoveNext on a Recordset, the form will move ahead one record whereas when you do same thing with RecordsetClone, the form stays put at the same record.
Hope that helps.
First off ... there is no need to switch to ADODB in order to use SQL Server as your back end. I would actually discourage it. Use ADODB where appropriate, but a the wholesale approach of ADODB only since you are using SQL Server is an ineffective approach with an Access front end.
I am assuming that you will use Linked Table objects as your primary means of accessing the data correct? With that being the case, you will save yourself a lot of trouble by sticking with DAO when utilizing those objects. Basically, if you access data through an Access object (Table Object or Query Object) then DAO is your best friend. ADO is a good choice when direct access to the server is needed, but do remember that if you are using CurrentProject.Connection, then you are not directly accessing the SQL server. But even if you need direct access to the server -- Passthrough Query Objects (which would be an Access object, thus DAO should be the mode with them) are an excellent choice.
Also, it is important to note that Access objects that bind to data (Forms, Reports, Combos, ListBoxs) all use DAO by default (edit: with the .mdb/.accdb formats).
Now, the difference between .Recordsource and .Recordset is this. Recordsource is a string property that tells the Form object how to query for the bound data, when you set it, the Form object will query for the data using the string that the property is set to. The setting the the .Recordsource property, will in turn set the .Recordset property. If you set the .Recordsource property, the Form's .Recordset property will return a DAO recordset object. Recordset is an object property, when you set it, you have done the querying FOR the Form and just tell the form that this is the data I want to bind you to.
In Summary:
Recordsource: Tells the Form object how to query for the data (note that DAO will be used, unless you are using and ADP as your FE).
Recordset: You have done the querying already, and give all the data to the Form.
Note that setting of one, will effect the other. Also note that setting the .Recordset to an ADO recordset requires additional preparations in order to make that bound data editable.
>> but I've not looked at what it reports when you bind a ADO Recordset <<
When setting the .Recordset object, a result will be that the Recordsource property will reflect the SQL statement that was used to query for the data. For example, with a Form_Load event of something like this:
Private Sub Form_Load()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set cnn As New ADODB.Connection
    Set rst As New ADODB.Recordset
    cnn.Open "Provider=SQLOLEDB;" & _
             "Data Source=someServer;" & _
             "Initial Catalog=someDatabase;" & _
             "Network Library=DBMSSOCN;" & _
    rst.CursorLocation = adUseClient
    rst.Open "SELECT * FROM someOwner.someTable", cnn, adOpenKeyset, adLockPessimistic                        
    Set Me.Recordset = rst
End Sub

The .Recordsource property will subsequently be set to "SELECT * FROM someOwner.someTable"
BUT ... do take note that if you try to Refresh your bound data through the Ribbon (or Menu's) you'll get an error (If I recall correctly, something related the provider not being initized). That SQL statement has a strong likely hood to be invalid against the Jet/ACE engine due to the slight differences in SQL syntax, so usage of the value for subsequent operations should be done with the awareness that it was valid for the connection use used to create the bound .Recordset.
... OOOHHHH! .....
One more important note, DAO is the default object library for Microsoft Access (with the exception of A2000 and possibly A2002 -- but that was a fiasco the MS corrected quickly < ). ADO is not referenced by default in A2007 and A2010. Microsofts general recommendation for manipulating SQL Server data with Access is through Linked Table Objects and the MDB/ACCDB format.
So ... in the end use the appropriate library for the task at hand. I often use a mix of DAO and ADO with about a 90/10 mix respectively for code based data operations.
Plus ... be sure to check out:
And ...
I agree with Brent that if you can use DAO, use it. ADO is somewhat more persnickety - FWIW, I've documented some of its quirks. Generally, I end up using DAO 95% of the time. The 5% time when I do use ADO are when the following conditions are true:
) I want to bind a form to an updatable stored procedure/table-valued function
2) I want to bind a form to a complex recordset that would be nonupdatable but can be made updatable by specifying UniqueTable property
3) I want to bind a form to a disconnected or ad-hoc recordset (without requiring a separate temp file).
Note that #1 and #2 only makes sense when we're using ODBC sources (even with 2010's new table macros, it's not really the same story). #3 conceivably can be use with Jet sources but again, ODBC sources tend to make more sense for a disconnected recordset, and besides, some time using a temp file is actually easier nonetheless.
Finally, note that you cannot bind a ADO recordset to a report (at least in mdb/accdb?); but that's moot as Brent already explained that you can just use passthrough query anyway. Also, there's the alternative of using DAO with a View where there is a INSTEAD OF trigger defined to allow you to update a complex recordset without being constrained by assumptions made by the act of binding.
I really appreciate the input. Some more background giving my motivation might be helpful. I have created a rather large and complicated front end to a relatively small back end that was developed for an oil & gas offshore production platform construction project. I did this as a subcontractor to a major O&G company, and I was fortunate enough to be able to retain the marketing and development rights to the database.
The back end was originally a Jet database utilizing DAO exclusively in the front end. But then, the platform construction was completed and my involvement in the project was over. I started looking for another Access gig, but found that almost everybody is using SQL Server as a back end. Additionally, telephone interviews with prospective employers have indicated that T-SQL with an Access front end is common with larger companies such as ADM, Walmart, and Bloomberg that use databases with millions of records. Also, my own search engine research has found that with large SQL Server databases, ADODB is preferable over DAO.
So, as a learning project, I have created a linked SQL Server back end for my database, and I am starting to replace DAO with ADODB where ever I can with a goal of eliminating all links. Phase 2 will include stored procedures.
Olook forward to seeing your additional comments.
T-SQL is the language used for SQL Server. If someone is saying that they are using T-SQL with an Access front end, my eyebrow would raise up a bit. What they are doing is using a SQL Server back end and Access as a Front End. SQL Server stored procedures, views, and such are written in T-SQL syntax (T-SQL syntax compliant statements/commands tell the SQL Server database engine what to do) and Access can invoke a stored procedure, or open other SQL Server object, but Access itself does not use the T-SQL syntax, with the exception of a Passthrough Query Object or an Access ADP file that directly connects to the SQL Server database. Also, VBA will only utilize T-SQL when utilizing a direct connection to the SQL Server database or modifying the SQL property of a Passthrough Query Object.
My the way millions of records is inconsequential really.
If you continue to exclusive use your Linked Table Objects and Query Objects to access your data, your back end is virtually indendant. In other words, you can use any back end database (as long as a linked table can be created that points to it) -- SQL Server, Oracle, etc with very little changes -- or possiblt NO CHANGES AT ALL if you code with the idea your FE will connect to various databases.
That statement needs to be qualified, as Banana and I have tried to do, if you manipulate your data through an Access object, like a Linked Table or Query Object, use DAO. The ACE (Access Connectivity Engine) or Jet (Joint Engine Technology) database engine was specifically written to connect to several datasources and DAO is the prefered choice to tell the Jet/ACE engine what to do.
ADO is indeed preferred when you are directly connecting to a SQL Server database, but that need to directly connect to a SQL Server database with an Access application is often not necessary. For example, I create C# applications -- I use ADO.Net, DAO is not even a consideration because the Jet/ACE engine is not being used to send commands to the SQL Server database, but if I were to create the same application with the Access developement enviroment, DAO and Linked Tables would be the data access strategy of choice because the Jet/ACE engine is quite effective with connecting to and communicating with SQL Server and DAO is quite effective (and efficient) at telling the Jet/ACE database engine what to say to SQL Server. In addition, directly connecting to the SQL Server database (or any server based system), often does not yeild as huge of a performance benifit as one might think.
Again I want to re-interate that your data access library choice and data manipulation strategy needs to be directed by the environment and usage of the data you are manipulating. Just as in Banana's list of three scenarios in which ADO is used, in all those circumstances the requirement is weighed against the capabilities (and usability) of the available data access libraries, and ADO makes the most sense for Banana (and many experienced developers for that matter) in those situations.
Please remember the scope is which you are working and the inherent capabilities that Access, its object model, and the Jet/ACE engine affords you -- the system if very capable, flexible, and efficient. Also, remember that talking on the phone with folks regarding there IT set up can yeild interesting results if the people on the other end are just throwing out keywords that sound cool and their depth of understanding on the topic is from an HR/PR point of view.
Good excercise for a learning experience, but in my opinion, exclusively using ADO and no linked tables within an Access environment kinda short circuits a large part of what Access has to offer you. If you like Access Forms and Reports, but don't want to use Table Objects and Query Objects, then I would suggest you use and ADP, however that advise comes with a caution --- Microsoft has not invested any developement money or time to further the ADP technology with its last two releases, and the available ADP technology is not keeping up with SQL Server development that has introduced new features. The larger that gap becomes the less like the ADP technology will be supported in the future. {note: that is all my own opinion --- even though I really like the concept of ADP's!!!}
In short, I go back to this general statement:
Use DAO if you are manipulating your data through, or using your data in, an Access object (assumption of .MDB/.ACCDB or its derivatives). Use ADO if you have the need to directly connect to a server based database engine (SQL Server, Oracle, etc.) and utilize its objects.
Also, I feel its supremely important that an Access developer knows how to use ADO and DAO as well as the exclusive abilities of each in order to create a solid application.
Thank you VERY much for this very informative discourse. What I think I should take away from this is that I should continue with my learning exercise but without being so compulsive about eliminating all links to the back end. I will include use of some stored procedures and views as part of the learning exercise.
an, how I love UA!
To give you a bit of back ground on one of my Access FE / SQL Server BE scenarios ...
ccess Front End:
- FE distributed to users
- Concurrently connected user base was approximately 70. Some installatations of the FE were international -- ie, the Front End was in Japan and the SQL Server database was in the USA (mid-west region).
- ADO and DAO were used. Bound forms had Recordsource properties set to only allow 1 record (or a small amount of records). All Form Recordsources were filtered on indexed fields. Rarely, if ever, was there a query against the database that did not include at least one indexed field.
- Reporting of large date spans (ie: encompassing huge chunks of data) was met with an occasional groan of slowness, but the users who groaned had no concept as to what was happening when such a request was trying to be processed.
VBA Code:
- more than 40,000 lines of code.
SQL Server db specs:
- ~180 tables
- a lot of Views and Stored Procedures and Triggers
- Several data tables with well over a million records. If I recall, one was at the 3 million mark when I left the organization.
Methods of data input:
- Automatic. Manufacturing equipment (thousands of monitored datapoints) wrote production information to the database, rarely was the database idle during production.
- Manual. Through bound forms that retriev only the data they needed.
Primary data access methods:
- Forms: SQL statements sourced (ie: the FROM clause) through linked tables. The SQL statements were written, via VBA, to the .Recordsource property.
- Reports: Primarily passthrough Query Objects that had the .SQL property of the DAO.QueryDef object manipulated prior to opening the Report object.
- VBA and Recordset objects: DAO 90-95% of the time; ADO (with a Connection object that connects directly to the server) otherwise
I have had many years under my belt with similar frameworks as stated above.
Yes ... most definately ... also note that a View can be the source of a Linked Table object.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.