Access Login - SQL Stored Proc

[WillM]

Good morning,
I am not sure if this needs to go here or over in the SQL forum.

I have the following Stored Procedure:

USE TestDB
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[getLogin]
@StaffName nvarchar(25), @Password nvarchar(25)

AS
BEGIN
SET NOCOUNT on;

SELECT * FROM dbo.vw_Staff where Staff=@StaffName and UserPass=@Password
End

I have an Access form with 2 unbound text boxes: StaffName, Password

What I would like to do is, trying to avoid SQL injections and increase security in the database, pass the login information from Access to SQL as securely as possible.

I cannot figure out the VBA to pass the parameter back to SQL and have the SP compare and return the information to me.

I am at a loss of what to do next.

This is what I have in Access so far:

Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim StaffName As String
Dim Password As String

StaffName = Me.txtLogin
Password = Me.txtPassword

qdf.Connect = "ODBC;DSN=DBConn;Trusted_Connection=Yes;DATABASE=TestDB"

I am getting an error message:
"Run-Time error '91':
Object variable or With block variable not set


Thank you all for any help you can give.

 

[BigHappyDaddy]

qdf needs to be set first.
Try something like:
<Code>set qdf = currentdb.createquerydef ("tempquery")
<\CODE>

Sent from my SM-G900P using Tapatalk

 

[WillM]

Of course...sometimes it is the simple things. Thank you.

Still stumped on how to pass the variables back to SQL as parameters to the stored proc.

 

[pbaldy]

You'd set the SQL of the pass through query to:

"EXEC getLogin '" & StaffName & "', '" & Password & "'"

 

[Minty]

Your Stored / temp query will be something like

EXEC dbo.YourSProc @YourParameter = 12345

Use the qdf to change the query as you need to to avoid hard coded values. Make sure you have t set to return records assuming that it does.

If it has string parameters then put them in single quotes.