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) :
Aucun commentaire:
Enregistrer un commentaire