Creating a Stored Procedure in MS Access 2016

[aziz rasul]

I am trying to convert a MS access 2002 adp file to a accdb MS Access 2016 file.

In the adp file I have a stored procedure, which when opened has the following:-

-- =============================================
-- Author:		XXXXX XXX
-- Create date: 17th August 2011
-- Description:	Gets the user details.
--              Used on frmSplashScreen
-- =============================================
ALTER PROCEDURE dbo.GetUserDetails 

	@UserName nvarchar(50)
	
AS BEGIN

SET NOCOUNT ON

DECLARE
	@UserNameLOCAL nvarchar(50) = @UserName

SELECT
	FullName = A.Title + ' ' + A.Forename + ' ' + A.Surname,
	A.PeopleId,
	A.UserName,
	A.Email,
	A.UserReason,
	A.SeeAnEnhancements,
	A.UserEnabled,
	A.Site,
	A.AdminGroup
FROM
	dbo.tblPeople A
WHERE
	A.UserName = @UserNameLOCAL
	AND
	A.UserEnabled = 1
	
END

How do I create this stored procedure in the accdb file?

 

[Galaxiom]

ADP is deprecated. Don't waste you time developing in it.

 

[CJ_London]

access does not have stored procedures - you create them in the backend (sql server).

Not tried it but you might be able to use a passthrough query in access to create the procedure in sql server. Note that the code is altering an existing stored procedure, not creating one

 

[aziz rasul]

I am trying to replicate the stored procedure in the adp into the accdb file i.e. in MS Access 2016. I assume that you can still create stored procedures in MS Access 2016?

 

[Galaxiom]

Create a QueryDef and assign the the SQL property. You also need to translate the SQL syntax to Access using the ampersand concatenator instead of the +. Otherwise the nulls will propogate.

Username can be implemented as a Parameter.

Probably best just rebuild in the query editor.

 

[aziz rasul]

I have written the following code:-

    Dim cmd As New ADODB.Command

    Set cmd = New ADODB.Command
    
    cmd.ActiveConnection = "Provider=sqloledb;Data Source=SQL2008CLS\LEW;Initial Catalog=MedicalLeave;Trusted_Connection=Yes;"
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "GetUserDetails"
    cmd.Parameters.Append cmd.CreateParameter("@azrl", adVarChar, adParamInput, 255, "azrl")
    cmd.Execute

It doesn't error, so I'm hoping it works. Assumming it works, how do I retrieve the data?

 

[Guus2005]

Just connect tblPeople to your ACCDB file and create a function GetUserDetails which retrieves the data you require.

More or less something like this:

Public Function GetUserDetails(strUsername As String) As Recordset

    Dim db As Database
    Dim rst As Recordset
    Dim strsql As String
    
    strsql = "SELECT A.Title & ' ' + A.Forename + ' ' + A.Surname as Fullname, A.* FROM tblPeople A WHERE A.UserName = " & strUsername & " AND A.UserEnabled = 1"
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strsql)
    
    If Not rst.EOF Then
        Set GetUserDetails = rst
    Else
        Set GetUserDetails = Nothing
    End If
    
End Function

HTH

 

[aziz rasul]

Thanks Guus2005, I will try that.