?
Solved

Run Stored  Procedure using VBA

Posted on 2003-10-21
4
Medium Priority
?
409 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 6

Accepted Solution

by:
robertjbarker earned 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

770 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