Use Recordset as Form Recordsource

[Kevin_S]

I can't save I've ever tried to do this in a DAO recordset so I wouldn't know for sure. I guess the only thing I can suggest would be to try and set it up as a test OR repost the question in the forum and maybe others who have more experience then I do will be able to answer the question...

Sorry I couldn't be more helpful...
Kev

 

[RichMorrison]

Rob wrote
<<
If you do this using an Access backend and a DAO recordset, will it keep it disconnected from the backend?
>>

Specifically........... you need to use **ADO** not DAO to get a disconnected recordset.

I played with this for a while in Acc 2K without much success. In the book "Using Access 2000" by Roger Jennings, there is a small section about using a disconnected ADO recordset in an Access form.

In another application I wound up using a query results table; a local Access table that I filled with the content of an ADO recordset. Each user has a private copy of the results table and the table is cleared out and reloaded when the app needs a fresh batch of data. This approach works pretty well.

RichM

 

[Rob.Mills]

I was thinking about doing something like that. Kind of like your own version of replication. When the user changes a record do you have the form update the same record on the server?

 

[RichMorrison]

Rob,

The "query results table" design works for reports and online queries. I don't use it for database update apps.

I have used local tables for data entry apps. Each user gets a private table for entering new data. When the user click a "Done" button, then the app posts the private data to a public table. This works pretty well too.

I started doing this with old Access 2.0 and have kept using this "strategy" in 97 and 2K apps.

RichM

 

[Kevin_S]

Rich -

A follow up question if you have a second:

Everything working great now but one last hiccup - I have bound the disconnected ADO recordset to the form but I can't edit the data and close the form without getting the following error message:

This Recordset is not updateable

This message is followed by the standard "you cant save at this time" error messages...

Now - I know that you can't edit data from a stored procedure but I figured that, by disconnecting the connection I was, bascially, unhooking or unlocking the data... but this doesn't seem to be the case.

What I want to happen is this:
1) - get data from connection & stored procedure
2) - bind the retured recordset to a form for view/edit
3) - enable user to edit record and the click a 'save edit button'
4) - save edit button reconnects to server and runs an update stored procedure that saves changes to backend that the user requests...

Is there a way to do this and get around the error messages OR is there a way to disable these error messages?

Thanks a bunch Rich,
Kev

 

[RichMorrison]

Kevin,

I have not actually done this. I just have an Acc 2K book with a half-page example of a code freagment.

It says that you:
1) open a Connection
2) Set a New Recordset
3) Fill the Recordset with a Select string
4) Set the Connection = Nothing
5) Modify the Recordset via a form
6) Do an "Update" method call on the Recordset
7) Reopen the Connection
8) Do an "UpdateBatch" method call to send changes to the data source.

I suggest you try MSDN and Search with some keywords. Maybe you will find a more complete example.

HTH,
RichM

 

[Rob.Mills]

Kevin,

Let me know if you find anything on this. I'm gonna look myself.

 

[pono1]

Kevin,

You can't, in this case, have your cake and eat it too.

Your form is not updateable because it has no recordsource (it's unbound). It only has data to display, courtesy of a recordset.

If you want your form to remain unbound it's up to you to do the magic -- by grinding out some code -- that Access normally does for you: Saving a rec back to it's source, feeding recs one at a time into the form, checking concurrency. I would also consider, as Rich suggested, caching recs into a local table (in which case you can still use bound Access forms after incurring the initial traffic burst) or throwing recs into an array (if you have lots of recs with many fields and the idea of bound forms bothers you) and then working with the array to move recs into the controls of your forms. Arrays are very fast compared to recordsets -- see the getrows method in ADO help. Another thing that may interest you: the Save method of ADO; and you'll also want to read about cursor types and cursor locations, if you already haven't. You'll appreciate bound forms all the more if you continue on with this.

'Disconnect a recordset...

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection

'using a regular old Jet backend.    
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=X:\MyBE\BackendDB.Mdb"
            
'Connect to source.
    cn.Open

'Open recordset, bringing all recs across net to client.
    With rst
        .CursorLocation = adUseClient 'A potentially heavy dose of net traffic.
        .Source = "TblData" 'better to use a SQL statement that restricts recs.
        .CursorType = adOpenStatic 'required for client-side cursor.
        .ActiveConnection = cn
        .LockType = adLockOptimistic
        .Open
    End With
   
'Disconnect recordset from its source and close connection.
    Set rst.ActiveConnection = Nothing
    cn.Close
        
'Test to see if data is still around on client...
    Debug.Print rst.Fields(0)
    Debug.Print rst.Fields(1)
    
    'Someplace, somewhere, you will eventually want to
    'remove the reference to your rst by setting it to nothing...

           Set rst = nothing

    '... but not before you have done what you want with the data,
    'changing recs and then reconnecting to update your data source
    'one rec at a time or in a batch, etc.

'Will this now raise an errror?
    Debug.Print rst.Fields(0)
    Debug.Print rst.Fields(1)

Please keep us posted with your progress...

Regards,
Tim

 

[pono1]

Then again, let Access 2002 users eat cake...

Regards,
Tim

 

[Kevin_S]

Hey pono1 & other friends...

Just a follow up:

I have been able to open a form and populate it with a recordsource generated from a disconnected ADO recordset (as mentioned earlier) but I haven't been able to edit the recordset (even to just change the data on the form without saving) without getting hit by three error messages:

1) - This recordset is not updateable
2) - You cant save this record at this time
3) - MS Access has encountered errors and is forced to close

I haven't been able to find a workaround for this or find out how to trap the error messages until I came across this article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;288375

seems this is an error with Access (shocking I know) that MS is aware of and the only way to resolve this is to either download MDAC 2.6 on EVERY pc or use a different connection property (but this is only useful if your using an .adp file...)

I am going to try and find another solution so if anyone has any ideas let me know... I've gone too far to turn back now....

Kev

 

[RichMorrison]

<<
I am going to try and find another solution so if anyone has any ideas let me know... I've gone too far to turn back now....
>>

Kevin,

What do you want to *do* with the data that comes back from the SQL server back end ?

If you just want to display it I recommend that you append the ADO recordset to a local Access table. Then you can use the data in the local table in forms or reports. That is what I do with applications using Oracle as the back end and Access 2K as the front end.

If you want to update the data and send the results back to SQL server then I don't have any experience that could help.

Good luck,
RichM

 

[Rob.Mills]

There's a couple things you can do

1. You can setup your form on it's BeforeUpdate to open the Oracle table and update the same record before it updates the local table

- or -

2. Setup a field in the local table as a Yes/No. Then on the BeforeUpdate have it mark this field as Yes. Then on the Unload event of the form have it update the Oracle table in one event.

Unfortunately, this setup does leave itself open to corruption more than others. But for the most part I believe it would work fine.

 

[Kevin_S]

Hi Rich -

Could you possibly show me an example of how you append an ADO recordset to a table and then send back and update the changes...?

I have to leave for the day but I'll check in later tonight and throughout the weekend (yes, I know, my wife says the same thing but what can I say... I love to program )

Thanks a million for the continued help,
Kev

 

[Pat Hartman]

Jet passes queries to server whenever possible

I want to stay away from queries on linked tables and concentrate on keeping as much as the "muscle" work on the server as possible

- I hate to burst your bubble. I see that you have expended a lot of effort to get this to work but it really isn't necessary at all. Jet makes every effort to send your queries to the server for processing. That means that linked tables and querydefs stored in your Access database would be just as efficient provided of course that you followed some general design guidelines to optomize your app for client/server. The most important thing you need to do is to bind your forms and reports to queries with where clauses instead of directly to tables. This allows you to minimize the number of columns and rows retrieved from the server. You also need to be careful with heterogeneous joins and VBA and user-defined functions.

I've attached an exerpt from the Jet Programmer's Guide with more info.

 

[RichMorrison]

Kevin
<<
Could you possibly show me an example of how you append an ADO recordset to a table and then send back and update the changes...?
>>

No I can't. As previously posted, I have not made any apps that append server data to local tables *AND* post updated results back to a database server.

RichM

 

[Kevin_S]

No I can't. As previously posted, I have not made any apps that append server data to local tables *AND* post updated results back to a database server.

I've actually started to go a different direction on this Rich using an unbound form and parsing the ADO recordset and inserting the data into the appropriate fields on the form (this method does result in a lot of cumbersome coding however... I'll keep you posting on my findings)

I hate to burst your bubble....

LOL! Not a problem Pat but your response to this does confuse me though as I have always been instructed and have picked up in text and forum discussions that, in order to speed up client/server applications (especially over a slow network) that most of the processing should be kept to the server as much as possible?!?!? And, furthermore, that the most effecient way to run a query is to pass a parameter to a stored procedure and then return results from this to the Access client...?

The white papers/books I have read on this (SQL Server Magazine & Professional SQL Server 2000 Programming by Wrox) state that the Stored Procedure is faster as it has the compiled the SQL on the server side and only receives a parameter while dynamic SQL or a saved query (I haven't heard the term querfdef before so I'm assuming this is simply a query that is saved in the database (please correct if I'm mistaken) has to pass to the server the SQL to be executed - which is then compiled on the server - and thus takes longer to process then a stored procedure... Is this not the case....? Don't linked tables in the client also slow down data transfer and application loading as well?

I always base forms/reports on queries with drilled down Where clauses to return only one record but I was going to try and use ADO recordsets generated from sotred procedures as I was under the assumption that this was going to be the fastest possible method to get the data but your recent post now has me second guessing...

...Please advise...

Thanks,
Kev

 

[RichMorrison]

Kevin
<<
I've actually started to go a different direction on this Rich using an unbound form and parsing the ADO recordset and inserting the data into the appropriate fields on the form (this method does result in a lot of cumbersome coding however... I'll keep you posting on my findings)
>>

Yes, that's a standard paradigm(love that word) for ADO transaction processing; use an unbound form, get the data, copy the data to fields in a form, update the data in the form, send the data back to the database server.

That's how they do it in VB.

If you want to get the benefit of an Access "bound" form, then Pat's advice is what you want. Pass a query to the database server. The query should return one row. Bind the form the the query.

HTH,
RichM

 

[Pat Hartman]

There are levels of efficiency and certainly stored procedures that take parameters are at the top of the heap. However, the other overhead associated with using them plus the complexity of the VBA code involved reduces that efficiency significantly.

The next level down is forms bound to stored querydefs that take parameters to reduce the number of rows returned. Jet processes these queries first and that takes some overhead but by using stored querydefs rather than SQL strings, you minimize the overhead involved. When Jet stores the querydef, it saves a lot of information that it can then use when you run the query. Jet sends as much of the query to the server for processing as possible. So, it is your job to make sure that the important part of the processing happens on the server. To do this, you need to be careful about the functions that you use. Avoid using any functions in the where clause. You don't want to force Jet to request all table rows because it needs to evaluate your function locally. In some cases you can isolate your functions by using nested queries. The inner query will not contain any functions. That's the one that will be sent to the server to obtain the data. The outer query may then use functions because they will be applied to the returned recordset locally.

Since you have gone this far. Try to create a bound form and compare the load times of each form. Link the tables that you need. Create a query similar to your parameter stored procedure. Bind a form to the query. Just let the Access wizard build the form. Don't worry about it being pretty. I think that you wll not find a significant difference in the time it takes each form to load or possibly your unbound form will be slower. I know you can't test updating spead because you currently can't update your unbound form but this little test will tell you whether you are expending a significant amount of energy to no purpose.

 

[Kevin_S]

To do this, you need to be careful about the functions that you use. Avoid using any functions in the where clause. You don't want to force Jet to request all table rows because it needs to evaluate your function locally.

I don't use any functions in the WHERE clause - only criteria like "Forms!someformName!someField" so this shouldn't require JET to return the entire recordset and then find the field only requiring the return of the single record that matches the PK field in the table. But (continuing on the logic line you opened) If I have one query with a WHERE clause like the one I just illustrated (Forms!frmName!fieldName) to return a recordset from the server and then I use this query in another query where I preform a count function then the inner query will be the only thing sent to the server correct..?

For the test: I just performed a test of the form bound to a disconnected ADO recordset and one with the same data bound to a query with a WHERE clause that will return the exact same information (was kind of setting up the test before your post) and the speed difference for me is minimal (nanoseconds) but I'm working on a T1 network connection and I'm roughly 50 feet from the server room whereas the users are hundreds of miles away on phone modem connections that connect at roughly 19-27 kps !!! so the minimal difference I see could be huge for them...

I'm still lost in this querydef verbage - is this just a saved query (visible in the query window) or is this something all by itself?

Thanks,
Kev

 

[Pat Hartman]

and then I use this query in another query where I preform a count function

Jet attempts to send everything to the server. If it can turn your nested query into a single query, it will send the whole thing to the server. You can have an impact on how queries are evaluated by Jet by separating them and nesting them. This adds little or no overhead and can sometimes save a lot of processing time when the query is complex.

Stored querydefs are the items you see listed in the query tab of the database container. NO other SQL is pre-processed. So any SQL strings in the recordsource of a form or report or the rowsource of a combo or listbox or written in VBA are NOT pre-processed. They are "compiled" every time they are executed. So, if you open a form with an SQL string recordsource 20 times in a single session, the query is "compiled" 20 times whereas if the recordsource points to a querydef, no compliation is required at all until you modify the querydef and save it again. In addition to taking some (albeit small) amount of time, the compilation process takes work space which is not recovered until the db is compacted and therefore contributes to db bloat. It also requires extra .dll's to be loaded. These .dll's are optionally loaded only when necessary so there is overhead associated with this also.

Part of the processing done when a query is "compiled" involves determining the best method to use to obtain the requested data. A key piece of required information is the row count of the table. Therefore, everytime you compact the db, Access "recompiles" all the saved querydefs (but not the SQL strings) so that it can take into account the current db stats.