Re: Can I append an ADO recordset to an Access table in one go?

"RickW" <RickW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
Is there a way of appending an existing ADO recordset to an Access table
without looping through the recordset and appending each record in turn?
Surely the answer is yes - and if so, how do I do it?

Background: I want to import from Excel but some of the fields can have
mixed numeric/text data types so in order to force these to import as text
have to use ADO with a connection including IMEX=1 as one of the extended
properties. This gives me an ADO recordset containing the data I want to
import, but the only way I can think of do this is by looping and
each record one at a time. This seems very inefficient and I don't want
do this if there is a better way. Can anyone help please? I am using

It is possible to transform records in a recordset from rows as returned by
a data source, to rows inserted into the recordset, at the XML level, you
can create an <rs:insert> node under <rs:data>, and move each of the <z:row>
nodes into it. Then connect it to the data provider, call UpdateBatch, and
your rows are inserted.

But there are a number of conditions and qualifiers:

The recordset must be able to be connected to the destination data provider,
and it must be batch-updateable. These are qualities of the schema portion
of the XML. You can't disconnect a recordset from Excel, and then connect
it to Access, but you can move the data nodes from one XML object to
another, provided that none of the data conflicts with the destination
schema (i.e., no data type mismatches, a value/attribute for any field with
rs:maybenull='false' must be included in every z:row, etc.)

But beyond validity in context of the schema, the data portion is
independent and more-or-less universal; the value of a given cannonical type
will be represented in a z:row the same way, regardless of provider. (Of
course, supported types may vary between providers, but absolute worst case,
for incompatibilities, is you make the destination a string type, and deal
with conversion after the fact.)

The easiest way to create the destination schema XML object is to open a
recordset that returns zero rows on the target table, and then Save it to an
XML object (rather than a file

Also, the value/attribute that stores the identity column can't be included
in any of the z:row nodes, for obvious reasons.

Further, if any updates have been done to the recordset, after loading it
from the provider, but before transforming it into an insert, you have to
fold each rs:update node into a coherent z:row.

A few notes and warnings:

This is not a trival undertaking. It is neither supported nor documented.
The Microsoft-annointed flock will tell you, "not a best practice"
(apparently, the practice of making things more functional than they are out
of the box is somehow other than "best".)

Large numbers of rows in batch updates tend to perform poorly, you're better
off to chunk them up, no more than a few hundred rows per batch.

Constraint/integrity violations caused by any rows inserted are supposed to
fail the whole batch, but it doesn't always happen like that.

SQL Profiler shows that under the hood, ADO is builds a parameterized INSERT
statement for each row. Since you could do that yourself, and skip the
overhead of manipulating the XML, it's worth consideration.




Relevant Pages

  • Re: ADO VB6 Extracting a recordset from existing recordset
    ... Declare a recordset variable ... Persist the filtered recordset to an XML object ... Dim DestNode As IXMLDOMNode, SrcNode As IXMLDOMNode ...
  • Re: Error -2147418113 with AddNew in transaction
    ... > Microsoft OLE DB Provider for SQL Server 7.01 ... > Any operation following a rollback or a commit transaction on a recordset ... > The OLE DB provider must support preserving cursors. ... > SQL Server supports preserving cursors on Commit and Abort. ...
  • Re: Data provider or other service returned an E_FAIL status every
    ... 'repeat each merge above for each recordset ... SQLOLEDB provider and that the stream itself is using a TEXT provider. ... For Each objField In recSourceClone.Fields ... I am thinking I could run a connection ...
  • Re: Newbie Q. for referencing DAO recordsets with VBA in Access front
    ... recordset. ... designer, keep in mind that sometimes the columns are renamed to something ... isn´t supported by that provider. ... HTH, Jens Suessmeyer. ...
  • Re: MDAC 2.8 vs MDAC 2.7
    ... Actually RecordCount has to do with the provider, ... If just changing of the connection string to use OLEDB provider solves this ... then it looks like a bug in OLEDB for ODBC provider or in ODBC ... But do you get an actual recordset opened in your case? ...