Solved

Run Stored  Procedure using VBA

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

Suggested Solutions

Title # Comments Views Activity
Help with SQL Server Stoplist 2 20
SQL Server - Slabs 9 38
Need SQL that flips  value 5 10
TSQL - How to perform an IF… SELECT? 2 0
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…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

895 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

17 Experts available now in Live!

Get 1:1 Help Now