Re: Can I append an ADO recordset to an Access table in one go?
- From: "Mark J. McGinty" <mmcginty@xxxxxxxxxxxxxxx>
- Date: Mon, 29 Oct 2021 08:23:01 -0700
"RickW" <RickW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9BEC2622-0E0D-4D91-A35F-88E1EE2E0916@xxxxxxxxxxxxxxxx
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
I
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
appending
each record one at a time. This seems very inefficient and I don't want
to
do this if there is a better way. Can anyone help please? I am using
Office
2003.
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.
-Mark
Regards
Rick
.
- Follow-Ups:
- Re: Can I append an ADO recordset to an Access table in one go?
- From: Bob Barrows [MVP]
- Re: Can I append an ADO recordset to an Access table in one go?
- Prev by Date: Unable to persist/save ADO records
- Next by Date: Re: Unable to persist/save ADO records
- Previous by thread: Unable to persist/save ADO records
- Next by thread: Re: Can I append an ADO recordset to an Access table in one go?
- Index(es):
Relevant Pages
|
|
