transaction based macros?

[qwertyjjj]

Hi

I have a macro plus some VBA code, which imports moves data around a database, imports a spreadsheet, then performs a few more related data tidy ups within the Access database.

Is there a way to run a transaction over all of this so that if something goes wrong, everything wll be rolled back? For example, if the import of the spreadsheet is rejected due to a bad field, can I roll back the first data changes ?

 

[KenHigg]

I think you have to go with something like Sql Server to get this scope of transactional type functionality...

 

[qwertyjjj]

So there is no way at all of doing something like this in Access?

Unfortunately, the design I'm doing has to be based in Access, so I guess I'll need some sort of workaround.
Maybe import the spreadsheet first into some temporary tables and transfer everything after that ?

 

[qwertyjjj]

Is there a way of running a transaction in VBA code and putting the macros in that function somehow or are VBA transactions only concerned with direct SQL commands ?

 

[KenHigg]

There are some transaction handling commands in access but I have never used them. Try searching access help on 'TRANSACTION', 'BEGIN TRANSACTION' or maybe 'COMMIT TRANSACTION'...

I'm guessing anything macro related would be out of scope and that only sql/query stuff would be relevant...

 

[Adeptus]

I don't think you can enclose macros in VBA transactions... but you CAN convert the macros to VBA! (automatically, even!)