How to create SQL Server-like Procedures in MS Access? 1

[mjquixote]

Is it possible to create queries like SQL Stored procedures in MS Access? For example, can you write conditional statements and declare local variables?

CREATE PROCEDURE procname
(
@username varchar(50),
@password varchar(50)

)
DECLARE @IsEnabled bit

IF NOT EXISTS (SELECT * FROM tbl_AdminUsers WHERE username = @username AND password = @password)
BEGIN
SELECT @IsEnabled = IsEnabled FROM tbl_AdminUsers WHERE username = @username
RETURN
END
ELSE
Etc, etc...

If this is possible can someone provide links to tutorials (i've searched high and low and came up with nothing...probably because it's not possible)

 

[dhookom]

You should be able to do all of this with VBA in a module in Access.

Public Function ProcName(strUserName as String, strPassword as String) 
   Dim booIsEnabled as Boolean
   If DCount("*","tbl_AdminUsers","UserName ='" & strUsername & "' AND Password = '" & strPassword & "'") > 0 Then
      booIsEnabled = DLookup("IsEnabled","tbl_AdminUsers","UserName ='" & strUsername & "'")
   End If
   Procname = booIsEnabled
End Function

You could also use ADO or DAO recordsets in place of the domain aggregate functions.

Duane
Hook'D on Access
MS Access MVP

 

[mjquixote]

Thanks. That's exactly what I was looking for. (I actually already started looking in using modules.) The only problem is I can't find much in the way of help or tutorials.

 

[jrbarnett]

Access has some VBA help, but its not installed as standard. You need to do a custom install of MS Office and specifically choose for the VBA help to be installed.

John