Can this work - if so how?!

I've pulled out a huge recordset, I set activeconnection to nothing, then
set it to my new connection - hit updatebatch - no errors or anything, but
no new records in the second database either.

I don't really want to have to do each field by hand - there's hundreds of
them :)

Any ideas how to make this work?

Did you open the recordset for openBatchOptomistic? and did you open
it as ClientSide cursor? and did you call the updateBatch method?

On Wed, 4 Jul 2001 17:42:02 +0100, "David Middling"

yes yes and yes :)

Which is why I'm confused :)

I'll have a play about with some test data - see whats going on in there.

adLockBatchOptimistic - open type was adOpenStatic.

Hmm, curious.

I pull the records out - just over 1000 in the recordset.

I add a test record.

updatebatch

There is now just ONE more record in the destination DB (ie. the one I just
did using addnew).

I check recordcount again = previous number + 1

I added the new record before changing ative connection - if that record can
change over, why not all the others?  Am I missing something obvious here?
:)

On Wed, 4 Jul 2001 17:42:02 +0100, "David Middling"

I am not sure what you are trying to do but it is not the correct
method for copying records from one database to another, if that is
what you are trying to do.

It sounds like you are changing no records in the recordset, and you
are wondering why nothing is being updated. Anyway, the method of
connecting the one recordset to two different databases sounds
dubious, and is probably in all cases unecessary.

For example if you are using an Access database, a single SQL
statement can do everything.

Post the code that you are trying to use, including the connection and
database details (what kind and version is it?), and someone will
probably suggest a better solution.

Richard.

This is what I'm using - basically need to pull data from an access database
which is used by our catalog software and copy new records (judged by the
max query below) to a SQL server - where it can be used for order searches
and statistical analysis by the rest of the office.

I do not wish to use access for everything (as I am atm) because it is slow
across the network, I want to modify bits of data (might break this program
which seems quite badly written - but works), and I wish to perform queries
based on shipping data in another database - easy to do if they're both on
SQL server.

I'm up for any efficient way of moving the data between the two.

=============================================================
set connAct = server.CreateObject("adodb.connection")
ConnAct.Open "DRIVER=Microsoft Access Driver
(*.mdb);DBQ=\\dave\temp\Catalog.mdb"
set connSQL = server.CreateObject("adodb.connection")
connSQL.open "Driver=SQL Server;Server=arthur;Database=actinic" set rs =
server.CreateObject("adodb.recordset")
set rs2 = server.CreateObject("adodb.recordset")
rs2.open "SELECT max([order sequence number]) AS osn FROM [order]", connSQL

iMaxOSN = rs2("osn") rs.CursorLocation = adUseClient

rs.open "SELECT [order].[order sequence number], [order].[order number],
[order].[status], [order].[total cost], [order].[sub-heading cost],
[order].[shipping total cost], [order].[tax total cost 1], [order].[order
total cost], [order].[total lines], [order].[total lines shipped],
[order].[total lines cancelled], [order].[credit card number],
[order].[ncreditcardissuenumber], [order].[credit card expiry date],
[order].[screditcardstartdate], [order].[date received], [order].[date order
finished], [order].[adjustment message], [order].[invoicecontactid],
[order].[delivercontactid], " & _ "[orderdetail].[status],
[orderdetail].[productreference], [orderdetail].[quantityordered],
[orderdetail].[quantitycancelled], [orderdetail].[quantityshipped],
[orderdetail].[price], [orderdetail].[sproductdescription],
[orderdetail].[orderdetailid], [orderdetail].[nquantitybackordered], " & _
"[person].[name], [person].[company], [person].[address line 1],
[person].[address line 2], [person].[address line 3], [person].[address line
4], [person].[address country], [person].[postal code], [person].[phone
number], [person].[fax number], [person].[email address],
[person].[contactid] " & _ "FROM [order], [orderdetail], [person] WHERE
[order].[order sequence number] = [orderdetail].[ordersequencenumber] AND
[order].[delivercontactid] = [person].[contactid] AND [order].[order
sequence number] > " & iMaxOSN, connAct, adOpenStatic,adLockBatchOptimistic

rs.activeconnection = nothing
rs.activeconnection = connSQL
rs.updatebatch
==============================================================