I have a large VB program that connects to Oracle database.

strCn = "Driver={Microsoft ODBC for Oracle};" & _
        "SERVER=PSPROD;"

Set Cn = New ADODB.Connection
Cn.ConnectionString = strCn
Cn.CursorLocation = adUseNone
Cn.Open

There are many users of my program so I have a table that contains each user's login name and their access rights to the various tables. I create a recordset of all users when the program is started and then select USERNAME and GRANTED_ROLE from the record set where USERNAME and PASSWORD are found. I use a "Set role 'GRANTED_ROLE' identified by 'password'" statment and Cn.Execute statement to set up the user's access rights. This is all done in a Module.

On a form, I want to call a Stored Procedure that will SELECT, INSERT and UPDATE information into another schema's tables. I am able to call and run the stored procedure when I create a new connection to the database with this code:

Dim cmd5040 As ADODB.Command Dim conn5040 As ADODB.Connection Dim param5040 As ADODB.Parameter

Set conn5040 = New ADODB.Connection conn5040 = "Driver={Microsoft ODBC for Oracle};" & _ "SERVER=PSPROD; UID=XXXXXXX; PWD=XXXXXXXX" conn5040.Open

Set cmd5040 = New ADODB.Command

With cmd5040 .ActiveConnection = conn5040 .CommandType = adCmdStoredProc .CommandText = "S4115040_IMPORT_NEWBIDITEMSPES.S4115040_CheckTime"

.Parameters.Append .CreateParameter(, adInteger, adParamInputOutput, 5)
.Parameters.Append .CreateParameter(, adVarChar, adParamInputOutput, 400)

End With

cmd5040(0) = 0 cmd5040(1) = "" cmd5040.CommandTimeout = 300

cmd5040.Execute conn5040.Close

However, I get the error message "-2147217900 [Microsoft][ODCB driver for Oracle]Syntax error or access violation" when I attempt to use the same connection ('Cn') when the program first started. My code is:

Dim cmd5040 As ADODB.Command Dim param5040 As ADODB.Parameter

Set cmd5040 = New ADODB.Command

With cmd5040 .ActiveConnection = Cn .CommandType = adCmdStoredProc .CommandText = "S4115040_IMPORT_NEWBIDITEMSPES.S4115040_CheckTime"

.Parameters.Append .CreateParameter(, adInteger, adParamInputOutput, 5)
.Parameters.Append .CreateParameter(, adVarChar, adParamInputOutput, 400)

End With

cmd5040(0) = 0 cmd5040(1) = ""

cmd5040.Execute

I have worked with my DBA. She has given me direct grants and direct execute privliges and I am still get the error message.

What am I doing wrong? Should I be able to use the original connection to run a stored procedure? Or must I create a second connection?