Fabricated ADO recordset - adding records

[spikepl]

Lagbolt has kindly provided an example of a fabricated ADO recordset bound to an ADO recordset here: http://www.access-programmers.co.uk/forums/showthread.php?t=258210 and it is reattached here.

I have been unable to discover how to add records using the form. So, what needs to be changed, so that one could add records just like with a normal form bound via DAO to a table?

 

[mdlueck]

I have been unable to discover how to add records using the form.

I stepped through the code execution... pretty slick what you have accomplished thus far. I thought surly one had to use DAO objects when bound to forms.

So what about creating the adoRS object at Form level. When the form opens, do the:

Set Me.Recordset = rs

LOC.

Then in an AddRecord button, preform an .AddNew, and read values from the form text field controls and plug them into the adoRS fields instead of your hard coded values.

Perhaps give that a try.

 

[spikepl]

Doing it "by hand" would certainly be possible, but I cannot imagine that it should be necessary. Surely the form mechanism should be able to deal with this? After all, deleting a record in the form works just fine...

I must be having a bad-Google day, because I still have not found anything suitable

 

[mdlueck]

Doing it "by hand" would certainly be possible, but I cannot imagine that it should be necessary.

In my mind, Forms and Reports were hard coded to only work with DAO objects. It is nice that some functionality is still in Access from when MS was on that "Pro ADO" kick a few years ago.

You have me intrigued to attempt to execute a query against a SQL BE DB (ADO.Command object returning ADO.Recordset) and have that resulting object be the data source for a Multiple Items form. If that would work, I could do away with much FE DB bloat substituting such for FE temp tables to bind Multiple Items (read only) Forms to. "Soon" I hope to get that tested.

 

[Galaxiom]

You have me intrigued to attempt to execute a query against a SQL BE DB (ADO.Command object returning ADO.Recordset) and have that resulting object be the data source for a Multiple Items form.

That is how I do subforms against SQL Server. The subform recordset is loaded dynamically by sending a parameter to a stored procedure which returns the recordset. It is very much faster than linked tables.

However this really only requires a conventional ADO recordset. Disconnecting the recordset is good for the server because the connection can be released.

I use fabricated recordsets to display transient data such as the results of data processing steps. I also use them to hold data parsed from text files for display in a form.

Sometimes instead of a form I display data in Listboxes which also work with an ADO recordset. Combos too.

I have never tried adding a record via a bound form. I add the new records directly to the recordset and reload it as the form's recordset.

One of the most useful purposes for a fabricated recordset is adding a boolean field for selecting records with a checkbox in a form. This avoids having to add a field to the original table and the associated problems with multiple users.

If that would work, I could do away with much FE DB bloat substituting such for FE temp tables to bind Multiple Items (read only) Forms to.

I have long been a advocate of recordsets instead of temporary tables.

One of the interesting features of a disconnected recordset is the ability to update its records then reconnect and synchronise it with the original data source. Unfortunately changes made via a bound form cannot be synchronised like this.

Also be aware of the ability to save a recordset as an XML file and reload it.

Another interesting feature is the ability to hold another recordset as a value in a field.

 

[mdlueck]

I have never tried adding a record via a bound form. I add the new records directly to the recordset and reload it as the form's recordset.

How would you populate a Multiple Items form without having it bound, at least to the ADO.Recordset object?

I do not care for editable Multiple Items forms. No free wheeling "spreadsheet database" applications! NO! NO!

So Galaxiom, you have had success with having an ADO.Recordset object be what feeds a Multiple Items style form?

 

[Galaxiom]

What do you mean by the term Multiple Items form?

 

[spikepl]

So much ADO about nothing!

I am putting this thing aside, because of too many stumbling blocks. A form bound to a fabricated ADO recordset does not work like when bound to a dao table. Some events do not fire. The discovery process to identify what works and what doesn't is very time consuming. Others must have stepped on all such mines but I've simply ran out of search terms and patience to locate that information on the web.

So back to temp tables...

If YOU happen to have a good link or two, or a working example doing CRUD operations on records of a fabricated ADO recordset bound to a form then please post them here. I am sure others might benefit.

 

[Galaxiom]

The biggest issue is the addition of new records.

Firstly this requires AddNew on the recordset. This is triggered by the BeforeInsert event of the form. Then the recordset must be reloaded to make the new record available to the form. Note that Requery is not the go. The command looks odd but it does what is required.

However the reload causes the focus to disappear into the ether probably because the state of the form is not what is expected in DAO. The SetFocus Method will not return it so an API call to the Windows SetFocus function is required.

No Update is required because this is done by the form when moving to another record.

If required, adding an unique key value is different with no source table let alone an autonumber field. I used an RMax + 1. RMax is a function I wrote as an equivalent for DMax but it uses a recordset instead of a query or table.

Note that the value is written to the bound control. If the recordset is written to, any of the empty fields will immediately show an error because fabricated recordsets don't support nullable fields. (AFAIK That can be done in an ADOX catalog.)

Quick and dirty sample built on Lagbolt's sample attached.

 

[mdlueck]

What do you mean by the term Multiple Items form?

Create \ More Forms button \ Multiple Items

Simple Add/Edit record forms are simple to create as unbound forms and I use ADO.Command objects in the DB class when working with such forms. They only deal with one record, thus are simple.

Until now, I have used temp tables and have bound Multiple Items forms in read-only mode to FE temp tables. If I could do about the same thing with ADO.Recordset objects, that would be slick-O.

 

[mdlueck]

All right, this looks like it has possibilities... I was able to get an adoRS object to populate a Multiple Items Form.

I cannot get the form to open at the last height I had it at while in form edit mode.

In general, I see potential for this concept.

I had to submit the query directly with an adoRS object as the one returned by an adoCMD object had the incorrect CursorLocation so the Form would not accept it as the Me.Recordset.

So this raises the question, could I take the adoRS object which is returned by a call to adoCMD Execute, and transfer all of the records to a brand new adoRS object which I have custom built from scratch so that I can specify the CursorLocation property? One line of code to transfer the records? I will not accept a solution which I must loop through the records to perform the transfer in interpretive VBA code.

I much prefer to submit SQL via adoCMD objects so an ADO form solution which could work with adoCMD objects submitting the actual query to the BE DB would be preferred.

 

[Galaxiom]

Create \ More Forms button \ Multiple Items

Ah! What most of us call Continuous Forms.

I don't use the form Wizards much.

 

[mdlueck]

Ah! What most of us call Continuous Forms.

Perhaps the name has changed over the years to what I see in A2007?

I don't use the form Wizards much.

I know of no way to obtain a Multiple Items / Continuous form otherwise. Specifically the Detail section I know of no manual way to obtain on a blank form.

 

[Galaxiom]

I know of no way to obtain a Multiple Items / Continuous form otherwise. Specifically the Detail section I know of no manual way to obtain on a blank form.

On the property sheet of the form under the Format tab. Change the Default View to Continuous Forms.

 

[mdlueck]

Thanks Galaxiom.

Might you also have in your bag of tricks how to obtain an exact copy of an ADO.Recordset object WITHOUT stooping to interpretive brute-force code? I see that point as one challenge with efficiently utilizing Fabricated ADO.Recordset objects and binding them to Forms.

Scenerio: Execute an ADO.Command object, receive back from it an ADO.Recordset object. Copy that ADO.Recordset in order to set it to a client side cursor rather than server side. Is this type of thing possible?

 

[Galaxiom]

Might you also have in your bag of tricks how to obtain an exact copy of an ADO.Recordset object WITHOUT stooping to interpretive brute-force code?

Pretty much everything with recordsets could probably be described as brute force compared to a query or a bound form. However by their nature of being able to exist solely in RAM, working with disconnected ADO recordsets is very fast.

But like any code heavy techniques that code can be encapsulated into a class making it simple to interact with.

Scenerio: Execute an ADO.Command object, receive back from it an ADO.Recordset object. Copy that ADO.Recordset in order to set it to a client side cursor rather than server side.

A client side cursor should not require a recordset to be copied. The Coursor Location property can be set on the original recordset. That recordset can then be disconnected from the source table/query by setting the ActiveConnection property to Nothing.

Then set the form's recordset property to it. At this point you effectively have the same functionality as a temp table binding except that it cannot be managed with queries. Note the way records need to be added as shown in my sample earlier in this thread.

The absence of queries is no big deal. Loops work astonishingly quickly. When I first started working with disconnected recordsets I thought my code had failed because it completed execution in such a short time.

The next step is updating the original data source with the new and changed records in the disconnected recordset. Unfortunately the use of the bound form defeats the Batch Update facility provided in ADO. Otherwise we could just reconnect and it would all be managed.

Once again we are forced to use loops. However the process involved in resynchronising temp tables is at least as complex if update conflicts are properly managed.

 

[mdlueck]

A client side cursor should not require a recordset to be copied. The Coursor Location property can be set on the original recordset. That recordset can then be disconnected from the source table/query by setting the ActiveConnection property to Nothing.

All right, one error at a time, I have arrived here with my prototype code:

Private Sub Form_Open(Cancel As Integer)

  Dim adoCMD As Object
  Dim strSQL As String

  strSQL = "SELECT [t].[pid],[t].[quoteid],[t].[metflg],[t].[metid],[t].[mettitle],[t].[toolstatusid],[t].[tooltypetitle],[t].[partnumber],[t].[parttitle],[t].[partvendortitle],[t].[toolstatustitle],[t].[lttotal],[t].[toolduedate],[t].[besttoolcost],[t].[prodpartflg]" & vbCrLf & _
           "FROM [tblRptPartsToolCost] AS [t]" & vbCrLf & _
           "ORDER BY [t].[partnumber]"

  Set adoCMD = CreateObject("ADODB.Command")
  With adoCMD
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdText
    .CommandText = strSQL
    Set adoRS = .Execute()
  End With

  With adoRS
    .Close
    [B][COLOR=Red].ActiveConnection = Nothing[/COLOR][/B]
    .CursorLocation = adUseClient
  End With

 Set adoCMD = Nothing

End Sub

Now I receive error: "Run-time error '3707' Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."

Just before I had not put the .Close and received a different complaint... seems very hard to get an ADO.Recordset free from entanglements of the ADO.Command object which created it.

Oh, I actually got around even that error message just now, with the following code:

  With adoRS
    .Close
    [B][COLOR=Blue].Source = vbNullString[/COLOR][/B]
    .ActiveConnection = Nothing
    .CursorLocation = adUseClient
  End With

However, I have completely emptied out the Fields collection, thus destroying the ADO.Recordset's usefulness.

Such was not what I was expecting based on your praise of this type of scenario.

Oh... how about grabbing the adoRS.Fields object and stuffing that into a second adoRS object... hhhmmm... and on and on the tests go. Galaxiom, please contribute some example code of this working as you intended.

Nope: BANG! "Run-time error '450' Wrong number of arguements or invalid property assignment

  Set adoRS = CreateObject("ADODB.Recordset")
  With adoRS
    .CursorLocation = adUseClient
   [B][COLOR=Red] .Fields = adoRStt.Fields[/COLOR][/B]
  End With

Nope again: BANG! "Run-time error '438' Object doesn't support this property or method."

  Set adoRS = CreateObject("ADODB.Recordset")
  With adoRS
    .CursorLocation = adUseClient
    [B][COLOR=Red]Set .Fields = adoRStt.Fields[/COLOR][/B]
  End With

 

[Galaxiom]

After playing around a bit it seems I have been quite lucky in what I have previously tried with ADO recordsets in forms.

They do work really well for displaying data but I had interacted directly with the recordset to change what was subsequently displayed in the bound form.

Adding records directly via the bound form is a worthy ambition. Too bad about the ADO command limitation though. That would have been really nice.

They seem bewilderingly unpredictable. Exactly how the recordset is produced affects the behaviour in the bound form. A simple disconnected recordset works quite well once the focus issue is addressed on the new record as I showed in my sample.

However when I built a fabricated recordset using the disconnected recordset as a model then copied the data, the form behaved quite differently.

It would open with #Error in every control. However if I then simply wrote to any bound control, the whole record would spring to life. I added this write to the Current Event and it all works fine. Once the record has been written to it displays flawlessly without any further monkey business.

However a weakness of the fabricated recordset is its inability to hold Nulls. All fields are updated to their respective initial values (zeros for numbers etc) when the record is updated.

Consequently I would be more inclined to pursue bound disconnected recordsets as candidates for ADO substitutes to temp table record source functionality in forms.

 

[mdlueck]

Too bad about the ADO command limitation though. That would have been really nice.

Oh... so does that means you do not use ADO.Command objects to execute Stored Procedures, resulting in an ADO.Recordset, which you then bind to a Multiple Items / Continuous style form?

 

[Galaxiom]

Oh... so does that means you do not use ADO.Command objects to execute Stored Procedures, resulting in an ADO.Recordset, which you then bind to a Multiple Items / Continuous style form?

I have that but I had not tried updateable functionality. Most of what I do involves reading data from various read-only sources so I had never tried to do it.