mardi 14 juin 2016

Visual Basic and ODBC error : "[IBM Iseries Access ODBC Driver] statement violates access rule: connection is set to read only."

I'm experiencing an ODBC connection using Visual Basic, I can easily send a SQL request then write the result (in a recordset) into an Excel file. The problem came when I tried to call a procedure, I got the error (as you can see in the title) and I didn't find an answer anywhere.

The code :

Set objMyConn = CreateObject("ADODB.Connection")
Set objMyRecordset = CreateObject("ADODB.Recordset")
Set objMyCmd = CreateObject("ADODB.Command")
Set objMyParam = CreateObject("ADODB.Parameter")

Set excelApp = CreateObject("Excel.Application")
Set workBook = excelApp.Workbooks.Add
Set workSheet = workBook.ActiveSheet

Dim param
Dim strSQL
param = InputBox("AGENTS ou CLIENT ?", "Parameter")

' Set CommandText equal to the stored procedure name.
objMyCmd.CommandText = "GETCUSNAME"

' Connect to the data source.
strSQL = "My connection string"
objMyConn.Open strSQL

objMyCmd.ActiveConnection = objMyConn

' Automatically fill in parameter info from stored procedure.
objMyCmd.Parameters.Refresh

' Set the parameter
objMyCmd(1) = param

' Execute query
Set objMyRecordset = objMyCmd.Execute

'Copy to the Excel file
workSheet.UsedRange.Clear
workSheet.Range("A1").CopyFromRecordset objMyRecordset
workSheet.SaveAs("C:TempexcelStoredTest.xlsx")

'All done
excelApp.ActiveWorkbook.Close

excelApp.Application.Quit

objRs.Close
objConn.Close
Set objMyRecordset = Nothing
Set objMyConn = Nothing
Set objMyCmd = Nothing
Set objMyParam = Nothing

The error comes to the line 27 ("objMyCmd.Parameters.Refresh"), when I comment this line, the same error appears for the next instruction, and if I comment both of them, the query can't execute because there's no parameter.

I also tested a procedure without parameter, but the same error appears.

Of all the solution I've tested, none worked.

The main solution proposed was to go to the regedit and change the value of AllowProcCalls by 1, it doesn't worked, as the setting changed in ODBC administration tools.

Also, I'm not connected as a readonly user, I've got all the rights.

If it can help, here is the error window (the message is in french but it's the title's one, so I don't think it's a problem to put it here as an image) :

Error window

Aucun commentaire:

Enregistrer un commentaire