0

I've tried

CurrentProject.Connection.BeginTrans
'   This acts as if you didn't begin a transaction. 
'   Changes are posted real time then gets an error on Commit that you are not in a transaction

Private conn As ADODB.Connection 

Set conn = CurrentProject.Connection
conn.BeginTrans
'   This fails silently in the sense that nothing posts to the database when it gets committed

Set conn = New ADODB.connection
conn.Open GetConnectionString
conn.BeginTrans   
'   This fails on the first SQL statement with
'   "Object variable or with block variable not set"
'   But it clearly is set because it works if I don't start a transaction

'   You would think that starting a transaction explicitly would work.
Private conn As ADODB.Connection 

Set conn = CurrentProject.Connection
conn.Execute "Begin Transaction", , adCmdText Or adExecuteNoRecords
'   This also fails silently in the sense that nothing posts to the database when it gets committed.

Commit looks just as you would expect. Whichever of these is appropriate:

CurrentProject.Connection.CommitTrans
conn.CommitTrans
conn.Execute "Commit Transaction", , adCmdText Or adExecuteNoRecords

Nothing works.

I can easily get it to work on DAO.

DAO.Workspace.BeginTrans

works just as you would expect, except that only DAO statements are committed. Anything posted through ADO is lost, disappearing as if they didn't happen.
But I don't want to switch hundreds of SQL routines to use DAO if I can avoid it.

Note that I'm not trying to create a new connection to the back end. These are all linked tables, with Access compatible SQL statements.

The solution I posted here, doesn't appear to be working after all.

What is the recommended approach to transactions in ADO on local tables?

BWhite
  • 713
  • 1
  • 7
  • 24
  • 2
    for your second example, are you indeed calling `conn.CommitTrans`? – ArcherBird Jul 11 '22 at 21:44
  • Where is the CommitTrans action? ADO code works for me. – June7 Jul 12 '22 at 02:45
  • 'fails on first SQL statement'. What statement? What error? – david Jul 12 '22 at 07:56
  • Question updated with clarification. – BWhite Jul 12 '22 at 23:12
  • @June7 OK. That's interesting. I'll try a sample project and see if I can reproduce. Are you sure it is on local or linked tables, not a direct SQL connection? – BWhite Jul 12 '22 at 23:14
  • Not sure what you mean by 'direct SQL connection'. Data is linked Access backend. `Dim cn As ADODB.Connection Set cn = CurrentProject.Connection cn.BeginTrans` followed by conditional code that leads to cn.CommitTrans or cn.RollbackTrans. – June7 Jul 13 '22 at 02:28
  • @June7 Well, that's the local access tables all right. And exactly what I'm doing. We'll see what the sample project shows tomorrow. – BWhite Jul 14 '22 at 20:23
  • I don't know. Everything works in the sample program. I thought the issue might be that we are running against Postgres instead of SQL Server, but it works fine with both. Back to the drawing board. Rollback works as well. ¯\_(ツ)_/¯ – BWhite Jul 15 '22 at 23:10

0 Answers0