Solved

Run Stored  Procedure using VBA

Posted on 2003-10-21
4
394 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Doozy
  • 3
4 Comments
 
LVL 6

Accepted Solution

by:
robertjbarker earned 250 total points
ID: 9596440

1) It looks to me like you are executing the following on the SQL Server:

     EXEC procNewPayee 'WHERE @Payee_ID = PayeeID'

what happens, I think, is that your parmeter, being longer than 18 characters is truncated and within the stored procedure the @payee_id parameter is set to 'WHERE @Payee_ID ='.  So, @sql_string ends up being:

CREATE TABLE WHERE @Payee_ID = (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))

I think what you want is more like:

DoCmd.RunSQL "EXEC procNewPayee '" & PayeeID &"'"

But, I don't know if RunSQL is really supposed to be used in this way.  Another way is:

  Dim cmdprocNewPayee As New ADODB.Command
  With cmdprocNewPayee
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText = "procNewPayee"
    .Parameters.Append .CreateParameter("(@Payee_id", adChar, adParamInput, 18, PayeeID)
    .Execute , , dExecuteNoRecords
  End With

2)  You could test to see if the table exists before creating it:

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))

if not exists
 (select * from dbo.sysobjects where id = object_id(cast(rtrim(ltrim(@table_name)) as nvarchar)) and
  OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
  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
end
go



0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 9596450
Note that in the above there is a space after ".Parameters.Append":

.Parameters.Append .CreateParameter("(@Payee_id", adChar, adParamInput, 18, PayeeID)
0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 9596469
Note also that I have never used "adChar" to pass a string.  I always use "adVarChar" and write my stored procedures accordingly.
0
 

Author Comment

by:Doozy
ID: 9603702
Great stuff - works a treat. Thanks
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now