Problem Calling Stored Procedure Inside a Transaction

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

First let me start by saying I have posted this question several days ago here:

http://www.xtremevbtalk.com/showthread.php?t=303140

First I setup the ADO Connection

Dim con As New ADODB.Connection
con.ConnectionString = "Provider=MSDASQL.1;Password=xxxxxx;Persist
Security Info=True;User Id=xxxxxx;Data Source=xxxxxx;Initial Catalog=xxxxxx"
con.Open()
con.BeginTrans()

Next I created the command to call the stored procedure and set the
connection on the command

Dim insertCommand As New ADODB.Command
With insertCommand
.Prepared = True
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.ActiveConnection = con
.CommandText = "VALID_SP_NAME"
Dim nbDate = CDate("1/2/3662 12:21:29.310 AM")
.Parameters.Append(.CreateParameter("@pNTNB_ID", _
DataTypeEnum.adDBTimeStamp,
ParameterDirectionEnum.adParamInput, 40, nbDate))
End With
insertCommand.Execute()

Now, when I execute the command without con.BeginTrans() it works just fine.
If I start the transaction it doesn't work at all and gives me a generic
Unspecified Error. Is it possible the provider does not support transactions?
I'm an idiot when it comes to VB so be gentle. ANY ideas at all are
completely welcome, I'm totally spent.

.

Relevant Pages

  • Re: ADO with alternate credentials
    ... Set objADOConnection = CreateObject ... > you can set the username and password on an ADO connection like this: ... > Steve wrote: ... >> 'parse domain name and set command vars ...
    (microsoft.public.windows.server.scripting)
  • ADODB.Connection Open Command appears to hang
    ... I am using some script in Excel to open an ADO connection. ... I am attempting to connect to ORACLE database using ADO connection. ... I've placed log messages into a text file and pinpointed the command causing ...
    (microsoft.public.data.ado)
  • Re: ADO compatibility between Excel XP and 2003
    ... to execute: ... Dim con As ADODB.Connection ... the connection string of the ADO connection is defined alright ...
    (microsoft.public.excel.programming)
  • Re: Find duplicates in FP 6 table?
    ... James D. Houston seemed to utter in ... ADO connection from a Visual Basic 6 app. ... Not in one command. ...
    (microsoft.public.fox.programmer.exchange)
  • movenext command in ADO connection
    ... I have a Word97 macro which uses an ADO connection. ... (icrs.Open cmd,,, adLockBatchOptimistic) ... The MoveNext ... command doesn't work. ...
    (microsoft.public.word.vba.general)