Calling stored procedures from VBA

R

Rik Hess

I hope this is the right place for this request.

I'm converting an Access app to use MS SQL Server. I'd
like to use stored procedures for performance, but can't
seem to be able to call them from a VBA module.

Any help is appreciated.

Thank you,
Rik
 
B

BJ Freeman

when using VBA use an & to append stings
1) when calling a SP with no parms the " goes right after the ST name
StoreProcudureName"
2) when calling a sp with parms a space between the SP and "
StoreProcudureName "
3) when using a string Parm incapusulate it with single quotes with in a
double quote " ' string stuff' ". note don't put space betweek double and
single quotes. did that for clarity.
Here is an VBA code for calling a many parms SP

'Takes the current Address and inputs it into the Datbase
Public Function SP_insertAddress() As Integer
Dim RS As New ADODB.Recordset
Dim validate As Integer
'Stop
If Len(LocalAddress.Address1) = 0 Then LocalAddress.Address1 = " "
If Len(LocalAddress.EmailAddress) = 0 Then LocalAddress.EmailAddress =
" "
If Len(LocalAddress.HomePhone) = 0 Then LocalAddress.HomePhone = " "
If Len(LocalAddress.Country) = 0 Then LocalAddress.Country = "USA"
If Len(LocalAddress.RecordTypeCode) = 0 Then LocalAddress.RecordTypeCode
= "U"
validate = 0
If LocalAddress.Validated = True Then validate = 1
Set RS = CurrentProject.Connection.Execute("SP_InsrtAddress " & _
"""" & Me.Address1 & """" & "," & _
"""" & Trim(Me.Address2) & """" & "," & _
"""" & Trim(Me.City) & """" & "," & _
"""" & Trim(LocalAddress.State) & """" & "," & _
"""" & Trim(LocalAddress.PostalCode) & """" & "," & _
"""" & LocalAddress.EmailAddress & """" & "," & _
"""" & LocalAddress.HomePhone & """" & "," & _
"Default" & "," & _
"Default" & "," & _
"Default" & "," & _
"Default" & "," & _
"Default" & "," & _
"Default" & "," & _
"""" & LocalAddress.Country & """" & "," & _
"Default" & "," & _
"""" & LocalAddress.RecordTypeCode & """" & ",'" & _
validate & "'," & _
"Default")
LocalAddress.AddressID = RS![AddressID]
RS.Close
SP_insertAddress = LocalAddress.AddressID
End Function

and here is the SP that it callse

/*
****************************************************************************
*******
File: SP_InsrtAddress.sql
Name: SP_InsrtAddress
Desc:
this is the address validate routine.
Notes:
1. Error messages used
sysRaiseError 1001 -- Generic import error
Processing Steps:
1. Verify that this address is USA--only address validated.
2. get the zipcode ID for USA addresses
3. set the region ID for non US addresses

Restart:
Restart from the calling procedure. No code modifications required.
Tables Used:
None Call SP in the Address Database.
Parameters:
None
Return values:
= 0 Success
<> 0 Failure
Called By:

Calls:
address..SPLkupCountryCode
address..SP
Change History:
Date Author Description
---------- --------------- -------------------------------------------
---------
01/07/02 BJ Freeman Imported from Address DB
****************************************************************************
*********
*/
Alter Procedure SP_InsrtAddress

@Address1 varchar(255) = Null,
@Address2 varchar(255) = Null,
@City varchar(100) = Null,
@state varchar(2) = Null,
@PostalCode nvarchar(20) = Null,
@EmailAddress nvarchar(50) = Null,
@HomePhone nvarchar(30) = Null,
@WorkPhone nvarchar(30) = Null,
@WorkExtension nvarchar(20) = Null,
@MobilePhone nvarchar(30) = Null,
@FaxNumber nvarchar(30) = Null,
@TaxRate real = 0.0,
@CarrierRoute char(4) = Null,
@Country nvarchar(50) = Null,
@DeliveryPoint int= Null,
@Addresstype char(1)= Null,
@Validated bit =0,
@CheckDigit int= Null,
@AddressID int =1 OutPut

As
SET Nocount on

----------------------------------------------------------------------------
------
-- Declarations
----------------------------------------------------------------------------
------
DECLARE
@Rows INT
,@Err INT
,@ExitStatus INT
,@Msg VARCHAR(255)
,@ErrReturn INT
,@TRUE INT
,@FALSE INT
,@ZipcodeID int
,@RegionID int
,@ProvinceID int
,@AreaCodeID int

----------------------------------------------------------------------------
------
-- Initializations
----------------------------------------------------------------------------
------

SELECT
@ErrReturn = 1001 -- Error ID
,@ExitStatus = 0
,@FALSE = 0
,@TRUE = 1
,@ZipcodeID =1
,@RegionID =1
,@ProvinceID =1
,@AreaCodeID =1

IF EXISTS (SELECT AddressID
FROM dbo.Addresses
WHERE PostalCode = @PostalCode
AND Address2= @Address2)
BEGIN
SELECT AddressID
FROM dbo.Addresses
WHERE PostalCode = @PostalCode
AND Address2= @Address2
RETURN 0
END
/*
if @new = 1
Begin
EXEC SP_LkUPZipCodeID
@PostalCode,
@ZipcodeID OUTPUT
-----------------------------
-- Is this a USA address
--
-----------------------------

IF @ZipcodeID > 0
begin
SELECT @Country = 'Default' --set for usa
end

EXEC SP_LkUpCountries
@Country,
@RegionID OUTPUT

*/
insert into dbo.Addresses
(
Address1,
Address2,
City ,
ProvinceID,
state,
PostalCode,
ZipcodeID,
RegionID,
EmailAddress,
AreaCodeID,
HomePhone,
WorkPhone,
WorkExtension ,
MobilePhone ,
FaxNumber ,
TaxRate ,
[Carrier Route],
Country,
[Delivery Point],
[Address type],
Validated,
[Check Digit],
Activated,
Updated
)
values
(
@Address1,
@Address2,
@City ,
@ProvinceID,
@state,
@PostalCode,
@ZipcodeID,
@RegionID,
@EmailAddress,
@AreaCodeID,
@HomePhone,
@WorkPhone,
@WorkExtension ,
@MobilePhone ,
@FaxNumber ,
@TaxRate ,
@CarrierRoute,
@Country,
@DeliveryPoint,
@Addresstype,
@Validated,
@CheckDigit,
getdATE(),
getdATE()
)

SELECT @AddressId = @@IDENTITY
Select AddressId=@AddressId
RETURN 0
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top