Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-18-2019, 12:44 AM   #1
aziz rasul
Newly Registered User
 
aziz rasul's Avatar
 
Join Date: Jun 2000
Location: Earth
Posts: 1,743
Thanks: 20
Thanked 1 Time in 1 Post
aziz rasul is an unknown quantity at this point
Creating a Stored Procedure in MS Access 2016

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:-

Code:
-- =============================================
-- 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?

__________________
Aziz

Human reproduction process - one spermatozoon, out of 100's of millions, enters the egg and is secured, the fertilized egg becomes a clot and eventually implants itself to the wall of the uterus, referred to as implantation.
Moreover We placed him as a drop (nutfah) in a safe lodging. Then We created the drop (nutfah) into a clinging clot (alaqah).
Qur’an – Surah al-Mu’minun (The Believers) 23:13-14
See also Qur'an 21:30, 21:104 regarding creation and end of the universe (and the creation of a new universe).
aziz rasul is offline   Reply With Quote
Old 10-18-2019, 12:50 AM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,656
Thanks: 98
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Creating a Stored Procedure in MS Access 2016

ADP is deprecated. Don't waste you time developing in it.
Galaxiom is offline   Reply With Quote
Old 10-18-2019, 12:52 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,333
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Creating a Stored Procedure in MS Access 2016

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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-18-2019, 12:53 AM   #4
aziz rasul
Newly Registered User
 
aziz rasul's Avatar
 
Join Date: Jun 2000
Location: Earth
Posts: 1,743
Thanks: 20
Thanked 1 Time in 1 Post
aziz rasul is an unknown quantity at this point
Re: Creating a Stored Procedure in MS Access 2016

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?
__________________
Aziz

Human reproduction process - one spermatozoon, out of 100's of millions, enters the egg and is secured, the fertilized egg becomes a clot and eventually implants itself to the wall of the uterus, referred to as implantation.
Moreover We placed him as a drop (nutfah) in a safe lodging. Then We created the drop (nutfah) into a clinging clot (alaqah).
Qur’an – Surah al-Mu’minun (The Believers) 23:13-14
See also Qur'an 21:30, 21:104 regarding creation and end of the universe (and the creation of a new universe).
aziz rasul is offline   Reply With Quote
Old 10-18-2019, 01:00 AM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,656
Thanks: 98
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Creating a Stored Procedure in MS Access 2016

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.
Galaxiom is offline   Reply With Quote
Old 10-18-2019, 07:57 AM   #6
aziz rasul
Newly Registered User
 
aziz rasul's Avatar
 
Join Date: Jun 2000
Location: Earth
Posts: 1,743
Thanks: 20
Thanked 1 Time in 1 Post
aziz rasul is an unknown quantity at this point
Re: Creating a Stored Procedure in MS Access 2016

I have written the following code:-

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?
__________________
Aziz

Human reproduction process - one spermatozoon, out of 100's of millions, enters the egg and is secured, the fertilized egg becomes a clot and eventually implants itself to the wall of the uterus, referred to as implantation.
Moreover We placed him as a drop (nutfah) in a safe lodging. Then We created the drop (nutfah) into a clinging clot (alaqah).
Qur’an – Surah al-Mu’minun (The Believers) 23:13-14
See also Qur'an 21:30, 21:104 regarding creation and end of the universe (and the creation of a new universe).
aziz rasul is offline   Reply With Quote
Old 10-22-2019, 06:56 AM   #7
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,523
Thanks: 53
Thanked 85 Times in 78 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Re: Creating a Stored Procedure in MS Access 2016

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

More or less something like this:
Code:
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

Guus2005 is offline   Reply With Quote
Old 10-22-2019, 07:03 AM   #8
aziz rasul
Newly Registered User
 
aziz rasul's Avatar
 
Join Date: Jun 2000
Location: Earth
Posts: 1,743
Thanks: 20
Thanked 1 Time in 1 Post
aziz rasul is an unknown quantity at this point
Re: Creating a Stored Procedure in MS Access 2016

Thanks Guus2005, I will try that.

__________________
Aziz

Human reproduction process - one spermatozoon, out of 100's of millions, enters the egg and is secured, the fertilized egg becomes a clot and eventually implants itself to the wall of the uterus, referred to as implantation.
Moreover We placed him as a drop (nutfah) in a safe lodging. Then We created the drop (nutfah) into a clinging clot (alaqah).
Qur’an – Surah al-Mu’minun (The Believers) 23:13-14
See also Qur'an 21:30, 21:104 regarding creation and end of the universe (and the creation of a new universe).
aziz rasul is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating my first stored procedure CoffeeGuru SQL Server 2 07-21-2014 02:36 PM
Call Stored Procedure from Access Amritp Queries 1 08-13-2013 02:37 PM
Help Creating a Stored Procedure in SQL vurna SQL Server 8 01-17-2012 07:06 AM
Execute stored procedure from Access RoddyPiper SQL Server 6 09-16-2010 01:49 PM
What is the best way to run stored procedure from the MS Access accesser2003 General 3 07-14-2008 05:05 AM




All times are GMT -8. The time now is 01:56 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World