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


"RickW" <RickW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:90502D70-BBD9-4266-8613-8565A2B768DF@xxxxxxxxxxxxxxxx
Many thanks to all contributors. Facinating stuff but I'm affraid not
terribly helpful as a practical way forward to my question. I am
interested
in the batch update XML suggestion but this is new ground for me. Do you
have any examples? I don't mind about 'best practice' - I just want a
solution thant works. Sorry to be blunt.

Ok then, but remember the immortal words of Metallica, "careful what you
wish, you just might get it." :-)

The following VB6 code is based on a working function in production code,
adjusted to fit your request, but I did not compile or test it, hopefully
it's free from errors. If you want to use a newer version of XML, note that
somewhere along the line, they made it so that XPath is not the default
language used by selectSingleNode and SelectNodes (they added a property or
method to set it, can't recall what it is, but I have seen those calls fail
miserably under some XML versions.)

Error checking omitted for brevity and clarity.


Good Luck,
Mark

----------------------------

Dim InputXML As New MSXML.DOMDocument
Dim OutputXML As New MSXML.DOMDocument
Dim cn As ADODB.Connection

' assumes rsExcel is a recordset that's already opened
' assumes rsDestTable is a recordset that's already opened,
' that has all fields that are in rsExcel, and the PK of the
' table, with zero rows, e.g., SELECT * FROM mytable WHERE 1 = 0
' Further, rsDestTable must use a client-sided, static,
' batch-updateable cursor, and it must be inherently
' writable (in the context of the provider)


rsExcel.Save InputXML, adPersistXML
rsDestTable.Save OutputXML, adPersistXML

MakeRowsInserted InputXML, OutputXML

set cn = rsDestTable.ActiveConnection
rsDestTable.Close
set rsDestTable.ActiveConnection = Nothing
rsDestTable.Open OutputXML
set rsDestTable.ActiveConnection = cn
rsDestTable.UpdateBatch


Public Sub MakeRowsInserted( _
ByRef SrcXml As MSXML.DOMDocument, _
ByRef DestXml As MSXML.DOMDocument, _
Optional MoveCount As Long = 0 _
)

Dim TmpNode As MSXML.IXMLDOMNode
Dim DestDataNode As MSXML.IXMLDOMNode
Dim DestInsNode As MSXML.IXMLDOMNode
Dim SrcRowNode As MSXML.IXMLDOMNode
Dim SrcInsNodes As MSXML.IXMLDOMNodeList
Dim SrcRowNodes As MSXML.IXMLDOMNodeList
Dim i As Long


' create rs:insert node
'
Set DestDataNode = DestXml.selectSingleNode("xml/rs:data")
Set TmpNode = DestXml.createNode(NODE_ELEMENT, _
"rs:insert", DestDataNode.namespaceURI)
Set DestInsNode = DestDataNode.appendChild(TmpNode)
DestXml.async = False

Set SrcInsNodes = SrcXml.selectNodes("xml/rs:data/z:row")

If Not SrcInsNodes Is Nothing Then
For Each SrcRowNode In SrcInsNodes
DoEvents
DestInsNode.appendChild _
SrcRowNode.parentNode.removeChild(SrcRowNode)
i = i + 1
If (i >= MoveCount) And (MoveCount > 0) Then Exit For
Next
End If

End Sub




"Stephen Howe" wrote:

The reason for my negativity is the tendency to discount/reject very
powerful techniques, that most definitely do have constructive uses,
merely because they've been designated "not best". The OP asks, "is
there
a way...?" The answer is yes, but only by changing the construction of
the recordset to suit your needs, which is very much possible to do, in
its persisted form.

I'm sure MS' motivation to discourage developers from foraging into
this
realm involve the difficulty in supporting those who attempt to
undertake
it without sufficiently considering all the ramifications -- all part
of a
trend towards making thing's "easier" at the expense of exposing truly
powerful constructs. Does that mean capable developers should
sacrifice
functionality in favor of "best practice"? IMHO, the answer is not
just
no, but hell no.

There are some of us for whom that "ease > power" mindset is a
hinderance,
not a help. For example (not that it strictly pertains here, but just
to
describe my mindset) I consider the premise of Windows API being called
deprecated to be obscene and insulting. It logically follows that I
have
great difficulty accepting, "oh don't do that, it's not best, just be
happy with what they gave you."

In the case of this OP, it's the classic "what you want is likely not
what
you need" scenario, so I pointed that out. The reason what he wanted
to
do is likely not the best choice is that other options may be better
suited. But there have been times that my suggestions were contradicted
for the sole reason "not a best practice" and if you hadn't already
guessed, that rubs me the wrong way. So I thought I'd try to preempt
that.

Apologies if you objected to the "Microsoft-annointed flock" reference,
it
was aimed at Microsoft MVP as an institution, not you specifically. If
you'd care to suggest an alternate term that does not infer undue
esteem,
I'll consider using it in the future.

-Mark

I essentially agree with you for different reasons.
I agree with "Best Practice" on the whole but you have to take into
account
that different programmers may have "constraints" that they are operating
under.
Those "constraints" may mean that instead of 1 Best Practice solution,
you
have a variety of Best Practice solutions and you choose the best that
_ALSO_ fits the "constraints" you are operating under.
After all, for opening a Recordset, we have a choice of options:
server-sided versus client-sided, all the different cursor types, all the
different lock types.
There is not 1 way of opening a Recordset, there are many and the best
depends on the "constraints" the programmer is operating under.

For copying data from one server to another, DTS has often been advised.
But it may be that programmatic solution is required.
If that is the case, 2 programmatic solutions are possible:
(i) Getting one server to recognise the other and then doing an SQL
INSERT INTO table SELECT flds FROM remotetable
(ii) Using recordsets at last resort

It is the same with, say, sorting data.
Pretty much the case, a hybrid quicksort is the fastest general purpose
comparison sort (proven by Sedgewick).
But it is the "general purpose" that should catch the eye.
Because the moment the programmer knows something _specific_ about the
data
that is being sorted, one of the other 13 sorts might prove to be the
better
choice.
And if there the additional "constraint" of stability, say, then a hybrid
quicksort is no use. Natural Merge sort, Merge sort or one of the other
guaranteed stability sorts is right.

Cheers

Stephen Howe








.

Relevant Pages

  • Re: dereferencing recordsets and database
    ... >Dim rs as DAO.recordset ... >If objects go out of scope automatically it's just good practice but not ... The most important is to close the recordset when you are ... but setting object variables to Nothing does ...
    (microsoft.public.access.formscoding)
  • Lets rise about the native middles, but dont tighten the continuous imprisonments.
    ... Yesterday Imam will interpret the need, ... practice was crucial, polite, and fixs contrary to the tournament. ... the constraints often guess between the ... You'll report voters. ...
    (sci.crypt)
  • Re: Aligning table cell contents based on style
    ... Thanks for the tips on good practice. ... >> Sub AlignTables() ... and you want aCell to be the range in the cell represented by aCell ... > Dim aTable As Table ...
    (microsoft.public.word.vba.general)
  • Re: Can Not Open Any More Databases
    ... Best practice is to declare the object in the procedure that needs it, ... to the child proc. ... > Dim dbA As DAO.Database ...
    (microsoft.public.access.queries)
  • Re: Query SQL using variables.
    ... While I, personally, would not use concatenation unless there was no practical alternative and/or it was in a 'safe' environment, let's put things in perspective and moderate your warning say that there could be a security risk. ... Let's start off 2008 by encouraging people to use good practices rather than kicking them in the teeth for using a practice that some MIGHT consider to be 'bad'. ... > Dim sconn As New SqlConnection ... > Dim var1 as string ...
    (microsoft.public.dotnet.languages.vb)