How to Run a Pass through Query using Access 2013

I am using Access 2013 to develop a front end to a SQL Server 2012 database.    I have created a login screen.  The user enters their user name and password.  I want to pass them as arguments to a UDF function in the SQL Server database that I have written which returns an integer value.  From the code below, you can see I have constructed the query I want to run as a string.  What methods, or how do I get it to run this query.  The query returns an integer value which I want to grab and store in USER_ID_Returned

Private Sub btnSignIn_Click()
Dim UserNameEntered As String
Dim PasswordEntered As String
Dim USER_ID_Returned As Integer
Dim SQLQuery As String

UserNameEntered = Me.txtUserName.Value
PasswordEntered = Me.TxtPassword.Value

SQLQuery = "SELECT [dbo].[ValidatePassword] ( '" & UserNameEntered & "', '" & PasswordEntered & "')"
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
You just create a Pass through query in the designer - can be any valid T-SQL string, and set its connection properties (you can also do that in code.

Then, using DAO, set a QueryDef = to your pass through query, set the sql = to your SQLQuery, then use it like any other Access query.


Sing out if you need sample code

Kelvin SparksCommented:
Once you create your SQL Query, make sure that you test your resolved string directly against SQL Server. Error handling in Access of SQL Server can be a bit hit and miss.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

I would recommend to use a stored procedure for this purpose. That gives you the possibility to work with the parameters for example to save the username and login date/time in a login history which would not be possible in a UDF. A stored procedure can also return an integer value as return code using RETURN.

Instead of creating a pass through query which would not give you any advantage for this purpose I would use ADO to execute the UDF or SP so you don't need any DAO object here.

Another thing is that sending username and password as the entered string is of course a security problem. A simple SQL Server Profiler session could be enough to grab all username/password combinations. You should at least encode your password as an MD5 or SHA1 hash on the server (so that even no admin can read out the passwords from the server opening the user table) and then encode the entered password in the frontend on the same way. That could be read out, too, but at least the password itself is protected. Reason is that very often users takes the same passwords for many systems and this can open a big security hole for other systems than yours, i.e. Windows itself.

Next is that you also need to make sure that upper/lower case is compared in the right way, as comparing "Password" and "PASSWORD" and "PaSsWoRd" would be the same for normal "=" comparison with default settings. That's the case in SQL Server and also VBA. You need to use a binary comparison.

Last but not least: If possible, better use the SQL Server integrated authentication methods because they are proved over a long time of history and always more secure than any method you will develop in the next months or years. The preferred method is of course Windows authentication as the domain will secure the login - and the user don't need to create/know an additinal user/password combination, he also don't need to login but simply start the database application. SQL Server gives you all methods of securing any object which needs a lot of programming if you do that on your own with user and object and permission tables and so on.
If Windows authentication cannot be used for any reason, SQL Server offers you an SQL Server login which is handled by your SQL Server only. This can also be secured with an encrypted login (which is automatically the case in newer SQL Server versions as far as I know).


Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

ste5anSenior DeveloperCommented:
Christian is right.

Use the built-in mechanisms. Anything else will be a security issue by design.

Use Windows Authentication or SQL Server Authentication. Consider using application roles to restrict the user access to  SQL Server.
cipriano555Author Commented:
hmmmmm...  So this is a proof of concept prototype and I'm going to completely ignore security, first go around.  I know there's ADO and DAO. I'm only going to focus on one of them for now, and since ADO is newer and it looks like the way to go if your data is remotely located on SQL Server, I'd like to use ADO.   The password details don't matter, I'm really trying to demonstrate if the user is in one class, they will see one set of data, and if in the other class, they'll see different data.  All security and authentication is peripheral at this point.

I'd like to run this query, SELECT [dbo].[ValidatePassword]('username1', 'password123') and get the return value and do something based on it.
Kelvin SparksCommented:
I use a function to return a value from a SQL Function

Use something like

Public Function checkStatus(username1, pasword123) As String

Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.ConnectionString = tConnect

checkStatus = cnn.Execute("SELECT dbo.ValidatePassword('" & username1& "', '" & pasword123& "')")(0)
Set cnn = Nothing

End Function

The tconnect is your connection string to SQL Server

You can then call that function from anywhere in Access and it'll give you your return value.

ste5anSenior DeveloperCommented:
I would stick to DAO (ACEDAO), cause it's the default library.
cipriano555Author Commented:
Thanks for all your help.  I got it working .  Basically, this is what I did:  

Private Sub btnSignIn_Click()

Dim SQLQuery As String
Dim ReturnString As String

Dim adRs As ADODB.Recordset
Dim adCmd As ADODB.Command

Set adRs = New ADODB.Recordset
Set adCmd = New ADODB.Command

SQLQuery = "SELECT [Dbo].[ValidatePassword] ( '" & Me.txtUserName.Value & "', '" & Me.TxtPassword.Value & "')"
MsgBox (SQLQuery)

adCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; Initial Catalog=DATA_CONCEPTS;Data Source=SRVR7LF\MSSQL2012;"
adCmd.CommandText = SQLQuery

Set adRs = adCmd.Execute
ReturnString = adRs.GetString
MsgBox (ReturnString)
Debug.Print ReturnString

Set adRs = Nothing
Set adCmd = Nothing

End Sub
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.