MS Access 2007, checking current user against a table

We have a simple access database, and woul开发者_StackOverflow社区d like a button on a form to only be available to select members of staff. (The button has an event tied to it). I'd like to store the usernames of the staff allowed to click the button in a separate table.

What I'd like to do, is perform a simple query to see if the username exists in the table, and set the enabled state of the button depending upon the outcome.

My background is C# and SQL Server, but VBA and access are new to me, and I think I'm struggling with the quirks of this environment.

I've got the username of the logged on user in a string fOSUserName via a call to GetUserNameA in advapi32.dll, but I'm struggling with the simplest of queries to determine if the username exists in the table.

Dim strSQL As String
Dim intResult As Integer
Dim db As DAO.Database
Dim rs As Recordset    
Set db = CurrentDb    
strSQL = "SELECT COUNT(*) FROM [USERS] WHERE [USERS].[NAME] = '" & _
    fOSUsername & "'"        
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If Not rs.EOF Then
    intResult = rs.Fields(0)
Else
    intResult = 0
End If
rs.Close
db.Close

This fails on db.OpenRecordset giving me the error

Run-time error '3061':

Too few parameters. Expected 1.

Can anyone offer some pointers?

When you continue a line in VBA, you need a space between before the line continuation character ("_"). So instead of this:

strSQL = "SELECT COUNT(*) FROM [USERS] WHERE [USERS].[NAME] = '" &_
    fOSUsername & "'" 

Use this:

strSQL = "SELECT COUNT(*) FROM [USERS] WHERE [USERS].[NAME] = '" & _
    fOSUsername & "'" 

However, as @Igor Turman pointed out, the lack of a space before the underline character should trigger a compile error. So I'm unsure what's going on but suggest you fix it anyway to avoid confusion.

I'll suggest that rather than opening a recordset, and then reading a value from that recordset, this could be handled simply with the DCount() function.

Dim strCriteria As String
strCriteria = "[USERS].[NAME] = '" & fOSUsername & "'"
Debug.Print "strCriteria: '" & strCriteria & "'"
If DCount("*", "USERS", strCriteria) = 0 Then
    Debug.Print "not found"
Else
    Debug.Print "found"
End IF

If your missing parameter error is because USERS is a query rather than a table, you can ask DCount() to use a table instead. Or fix the query.

Sounds like your [USERS] object is not a table but Query (with parameter). Also, if you had a syntax error like '&_'(invalid) as opposed to '& _'(valid), your database would not compile. So, if table vs query is your case, please use the following:

...
Dim rs As Recordset
Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("Users")
qdf.Parameters("UserNameParameter") = fOSUsername
Set rs = qdf.OpenRecordset
...

I'm not totally familiar with the way you are using it, but I've always done it this way:

Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[your access db file path and name];Persist Security Info=False"

sSQL = "SELECT COUNT(*) FROM [USERS] WHERE [USERS].[NAME] = '" &_ 
    fOSUsername & "'" 

Set rs = New ADODB.Recordset
rs.Open sSQL, cn

If Not rs.EOF Then 
    intResult = rs.Fields(0) 
Else 
    intResult = 0 


rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing