Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Run Stored  Procedure using VBA

Posted on 2003-10-21
4
Medium Priority
?
411 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

Independent Software Vendors: 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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

618 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