By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,167 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

Using SQL in doe

P: n/a
HI All,
As you can tell by the post, I am still in the early stages of learning
VB so please bear with me. My question is, can you use the results of a SQL
as a variable in code? Something like....

Dim strSQL As String
strSQL = "SELECT DISTINCT DLookUp('[pwd]'," & _
"'[accees_ids]','[userid] = forms!access_login!userinput')" & _
"from accees_ids WITH OWNERACCESS OPTION;"
If strSQL = Me.userinput.Value Then.........

Many thanks and sorry if this is a stupid question,

Mark
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Mark" <ma*********@ntlworld.com> wrote in message
news:y1**************@newsfe3-win.ntli.net...
HI All,
As you can tell by the post, I am still in the early stages of learning VB so please bear with me. My question is, can you use the results of a SQL as a variable in code? Something like....

Dim strSQL As String
strSQL = "SELECT DISTINCT DLookUp('[pwd]'," & _
"'[accees_ids]','[userid] = forms!access_login!userinput')" & _
"from accees_ids WITH OWNERACCESS OPTION;"
If strSQL = Me.userinput.Value Then.........

Many thanks and sorry if this is a stupid question,

Mark

In very general terms, yes, you can do this. But there are a number of
things wrong here - the sql statement looks a bit wild and the WITH
OWNERACCESS OPTION will not have any effect at all unless it is used with a
saved query, and is only used when you have implemented Access user-level
security. Which begs the question - what are you trying here - some sort of
login form - and are you using user-level security?

Nov 13 '05 #2

P: n/a
"Mark" <ma*********@ntlworld.com> wrote in message news:<y1**************@newsfe3-win.ntli.net>...
HI All,
As you can tell by the post, I am still in the early stages of learning
VB so please bear with me. My question is, can you use the results of a SQL
as a variable in code? Something like....

Dim strSQL As String
strSQL = "SELECT DISTINCT DLookUp('[pwd]'," & _
"'[accees_ids]','[userid] = forms!access_login!userinput')" & _
"from accees_ids WITH OWNERACCESS OPTION;"
If strSQL = Me.userinput.Value Then.........

Many thanks and sorry if this is a stupid question,

Mark


Nope. you have to open a recordset based on the SQL statement and play
with recordsets.
Nov 13 '05 #3

P: n/a
Right,
What I have done with the help of previous posts is using Access
security, it create 2 user accounts. One Admin and another general user. The
general user account has no read permissions so all the query's are set to
run with owner access option.

The problem I have is that I have created my own security on top of
Access's. When the DB is opened, a login form is opened. Because the users
have no read permissions, I need to access info in the tables to validate
the user ID entered. I received a reply from a previous post which gave a
function combined with a stored query which validated the user. I managed to
adapt this to validate the password also which is fine.

I have other code on the user ID & password input which checks to see if the
password is out of date and if the password = user ID (first time login).
This is my problem as I can't figure out how to adapt the code I was given
to perform the other checks.

The function I'm using is:

Function ValidUserIDDAO(strUserID As String) As Boolean
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef
Dim loRst As DAO.Recordset

Set loDB = CurrentDb
Set loQdf = loDB.QueryDefs("qUserID")
With loQdf
.Parameters(0) = strUserID
Set loRst = .OpenRecordset
End With

With loRst
ValidUserIDDAO = Not (.EOF And .BOF)
.Close
End With
Set loRst = Nothing
Set loQdf = Nothing
Set loDB = Nothing
End Function

and the SQL is:

PARAMETERS TestUserID Text ( 255 );
SELECT accees_ids.UserID
FROM accees_ids
WHERE (((accees_ids.UserID)=[TestUserID]))
WITH OWNERACCESS OPTION;

I then have the code set on the form to something like:

If not ValidUserDAO(Me.userinput.Value) Then ...

Being new to this, I don't know and have not been able to find out how to
use the results of a stred query as a variable.

Any help would be much appreciated.

Mark

"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:ci**********@hercules.btinternet.com...
"Mark" <ma*********@ntlworld.com> wrote in message
news:y1**************@newsfe3-win.ntli.net...
HI All,
As you can tell by the post, I am still in the early stages of

learning
VB so please bear with me. My question is, can you use the results of a

SQL
as a variable in code? Something like....

Dim strSQL As String
strSQL = "SELECT DISTINCT DLookUp('[pwd]'," & _
"'[accees_ids]','[userid] = forms!access_login!userinput')" & _
"from accees_ids WITH OWNERACCESS OPTION;"
If strSQL = Me.userinput.Value Then.........

Many thanks and sorry if this is a stupid question,

Mark

In very general terms, yes, you can do this. But there are a number of
things wrong here - the sql statement looks a bit wild and the WITH
OWNERACCESS OPTION will not have any effect at all unless it is used with
a
saved query, and is only used when you have implemented Access user-level
security. Which begs the question - what are you trying here - some sort
of
login form - and are you using user-level security?


Nov 13 '05 #4

P: n/a
> The function I'm using is:

Function ValidUserIDDAO(strUserID As String) As Boolean
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef
Dim loRst As DAO.Recordset

Set loDB = CurrentDb
Set loQdf = loDB.QueryDefs("qUserID")
With loQdf
.Parameters(0) = strUserID
Set loRst = .OpenRecordset
End With

With loRst
ValidUserIDDAO = Not (.EOF And .BOF)
.Close
End With
Set loRst = Nothing
Set loQdf = Nothing
Set loDB = Nothing
End Function

and the SQL is:

PARAMETERS TestUserID Text ( 255 );
SELECT accees_ids.UserID
FROM accees_ids
WHERE (((accees_ids.UserID)=[TestUserID]))
WITH OWNERACCESS OPTION;

I then have the code set on the form to something like:

If not ValidUserDAO(Me.userinput.Value) Then ...

Being new to this, I don't know and have not been able to find out how to
use the results of a stred query as a variable.


You can't directly use the results of a stored query in a variable.
You *can*, however, open a recordset based on a query and then process
the individual records. Crack open the help file and read up on
Recordset.
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.