Solved

Run Stored  Procedure using VBA

Posted on 2003-10-21
4
402 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

839 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