anthonytr
asked on
Run Stored Procedure uisng ADO
Hi,
I am trying to run the following stored procedure from my VBA Access application which returns the next available WO number, but i'm not getting the code quite right.
My SP is:
This, when run directly in MSSQL server, returns the correct number. The code I have in VBA to run it is:
How do I return the value so that I can use it?
Anthony
I am trying to run the following stored procedure from my VBA Access application which returns the next available WO number, but i'm not getting the code quite right.
My SP is:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[p_GetNewWONumber]
(@NextWO INT OUT
)
AS
BEGIN
SET NOCOUNT ON;
SELECT @NextWO = COALESCE(MAX(WONumber), 0) +1
FROM tblWorkOrderHead
RETURN @NextWO
END
This, when run directly in MSSQL server, returns the correct number. The code I have in VBA to run it is:
Public Function GetNewWO()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = "Driver={SQL Server};Server=MYSERVER;Database=MYDATABASE;Trusted_Connection=yes;"
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "p_GetNewWONumber"
cmd.Parameters.Append cmd.CreateParameter("@NextWO", adVarChar, adParamInput, 255, Null)
cmd.Execute
End Function
How do I return the value so that I can use it?
Anthony
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Paul,
To use this:
dim lngNextWONumber as Long
lngNextWONumber = DMax("WONumber", "tblWorkOrderHead") + 1
Would I need to have the table linked or connected to my MS Access database?
To use this:
dim lngNextWONumber as Long
lngNextWONumber = DMax("WONumber", "tblWorkOrderHead") + 1
Would I need to have the table linked or connected to my MS Access database?
ASKER
I've sorted it!
Public Function GetNewWO()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim returnWO As String
cmd.ActiveConnection = "Driver={SQL Server};Server=SERVER;Database=DATABASE;Trusted_Connection=yes;"
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "p_GetNewWONumber"
cmd.Parameters.Append cmd.CreateParameter("@NextWO", adInteger, adParamOutput)
cmd.Execute
returnWO = (cmd.Parameters("@NextWO").Value)
Debug.Print (returnWO)
End Function
Yes, the table would need to be linked.
Another option would be to build a Pass-Through query (which would require an ODBC connection to the SQL database, but the table wouldn't have to be linked):
Select max(WONumber) + 1 as NextWONumber from tblWorkOrderHead
and then use a DLookup against the passthrough query.
Another option would be to build a Pass-Through query (which would require an ODBC connection to the SQL database, but the table wouldn't have to be linked):
Select max(WONumber) + 1 as NextWONumber from tblWorkOrderHead
and then use a DLookup against the passthrough query.
Open in new window