Guys i need help with returning a single value from my sql stored procedure i want to call the sp with vba and return a single value all the code is below any help would be great.
'Access ADODB CODE
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQLServer As String
Dim Catalog As String
Dim SQLString As String
Dim SQLInvNo As String
Dim PURCHASEORDERNO As Integer
PURCHASEORDERNO = Me.PURCHASEORDERNO
SQLServer = "Gretzky\RNYSQLAccess"
Catalog = "SQLcafe2010"
SQLString = "Exec sp_Suppliername " & PURCHASEORDERNO
'Create a new ADO Connection object
Set cn = New ADODB.Connection
'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = SQLServer
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = Catalog
.Open
End With
'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = SQLString
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
__________________________
SQL SP
USE [SQLCAFE2010]
GO
/****** Object: StoredProcedure [dbo].[sp_Deltacurrentid] Script Date: 05/07/2013 07:49:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[sp_Suppliername]
@PoNumber int
As
Begin
declare @SupplierName as nvarchar (100)
set @suppliername = (SELECT Suppliers.[SUPPLIER NAME]
FROM PurchaseOrders INNER JOIN
Suppliers ON PurchaseOrders.SUPPLIERID = Suppliers.SUPPLIERID
WHERE (PurchaseOrders.[PURCHASE ORDER NO] = @PoNumber)
)
return @PoNumber
end
'Access ADODB CODE
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQLServer As String
Dim Catalog As String
Dim SQLString As String
Dim SQLInvNo As String
Dim PURCHASEORDERNO As Integer
PURCHASEORDERNO = Me.PURCHASEORDERNO
SQLServer = "Gretzky\RNYSQLAccess"
Catalog = "SQLcafe2010"
SQLString = "Exec sp_Suppliername " & PURCHASEORDERNO
'Create a new ADO Connection object
Set cn = New ADODB.Connection
'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = SQLServer
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = Catalog
.Open
End With
'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = SQLString
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
__________________________
SQL SP
USE [SQLCAFE2010]
GO
/****** Object: StoredProcedure [dbo].[sp_Deltacurrentid] Script Date: 05/07/2013 07:49:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[sp_Suppliername]
@PoNumber int
As
Begin
declare @SupplierName as nvarchar (100)
set @suppliername = (SELECT Suppliers.[SUPPLIER NAME]
FROM PurchaseOrders INNER JOIN
Suppliers ON PurchaseOrders.SUPPLIERID = Suppliers.SUPPLIERID
WHERE (PurchaseOrders.[PURCHASE ORDER NO] = @PoNumber)
)
return @PoNumber
end