ADO Transaction with linked MDB file?

[Plateriot]

I get 'Operation Not available for this object'
when I try these lines:

Public cnMain As New ADODB.Connection
' fails here ----> Set cnMain = CurrentDb.Connection

'my intent is to do transactions that I normally would with this line
Set cnMain = CurrentProject.connection (in an ADP)
..
..
..
cnMain.BeginTrans

Can I do ADO Transactions with MDB's? (linked or otherwise?)

I know many arguments can be made about ADO and DAO, but we eventually plan
to upgrade to SQL Server and it would be nice not to have to re-write the
code.

 

[Brendan Reynolds]

I get 'Operation Not available for this object'
when I try these lines:

Public cnMain As New ADODB.Connection
' fails here ----> Set cnMain = CurrentDb.Connection

'my intent is to do transactions that I normally would with this line
Set cnMain = CurrentProject.connection (in an ADP)
.
.
.
cnMain.BeginTrans

Can I do ADO Transactions with MDB's? (linked or otherwise?)

I know many arguments can be made about ADO and DAO, but we eventually
plan
to upgrade to SQL Server and it would be nice not to have to re-write the
code.

You can use CurrentProject.Connection in an MDB too.

As I understand it, CurrentDb returns a reference to a DAO Database object,
so all of its object properties will be DAO objects, not ADO objects.

 

[Plateriot]

Thank you. I'll try it now.
I thought that CurrentProject had to reference an .ADP hence - 'project'

 

[Plateriot]

Got it to work, but now - is this SQL server related? ---
'Trying to insert a child record into a Parent that was created by a
transaction -
I get this error:
Requested operation requires an OLE DB Session object, which is not
supported by the current provider. ...

This worked with SQL Server
Here's my Code (I'm trying to run this while the cnMain.BeginTrans is in
session)
(but will it work with an MDB?)

lCurrentReqID = lReqID
'Add a Business partner ID with Related ReqID to the tbl_BP_Requests table
strSQL = "Insert into tbl_BP_Requests (ReqID,BusinessPartnerID) " _
& " Values(" & lCurrentReqID & "," & Me.cmbBusinessPartner.Value & ")"

Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnMain
.CommandText = strSQL
.CommandType = adCmdText
.Execute
End With

 

[Brendan Reynolds]

Got it to work, but now - is this SQL server related? ---
'Trying to insert a child record into a Parent that was created by a
transaction -
I get this error:
Requested operation requires an OLE DB Session object, which is not
supported by the current provider. ...

This worked with SQL Server
Here's my Code (I'm trying to run this while the cnMain.BeginTrans is in
session)
(but will it work with an MDB?)

lCurrentReqID = lReqID
'Add a Business partner ID with Related ReqID to the tbl_BP_Requests
table
strSQL = "Insert into tbl_BP_Requests (ReqID,BusinessPartnerID) " _
& " Values(" & lCurrentReqID & "," & Me.cmbBusinessPartner.Value & ")"

Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnMain
.CommandText = strSQL
.CommandType = adCmdText
.Execute
End With

I can't say whether this will make any difference or not, but it might be
worth a try ...

I understand that assigning a connection without using the keyword "Set"
creates a new copy of the connection, while using Set just uses the pointer
to the existing instance of the connection. So you could try changing ...

.ActiveConnection = cnMain

.... to ...

Set .ActiveConnection = cnMain

I'm not sure if it will help, but it won't hurt.

 

[Plateriot]

That worked. And I finally learned what 'Set' does. Thank you.

 

[Clifford Bass]

Hi Plateriot,

You can simplify that greatly:

'Add a Business partner ID with Related ReqID to the tbl_BP_Requests
table
cnMain.Execute "Insert into tbl_BP_Requests (ReqID,BusinessPartnerID) " _
& " Values (" & lCurrentReqID & "," & Me.cmbBusinessPartner.Value &
")", , _
adCmdText

Clifford Bass

:

[snip]

'Add a Business partner ID with Related ReqID to the tbl_BP_Requests table
strSQL = "Insert into tbl_BP_Requests (ReqID,BusinessPartnerID) " _
& " Values(" & lCurrentReqID & "," & Me.cmbBusinessPartner.Value & ")"

Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnMain
.CommandText = strSQL
.CommandType = adCmdText
.Execute
End With

[snip]