Help querying a table using VBA

[Guest]

Hi,

I have an Access 2000 db with a user table showing the various user levels
(such as NormalUser, SeniorUser, Security, Admin, and Designer).

At startup I use the Win32 API to get the NT Username. Then I want to
compare the NT Username with the records in the table to find out the user
access level - then I can show the correct menu according to the users
permissions.

Here is the SQL Query that I need to run in VBA:

"SELECT userLevel from Users where NTUserName = '" & sUser & "'"

and the database is the CurrentDB (called "Project Eagle.mdb")

How do I do that please?

(Normally in MS Excel VBA or VB 6 I would use ADO, and have limited
knowledge of Access or DAO).

I tried to use the code in the Help (Northwind) for this, but I got an
'Unrecognized Database format' error.

Here is the code I tried to use:
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsEagle = wrkJet.OpenDatabase("Project Eagle.mdb")

' Set rstTemp = CurrentDb.OpenRecordset("SELECT userLevel from Users
where ' NTUserName = '" & sUser & "'", dbOpenDynaset, dbReadOnly)

Set rstTemp = dbsEagle.OpenRecordset( _
"SELECT userLevel Users where NTUserName = '" & sUser & "'",
dbOpenDynaset, dbReadOnly)

intUserLevel = rstTemp!userLevel

getUserLevel = intUserLevel
<<< end code <<<

Unfortunately it fails on the Set dbsEagle = wrkJet.OpenDatabase line with
'unrecognized database format' error.

Is there a better or simpler way to do what I want in VBA just to get the
user level from the table Users?

thanks for any help

Philip

 

[Douglas J. Steele]

If all you need is a single field for a single user, don't bother with a
recordset: use DLookup instead.

DLookup("[userLevel]", "[Users]", "[NTUserName] = '" & sUser & "'")

Remember that since there's a possiblity that what's returned may be Null,
you cannot assign the value to a String variable.

 

[Guest]

I would suggest using the DLookup function rather than establishing a
recordset to retrieve one piece of data:
userLevel from Users where NTUserName = '" & sUser & "'"

varUserLevel = DLookup("[userlevel]", "Users", "[NTUserName] = '" &
sUser & "'")
If IsNull(varUserLevel) Then
MsgBox "User Not Found"
End If

 

[Guest]

Hi,

Thanks for your answer... so much more elegant than what I did (see below):

Dim oConn As ADODB.Connection
Dim oRec As New ADODB.Recordset

Dim intUserLevel As Integer

Set oConn = Application.CurrentProject.Connection

oRec.Open "SELECT userLevel from Users where NTUserName = '" & sUser &
"'", oConn, adOpenForwardOnly, adLockReadOnly

intUserLevel = oRec.Fields("userLevel")

.... thanks again!

Philip

If all you need is a single field for a single user, don't bother with a
recordset: use DLookup instead.

DLookup("[userLevel]", "[Users]", "[NTUserName] = '" & sUser & "'")

Remember that since there's a possiblity that what's returned may be Null,
you cannot assign the value to a String variable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

I have an Access 2000 db with a user table showing the various user levels
(such as NormalUser, SeniorUser, Security, Admin, and Designer).

At startup I use the Win32 API to get the NT Username. Then I want to
compare the NT Username with the records in the table to find out the user
access level - then I can show the correct menu according to the users
permissions.

Here is the SQL Query that I need to run in VBA:

"SELECT userLevel from Users where NTUserName = '" & sUser & "'"

and the database is the CurrentDB (called "Project Eagle.mdb")

How do I do that please?

(Normally in MS Excel VBA or VB 6 I would use ADO, and have limited
knowledge of Access or DAO).

I tried to use the code in the Help (Northwind) for this, but I got an
'Unrecognized Database format' error.

Here is the code I tried to use:

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsEagle = wrkJet.OpenDatabase("Project Eagle.mdb")

' Set rstTemp = CurrentDb.OpenRecordset("SELECT userLevel from Users
where ' NTUserName = '" & sUser & "'", dbOpenDynaset, dbReadOnly)

Set rstTemp = dbsEagle.OpenRecordset( _
"SELECT userLevel Users where NTUserName = '" & sUser & "'",
dbOpenDynaset, dbReadOnly)

intUserLevel = rstTemp!userLevel

getUserLevel = intUserLevel
<<< end code <<<

Unfortunately it fails on the Set dbsEagle = wrkJet.OpenDatabase line with
'unrecognized database format' error.

Is there a better or simpler way to do what I want in VBA just to get the
user level from the table Users?

thanks for any help

Philip

 

[Jason Lepack]

I created this table:
nt_users:
nt_user_name - text
user_level - text

I then created this query:
get_user_level:
SELECT
user_level
FROM
nt_users
WHERE
nt_user_name=[user_name]

Then I created this function:
Public Function get_user_level() As String
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set qdf = CurrentDb.QueryDefs("get_user_level")
qdf.Parameters("user_name") = Environ("username")

Set rs = qdf.OpenRecordset
get_user_level = rs.Fields("user_level")

rs.Close
Set rs = Nothing
Set qdf = Nothing
End Function

Now, the function get_user_level() will always return the value in
user_level for whichever nt_user is currently logged in.

Cheers,
Jason Lepack