Transaction problem between DAO and ADO

[Anand]

Hi,
Was using DAO all along and following code was working fine:

wrkspc.Begintrans

with rst
..MoveFirst
Do Until .EOF
..AddNew
!pkID = DMax("pkID", "Table") +1
....other table fields
..Update
..MoveNext
Loop
End with

User Confirmation...
wrkspc.committrans

User Cancellation
wrkspc.rollback

Have recently switched to ADO - not much experience with it either...
Reworded the above code to make it suitable for ADO. Using connection object
to begin/commit/rollback the transaction.

Problem is that the code raises an error in ADO because the DMax function
(in ADO) gets the same pkID each time the loop executes - causing a primary
key integrity error. Have a feeling this is something to do with the way ADO
handles transactions - the first record is not added yet when in between a
begintrans and commit and causes DMax to get the same pkID. Any way out of
this?? Am I right in using connection object to process transactions?
TIA

Anand

 

[roger]

Wait a minute. what are you trying to do here?

Here's what I see:

with rst 'you have a recordset "rst"
.MoveFirst ' you goto to the 1st record
Do Until .EOF 'you're going to somthing to all records?

.AddNew ' you ADDNEW? (jump to the end of RST to add a record!)

!pkID = DMax("pkID", "Table") +1 'now you get the max value of pkID from a table named "TABLE"?????

...other table fields
.Update 'save
.MoveNext ' you can't movenext you're on the "new" record
Loop ' and you start over.
End with

To add one record to a table here's what I'd do:

dim NewID as long,objMyTable as object
set objMyTable = CurrentDb.OpenRecordset("tblMyTable")
newID =DMax("pkID", "tblMyTable") +1
with rst
.AddNew
.pkID = newID
...other table fields
.Update
End with

(actually, I'd just use an autonumber)

--Or are you trying to do something to all the records? (do until EOF, loop)
Like, I do that when I open two tables and for each record on this one, add
a record to that one, but that requires two objects.

HTH
roger

BTW DMax() is not ADO or DAO, its just VBA.

 

[Anand]

Sorry Roger..did not make myself very clear. I am trying to add records from
one rst to another - opened on the same table.. there are two recordset
objects opened in ADO.

Each loop on rst1 will add a new record in rst2. Logically I need a new pkID
in rst2 for each loop on rst1. This is not happening -DMax gets the same
pkID for all the loops - generating the PK error for rst1. Any suggestions on
how to solve this? I use the connection object for transaction processing.

If I use AutoNumbers can I get VBA to tell me the last pkID inserted?
Sometimes I need to know this for further processing.

Thanks,
Anand

 

[roger]

DMax gets the same pkID for all the loops - generating the PK error for
rst1. Any suggestions on how to solve this?

store it as a separate variable:
dim newID as long
newID =DMax("pkID", "tblMyTable") '1st store the starting value (the one
you CAN get)
do while whatever 'start your loop
newID=newID+1 ' increment IT by 1
AddNew
..pkID = newID
' etc etc etc
loop

If I use AutoNumbers can I get VBA to tell me the last pkID inserted?
Sometimes I need to know this for further processing.

Assuming .pkAutoID is an autonumber:

..addnew
set other fields here
..update ' saves record and creates the Autonumber
myStoredValue = .pkautoID ' store your autonumber from the ADO record
..movenext 'THEN movenext or Addnew or whatever


HTH
roger

 

[Anand]

Thanks Roger, Problem solved.

Anand

rst1. Any suggestions on how to solve this?

store it as a separate variable:
dim newID as long
newID =DMax("pkID", "tblMyTable") '1st store the starting value (the one
you CAN get)
do while whatever 'start your loop
newID=newID+1 ' increment IT by 1
AddNew
.pkID = newID
' etc etc etc
loop



Assuming .pkAutoID is an autonumber:

.addnew
set other fields here
.update ' saves record and creates the Autonumber
myStoredValue = .pkautoID ' store your autonumber from the ADO record
.movenext 'THEN movenext or Addnew or whatever


HTH
roger