Microsoft ODBC driver for Oracle Syntax error or access violation (-2147217900)

Question

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?

Answer 1

edit: on reviewing your code, I notice that the original connection Cn specifies the driver and the server name whereas the second connection conn5040 specifies the driver, server name, user and password.

Therefore, it may be that the stored procedure you are calling requires a user and password which the original cn connection does not specify

---

Original answer:

Make sure that the variable cn is still in scope when you try to use it. If it is declared in a module then it should be declared outside of any Sub or Function and, if other modules should be able to access it, it should be declared as Public

Option Explicit

Public cn as ADODB.Connection

Sub foo()
...

Presuming that cn is still in scope, you could examine the State property of the object which cn references to see if the Connection is still open.

If (cn.State = adStateClosed) Then
    ' we have a problem
    ...