INFO: Certain Recordsets Cannot Directly Post to Database (190903)

SUMMARY

The Remote Data Service objects RDSServer.DataFactory and RDS.DataControl offer a method CreateRecordset that allows you to dynamically create a recordset in memory. However, there is no way to post this recordset to a back-end database directly, even if the created recordset matches the structure of an existing table on your back-end database.

This also applies if a recordset is created with ActiveX Data Objects (ADO) code.

MORE INFORMATION

It is possible to dynamically create a recordset where its Fields collection exactly matches the Fields collection of a recordset opened off a back-end database. However, you need additional information to link a recordset opened off the database to a recordset in memory. Specifically, you need read-only metadata that is provided by the back-end database. This information allows you to post changes to that database later, even if the recordset was disconnected at some point.

CreateRecordset nor manually created recordsets allow you to specify this metadata. In fact, this information cannot be specified either through OLE DB or ADO. While on the surface you can create a dynamic recordset that looks and feels (through the ADO object model or OLE DB interfaces/properties) like an actual table, this does not mean there is enough existing information to actually link it to the back-end table.

Use one of the following methods to work around this:

Create the actual table on the back-end database, and choose a recordset to open off of that table. If you are using datastores that use SQL syntax, you can accomplish this with the CREATE TABLE statement.
Iterate through the dynamically-created recordset, and copy the contents of each record manually into a recordset opened off the corresponding physical table in the back-end database.