Using SQL in Access VBA

As you progress with using VBA coding, you will start to see the usefulness of using SQL code to run operations on your background tables. When I initially started writing VBA I would use the data in the fields on the current form, which sometimes led to some rather annoying workarounds to make sure all the data I wanted was there. Since learning to use SQL within VBA I have written much more efficient code, and made my life much easier.

The idea is to give Access a SQL string, which it then runs, and either gives you back some data or it performs another operation such as delete or update. Here I will give you a short example to demonstrate the basics of getting this set up. We will look at a function that checks a table to find out if the current user has been given administrative rights. Here is s snapshot of the table:

Table of users

As you can see, the column AdminRights is ticked for two users. We want a function that will determine whether a user ID has this ticked in the table.  You could use this for a variety of reasons – like only showing an admin screen to those users.  The function we will be looking at uses one other – a function  called fOSUserName that identifies the user ID from the PC – you can see the details of that in this post.

Here is the code we will review:

Function CheckAdminRights() As Boolean

 Dim strSQL As String
 Dim rs As Recordset
 Dim StaffID As String
 
 StaffID = fOSUserName
 strSQL = "SELECT * FROM Tbl_Users WHERE StaffID = '" & StaffID & "';"
 Set rs = CurrentDb.OpenRecordset(strSQL)
 rs.MoveFirst
 
 If rs("AdminRights") = True Then
     CheckAdminRights = True
 Else
     CheckAdminRights = False
 End If

End Function

First we set up three variables – a string to code the SQL code we will be constructing, a string to hold the user ID, and a third that is of type Recordset. This is a variable that is essentially a set of records from a table or tables. This can be looked at, searched, and even passed on to other functions.

After assigning the StaffID variable to the PC user ID with the fOSUserName function, we build the SQL string. The full statement for a user ID of 66011254 would be:

SELECT * FROM Tbl_Users WHERE StaffID = ‘66011254’;

However, we want to use this for any staff ID, so we replace the ID with the variable we have set up for that purpose (note: always watch the quotations marks when concatenating SQL strings – just one in the wrong place and it won’t work).

We then assign the recordset variable rs by running this SQL statement in the current database. The table of users has a primary key of StaffID, so in this instance we know that only one record should be returned for this recordset – you will need to be careful with this as this particular function will only check the first match it finds as we just use the MoveFirst operation.

The last bit is a check on the data in a particular field in the recordset we just created – we check whether or not the AdminRights field is true or false. This value is then assigned as the output Boolean for the function.

Once written, you can then call this function anywhere in the database:

If CheckAdminRights = True Then
    Me.AdminButton.Visible = True
End If

Leave a Reply

Your email address will not be published. Required fields are marked *