is "transaction" my answer?

[ianohlander]

Hello all,

Currently, I am working on an Excel ss application as a front-end to an Access Db (long story short- that's what the customer wanted, even though it could have all been done in Access with OLE to Excel ss.)

Anyway, I have a number of SQL statements that I need to execute from a VBA subroutine. I have 3 tables: customerTable, roomTable, and ReservationTable. customerTable has numerous fields, including customerIDNumber as key. reservationTable has numerous fields, including reservationIDNumber (as key), customerIDNumber, and roomIDNumber. Finally, roomTable has numerous fields including roomIDNumber and reservationIDNumber (the last is for checking availability- if it contains the default -1, then it is free).

Now, I have to to basically accomplish this:

1) get a room from roomTalbe (of particular type and number)
2) create a customer in customerTable
3) create a reservation in reservationTable, and set it's customerIDNumber to that of the new customer, and it's roomIDNumber to that of the retrieved room.
4) Finally, set the retrieved room's reservationIDNumber to the new reservation's ID number.

So I have two questions:
a) should I use a transaction to do this (and how)?
b) since the *IDNumber's are auto-generated by access, how do I get (and hold) their ID numbers without another requery after their creation?

I have been leaning toward the AS keyword and am trying out the "transaction" block. But both are giving me errors.

I'm really not sure how to proceed, except to (redundantly) retrieve the room through 1 query, store the ID in a variable. Then create a customer. Then query for that customer so I can access/store it's ID. Then create a reservation and then query it so I can access/store it's ID. Finally, using 2 final sql statements, make the appropriate updates in reservationTable and roomTable.

But that would be 7 DB queryies/updates- and that seems riduculously complicated, inefficient, and in fact a drain on resources.

So what would be the best way to do that?

Thanks for your help,

Ian Ohlander
ianohlander@yahoo.com