Create empty recordsets to use with adding forms

[BSman]

Is it possible to create empty recordsets that have empty rows that could be used for data entry from a form? What I'd like to do is create an empty recordset (based on a table layout) that would not be tied to any table. If necessary, I could see that I might have to specify the maximum number of empty rows (basically identifying the size of the temporary array). Then I would want to use that recordset as the data source for a form, add as many rows of actual data as necessary, and then write the data to the actual table.

This would be especially useful with a form that included a contiuous subform where you would define a separate recordset for the subform and another for the form.

So far I haven't found anything that indicates that it is possible to do something like this. Any help would be greatly appreciated.

Thanks,
Bob

 

[fneily]

What?
"Then I would want to use that recordset as the data source for a form, add as many rows of actual data as necessary, and then write the data to the actual table."

It sounds like, you want to collect data, check to see if it's complete data, and if it is append it to a "production" table. Why not just create a duplicate table, STRUCTURE only, of your production table and use this to collect the data. Then you can run your validations, or wait for data that's not ready yet, etc. Then when records are ready, run an append query to the production table. Actually, this is good process design.

Your second paragraph - what?
BSman

 

[JoeAtWork]

An ADO disconnected recordset should be able to do that.

Basically, you open the recordset, detach it from the database by setting the Connection property to Nothing, and then just work on it like normal.

When you want to send updates back to the database, you reattach the recordset by setting it's Connection property back to an open connection to the database (CurrentProject.Connection is available in Access), and call the UpdateBatch method.

I forget the exact setup, I believe you need to set the cursor type, cursor location, and a couple other properties to certain settings for this to work.

Google for "ADO disconnected recordsets" for more info.

 

[BSman]

Joe,

I did Google ADO disconnected recordset and found some interesting articles; however, it still doesn't seem to do what I need.

I have previously tried setting up forms with recordsets, but the problem was that you could not add new records to the recordset, at least not through the form. The disconnected recordsets seem to have the same situation.

Am I misunderstanding the articles I found?

Bob

 

[JoeAtWork]

I have not tried using disconnected recordsets for adding records in forms.

My first guess would be that you need a field for uniquely identifying the record. In other words, a primary key.

 

[BSman]

Joe,

Actually, nothing I've read or tried with recordsets suggests that new records can be added to any type of recordset. All of the material about disconnected recordsets talked about editing or changing the data, but not about adding new data. And when I tried using a (normal) recordset as a source for a form, additions were not possible.

A recordset is basically an in-memory array of data. Seems crazy to me that you can't define the size of the array (how many potential records might exist in the array) and fill data in as long as you kept within the size (number of records) you defined for the array.

Bob

 

[fneily]

but the problem was that you could not add new records to the recordset,"
A recordset is just a table or its subset known as the output of a query. If your form was bound to an uneditable query, then yes, you can't add records. You didn't give much detail on what actually you did to achieve such a result.
As for the disconnected recordset and a form,
ACC2000: Cannot Set Form Recordset to Disconnected Recordset

http://support.microsoft.com/kb/230737

Microsoft Access forms bound to Recordset objects require a valid ADO Connection object.
How to bind Microsoft Access forms to ADO recordsets

http://support.microsoft.com/kb/281998/en-us

will show you the requirements for an updatable disconnected recordset to be bound to a form.

You'll also notice the line:
"Even though it is possible to bind a form to an ADO recordset that is using data from a Jet database, Microsoft recommends that you use DAO instead. DAO is highly optimized for Jet and typically performs faster than ADO when used with a Jet database."
A "developmental" table mirroring the "production" table will do what you want.
You must like exotic.