Running stored procedure from module in .adp file
-
Hello,
I'm trying to get the ADO to behave-setting VBA declared globals to arguments in SPROC and then executing. I keep getting error message "Items cannot be found in collection...". Any ideas ??
Thanks much
This is the On_Click event code in VBA module...
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim params As ADODB.Parameters
Set cmd = New ADODB.Command
With cmd
.CommandText = "spEVA_Export"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
params.Refresh
params("@EndDate") = EndDate
params("@Account1") = Account1
Set rs = cmd.Execute
-
I'd like to run some test on my pc... can you send me the proc (at least the header so that I know what to work with)?
-
It's on a different PC. I'll remote in later this afternoon and get it to you.
THanks...
-
OK here's the full sub...
Private Sub Command84_Click()
On Error GoTo Err_Command84_Click
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim params As ADODB.Parameters
Set cmd = New ADODB.Command
With cmd
.CommandText = "spEVA_Export"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
params.Refresh
params("@EndDate") = EndDate
params("@Account1") = Account1
Set rs = cmd.Execute
Exit_Command84_Click:
Exit Sub
Err_Command84_Click:
MsgBox Err.Description
Resume Exit_Command84_Click
End Sub
-
Thanks. I meant the code of the stored proc on the sql server.
-
Ohh sorry, here it is....the parameters are referenced in the WHERE clause
CREATE PROCEDURE dbo.spEVA_Export(@EndDate varchar(20), @Account1 varchar(20))
AS INSERT INTO dbo.tblEVA_Export
([Account Number], Filler0, Filler01, [Record Sequence Num], [Security Name], Rate, [Maturity Date], Filler1, [Industry Code], CUSIP, Price, [Price Sign],
[Pricing Date], [Price Source Code], Filler2,
, [Moody's Rating], [Units Invested], [Units Sign], [Book cost], [Inv Sign], Filler3, [Market value],[MV Sign], [Estimated Annual Income], [Estimated Annual Income Sign], [Accrued Income], [Accrued Income Sign], Filler4, [Amortized Units],
[Amortized Unit Sign], [Asset Classification Code], Filler5)
SELECT dbo.fnPadZeroLeft(dbo.tblAccount.Account_ID) AS [Account Number], dbo.fnZeroFiller(6) AS Filler0, dbo.fnSpacer(9) AS Filler01, dbo.fnZeroFiller(0)
AS [Record Sequence Num], dbo.tblAsset.Short_Description AS [Security Name], dbo.fnRemoveDecimal(dbo.tblAsset.Nominal_Interest_Rate, 10, 6)
AS Rate, DATEPART(yy, dbo.tblAsset.Maturity_Date) AS [Maturity Date], dbo.fnSpacer(2) AS Filler1,
dbo.tblAsset.Industry_Category_ID AS [Industry Code], dbo.tblAsset.Identity_Number AS CUSIP, dbo.fnRemoveDecimal(ABS(dbo.tblPosition.Price), 9, 5)
AS Price, dbo.fnSpacer(1) AS [Price Sign], DATEPART(yy, dbo.tblPosition.Price_Date) AS [Pricing Date], dbo.fnSpacer(0) AS [Price Source Code],
dbo.fnSpacer(1) AS Filler2, dbo.tblPosition.Standard_Poors_Rating AS
, dbo.tblPosition.Moodys_Rating AS [Moody's Rating],dbo.fnRemoveDecimal(dbo.tblPosition.Quantity, 11, 2) AS [Units Invested], dbo.fnSpacer(1) AS [Units Sign],
dbo.fnRemoveDecimal(dbo.tblPosition.Cost_Base, 11, 2) AS [Book Cost], dbo.fnSpacer(1) AS [Inv Sign], dbo.fnSpacer(2) AS Filler3,
dbo.fnRemoveDecimal(ABS(dbo.tblPosition.Market_Value_Base), 13, 2) AS [Market value], dbo.fnSpacer(1) AS [MV Sign],
dbo.fnRemoveDecimal(dbo.tblPosition.Estimated_Annual_Income, 11, 2) AS [Estimated Annual Income], dbo.fnSpacer(1)
AS [Estimated Annual Income Sign], dbo.fnRemoveDecimal(dbo.tblPosition.Accrued_Income_Base, 11, 2) AS [Accrued Income], dbo.fnSpacer(1)
AS [Accrued Income Sign], dbo.fnSpacer(6) AS Filler4, dbo.fnRemoveDecimal(ABS(dbo.tblPosition.Paydown_Quantity), 16, 4) AS [Amortized Units],
dbo.fnSpacer(1) AS [Amortized Unit Sign], dbo.tblAssetTaxCategory.Level_2_Category_ID AS [Asset Classification Code], dbo.fnZeroFiller(6)
AS Filler5
FROM dbo.tblAccountGroup INNER JOIN
dbo.tblAsset INNER JOIN
dbo.tblAccount INNER JOIN
dbo.tblPosition ON dbo.tblAccount.Account_ID = dbo.tblPosition.Account_ID ON dbo.tblAsset.Asset_ID = dbo.tblPosition.Asset_ID ON
dbo.tblAccountGroup.Account_ID = dbo.tblAccount.Account_ID INNER JOIN
dbo.tblAssetTaxIntersect ON dbo.tblAsset.Asset_ID = dbo.tblAssetTaxIntersect.Asset_ID INNER JOIN
dbo.tblAssetTaxCategory ON dbo.tblAssetTaxIntersect.Taxonomy_ID = dbo.tblAssetTaxCategory.Taxonomy_ID AND
dbo.tblAssetTaxIntersect.Category_ID = dbo.tblAssetTaxCategory.Category_ID
WHERE (((tblAssetTaxCategory.Taxonomy_ID)='0002')) AND ((tblAccountGroup.Group_ID)=@Account1) AND ((tblPosition.Evaluation_Date)=@EndDate);
GO
-
I'd like to do this for you but I don't have time... so here's a working sample code from one of my programs :
Private Function exec_GetStartUpForm(ByVal FkADP As Integer, ByRef FormName As String, ByRef Maximized As Boolean)
On Error GoTo Gestion
Dim MyCmd As ADODB.Command
Set MyCmd = New ADODB.Command
MyCmd.CommandText = "dbo.GetStartUpForm"
MyCmd.CommandType = adCmdStoredProc
Dim MyParam As ADODB.Parameter
Set MyParam = New ADODB.Parameter
MyParam.Name = "@FkADP"
MyParam.Value = FkADP
MyParam.Size = 4
MyParam.Direction = adParamInput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@FormName"
MyParam.Value = FormName
MyParam.Size = 100
MyParam.Direction = adParamInputOutput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@Maximized"
MyParam.Value = Maximized
MyParam.Size = 1
MyParam.Direction = adParamInputOutput
MyParam.Type = adBoolean
MyCmd.Parameters.Append MyParam
MyCmd.ActiveConnection = MyCn
MyCmd.Execute
FormName = MyCmd.Parameters("@FormName").Value
Maximized = MyCmd.Parameters("@Maximized").Value
Set MyParam = Nothing
Set MyCmd = Nothing
Exit Function
Gestion:
Select Case Err.Number
Case Else
Select Case ErrHandler(ModuleName, "ModGlobals", "exec_GetStartUpForm", Err, Erl())
Case ErrResume
Resume
Case ErrResumeNext
Resume Next
Case ErrExit
MsgBox Err.Description & " : " & Err.Number
Exit Function
End Select
End Select
End Function
-
OK thanks for the code. I will deduce the necessary components. I'm new to SQL server and for that matter coding with ADO, so I wasn't sure if I needed to Dim MyCmd As ADODB.Command and then instantiate Set MyCmd = New ADODB.Command when I've already created the Sproc "spEVA_Export" with applicable parameters in SQL Server. You know? But it looks like I do if the actions are to be executed from an .adp file, correct? Intuitively, it seemed redundant to do so.
Thanks much........RO
-
It's not really redundant. Sql server and ado are 2 different things operating in 2 different environement... and to call a procedure you need to tell the name, the parameters and the values. There's no way around that in any language.
-
Very well...thanks again...I will persevere.
-
OK, This is killing me... I am still getting 'Connection cannot be used to perform this operation...' error. I'm not understanding how ADO cannot make the logical conclusion that I'm using the database set forth when I setup the .adp file.
All I need to do is pass two globals as parameters into a stored procedure- which also happens to reside in .adp file. (The code for the sproc is shown above) If I were to run the sproc w/o any parameter, then it's a piece of cake ... DoCmd.OpenStoredProcedure "spEVA_Export"
Why then should passing parameters become so much more code intensive? This is the code I have...
Function AdoSpec()
On Error GoTo FreeWilly
Dim MyCmd As ADODB.Command
Set MyCmd = New ADODB.Command
MyCmd.CommandText = "spEVA_Export"
MyCmd.CommandType = adCmdStoredProc
Dim MyParam As ADODB.Parameter
Dim MyCn As ADODB.Connection
Set MyCn = New ADODB.Connection
MyCn = DefaultDatabase
Set MyParam = New ADODB.Parameter
MyParam.Name = "@EndDate"
MyParam.Value = EndDate
MyParam.Size = 7
MyParam.Direction = adParamInput
MyParam.Type = adVariant
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@Account1"
MyParam.Value = Account1
MyParam.Size = 9
MyParam.Direction = adParamInput
MyParam.Type = adVariant
MyCmd.Execute , MyParam /*Connection error occurs here*/
Set MyParam = Nothing
Set MyCmd = Nothing
Exit Function
-
no 'MyCmd.ActiveConnection' statement? look at the example code again on page one. other than that, not sure what you could have missed?
-
Ya that's a big one. You must set a connection for the command object and that connection must be opened when executing the code.
Also I think that the parameters size are off a bit. For dates and int you can leave those out. My code is computer generated so it outputs that for every parameter even though it's not mandatory.
-
Since he is using an adp, you do not have to set a connection, like you do when you use an mdb. An access adp maintains its own connection to the SQL server.
Here is a subroutine that I use to execute a stored procedure that has parameters from an adp. This creates and appends the parameters at one time, in the same statement, though it is not necessary to do it this way.
The major difference that I see is that you need to set the cmd.ActiveConnection = CurrentProject.Connection.
Hope this helps.
Dim cmd As ADODB.Command
On Error GoTo HandleErr
DoCmd.Hourglass True
'Create command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "dbo.procIngredientLotNumberPut"
.CommandType = adCmdStoredProc
.Parameters.Append cmd.CreateParameter("@Ingredient", adVarChar, adParamInput, 25, gIngredientInfo.oIngredient)
.Parameters.Append cmd.CreateParameter("@LotNumber", adVarChar, adParamInput, 50, gIngredientInfo.oLotNumber)
.Parameters.Append cmd.CreateParameter("@Supplier", adVarChar, adParamInput, 50, gIngredientInfo.oSupplier)
.Parameters.Append cmd.CreateParameter("@SupplierDoc", adVarChar, adParamInput, 10, gIngredientInfo.oSupplierDocument)
.Parameters.Append cmd.CreateParameter("@ManufactureDate", adDate, adParamInput, , gIngredientInfo.oManufactureDate)
.Parameters.Append cmd.CreateParameter("@UserId", adVarChar, adParamInput, 8, gUserData.oUserID)
.Parameters.Append cmd.CreateParameter("@IngredientLotKey", adInteger, adParamOutput, , gIngredientInfo.oIngredientLotKey)
.Execute
End With
If Not IsNull(cmd("@IngredientLotKey")) Then gIngredientInfo.oIngredientLotKey = cmd("@IngredientLotKey")
'Destroy command object
Set cmd = Nothing
ExitHere:
DoCmd.Hourglass False
Exit Sub
' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
HandleErr:
Err.Raise Err.Number, Err.Source, Err.Description
Resume ExitHere
' End Error handling block.
End Sub
-
Carla, Thanks so much. I thought it was redundant to re-connect with an .adp file. Nevertheless, I got it to work with establishing connection, but I will tailor it to be more effecient as you suggested.
This works but is overkill...
Function AdoSpec()
On Error GoTo FreeWilly
Dim MyCmd As ADODB.Command
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim MyParams As Parameters
Set MyCmd = New ADODB.Command
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider='sqloledb';Data Source='OWS78002878791';" & _
"Initial Catalog='24_File';Integrated Security='SSPI';"
cn.Open
With MyCmd
.CommandText = "spEVA_Export"
.CommandType = adCmdStoredProc
Set MyParams = .Parameters
End With
MyParams.Append MyCmd.CreateParameter("EndDate", adVarChar, adParamInput, 100, EndDate)
MyParams.Append MyCmd.CreateParameter("Account1", adVarChar, adParamInput, 100, Account1)
MyCmd.ActiveConnection = cn
Set rs = MyCmd.Execute()
Set MyCmd = Nothing
Set MyParams = Nothing
Exit Function
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply