Doozy
asked on
Run Stored Procedure using VBA
I have a sp that creates a new table, with the new table's name to be the value of a field on the form
ALTER PROCEDURE procNewPayee
(@Payee_id as char(18))
AS
SET NOCOUNT ON
DECLARE @table_name char(20)
DECLARE @sql_string NVARCHAR(500)
SET @table_name = CAST(@Payee_id AS varchar(18))
SET @sql_string = 'CREATE TABLE ' + @table_name + '(Catalog_ID char(24),Cur_Code char(3), Sale_Qty int, Sale_Retail float(8),Sale_Period int ,Contract_Royalty float(2),Royalty_Payable float(2))'
EXEC sp_executesql @sql_string
I try to run it from my form
Dim PayeeID As String
PayeeID = Me.Payee_ID
DoCmd.RunSQL "EXEC procNewPayee 'WHERE @Payee_ID = PayeeID'"
I get the error
Incorrect syntax near the keyword 'WHERE'
(1) How can I fix this?
(2) How can I prevent this running if a table with that name already exists?
Thanks, Doozy
ALTER PROCEDURE procNewPayee
(@Payee_id as char(18))
AS
SET NOCOUNT ON
DECLARE @table_name char(20)
DECLARE @sql_string NVARCHAR(500)
SET @table_name = CAST(@Payee_id AS varchar(18))
SET @sql_string = 'CREATE TABLE ' + @table_name + '(Catalog_ID char(24),Cur_Code char(3), Sale_Qty int, Sale_Retail float(8),Sale_Period int ,Contract_Royalty float(2),Royalty_Payable float(2))'
EXEC sp_executesql @sql_string
I try to run it from my form
Dim PayeeID As String
PayeeID = Me.Payee_ID
DoCmd.RunSQL "EXEC procNewPayee 'WHERE @Payee_ID = PayeeID'"
I get the error
Incorrect syntax near the keyword 'WHERE'
(1) How can I fix this?
(2) How can I prevent this running if a table with that name already exists?
Thanks, Doozy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note also that I have never used "adChar" to pass a string. I always use "adVarChar" and write my stored procedures accordingly.
ASKER
Great stuff - works a treat. Thanks
.Parameters.Append .CreateParameter("(@Payee_