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

 

[AccessVandal via AccessMonster.com]

Try removing "Do Until .EOF" because it's not end of file and it's looping
with the same DMax ID.

 

[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

 

[AccessVandal via AccessMonster.com]

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.

Why do you need to open the table with two recordsets? According to your code,
you don't need to unless you tell us why.

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.

Again, if you didn't my message. DMax() will always get the same ID.

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

With AutoNumber, you don't even need any coding. Can you tell us why you need
to know the autonumber ID?

 

[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