RustProof Labs: blogging for education (logo)
My book Mastering PostGIS and OpenStreetMap is available!

Connecting SQL and MS Access - Part 3

By Ryan Lambert -- Published February 07, 2014

So far in this series we created a basic database in SQL Server 2012 Express with a few tables, views and a stored procedure. Then in the second part we created the shell of our MS Access front-end, created a DSN file and connected two views on the SQL Server to Access. In this third (and final!) post of the series we will create a form in MS Access that allows a user to create a new person record and assign them to a department using the stored procedure we developed in the first post. As with the prior posts, I'm assuming a level of familiarity with the systems being discussed, such as SQL Server and MS Access.

Start At the Beginning

I think most people get overly concerned with thinking about viewing the data. Yes, that's important. The reason to collect data is to retrieve it for one reason or another, otherwise what's the point? I've fallen victim to this thinking as well, and I believe in the overall outcome of the project it either costs time, features, or quality. First handle data entry... after all if nothing goes in, nothing comes out.

Create a Form

I create forms using the "Form Design" button under the "Create" ribbon in Access. There are other ways to approach it, but this is how I do it. The first thing I do is re-size the form, I typically start with about 5" x 6" to start with and adjust from there. Resizing forms in design view is a pretty typical "Click and Drag" type of operation.

Setting Form Properties

With our new form open, we need to set some initial properties. If the top- left corner where the rulers intersect has a black box in it (like in the screenshot below) that means the form is selected. If said intersection is just grey then click it! This can also be confirmed by the drop-down menu at the top of the Property Sheet view.

There are two form properties I always change right away. I set "Pop Up" = "Yes" and "Auto Center" = "Yes". In Access I prefer having the forms not display in tabs, I just like the feel of the popup forms and reports better. The Auto Center is essential if anyone who might ever think about using this database has more than one monitor. If this setting is missed, the common "bug" you will have to fix is "This button doesn't work. I keep clicking it but it doesn't do anything." I have never heard those words when changing the Auto Center property to Yes didn't fix it. Essentially what happens if you leave it set to No is the form displays on a non-existent monitor for some users, depending on their settings.

Adding Form Controls

Before adding the controls we need to figure out what data we're collecting. Looking at the definition for the stored procedure we created in Part 1, dbo.addNewPerson, there are 6 parameters it can accept:

@nameFirst VARCHAR(100),
@nameMI VARCHAR(25) = NULL,
@nameLast VARCHAR(100),
@dob DATE = NULL,
@sex CHAR(1) = NULL,
@deptID INT = NULL

I added the fields the match up with what the stored procedure needs. The name of each control matches the name the stored procedure expects which helps keep everything consistent and ease development/maintenance. You'll notice I added 6 controls and two buttons, but the Department control (a List Box) is empty and needs to be populated from the view dbo.vDept we linked to in the previous post.

Adding VBA To Add Awesomeness

To reduce maintenance in forms, I typically populate all dynamic form elements from a helper, or lookup, table in the database. A great example of this is how dbo.vDept is setup. It will essentially give an un-duplicated list of all departments in the table dbo.dept.

To get started open the addPerson form in "Design View", ensure the form itself is active (see instructions above), and open the property sheet to the Event tab. Click inside the "On Load" box inside the Property Sheet and and click on the Ellipses (...) for the "On Load" option. In the dialog that opens, select "Code Builder" to create a new method in the VBA editor.

VBA Editor

In the future, you can use Alt+F11 to open the VBA editor window. Each form can have its own "code behind", and you can also add modules and/or classes in VBA. When you select the Code Builder option for the first time it will create, and link to, the function to put your code in. In my form (two screenshots ago) I named the Department list box "dept", and I can set its row source (available options) using the 3 lines of code below.

In the code below I declare a string to hold the query string. I then assign that query string that returns two columns to the row source property of the "dept" list box control. This is one of the things Access makes fairly easy, just assign the query as the row source, and it just works. Also, because this is within the Form_Load method, the form executes the query every time it loads which ensures the most recent list always populates the "Department" list box.

Private Sub Form_Load()
    Dim sql As String
    sql = "SELECT deptID, deptName FROM dbo_vDept ORDER BY deptName ASC "
    Me.dept.RowSource = sql
End Sub

Now when the form loads, you should see the results of dbo.vDept in the Department list box:

Putting Action to Buttons

When I created the two buttons, I updated the text on them to indicate what I have planned for the button, but currently they have no action setup. Setting up buttons to have action is similar to setting the Form_Load action we set in the prior step. Open the form in Design View, click the button you want to make functional, and set the "On Click" event under the Event tab of the properties and choosing "Code Builder".

First, let's make the Reset Form button work. Notice the method is named "rstForm_Click"? The button is named "rstForm" and this is for the Click action of that button.

Private Sub rstForm_Click()
    DoCmd.Close acForm, "addPerson", acSaveYes
    DoCmd.OpenForm "addPerson", acNormal
End Sub

The method essentially closes and reopens the form, saving any changes to the form itself (not the data in the fields).

Finally, we need to create the code to make the "Create Record" button functional. Essentially it needs to take the data entered into the fields on the form and pass the values to our stored procedure. I've always thought it requires way too much code to accomplish such a simple task, but it's mostly copy/paste and changing variables so it isn't tricky, just annoying. Note, this doesn't have any input validation, error handling, or anything else fancy but is still already over 50 lines of code.

Private Sub createRecord_Click()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command

    Dim nameFirst As String
    Dim nameMI As String
    Dim nameLast As String
    Dim dob As Date
    Dim sex As String
    Dim deptID As Long

    Dim nameFirstParam As New ADODB.Parameter
    Dim nameMIParam As New ADODB.Parameter
    Dim nameLastParam As New ADODB.Parameter
    Dim DOBParam As New ADODB.Parameter
    Dim sexParam As New ADODB.Parameter
    Dim deptIDParam As New ADODB.Parameter

    nameFirst = Me.nameFirst.Value
    nameMI = Me.nameMI.Value
    nameLast = Me.nameLast.Value
    dob = Me.dob.Value
    sex = Me.sex.Value
    deptID = Me.dept.Value

    Call connStringSet
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = connString
    cnn.Open cnn.ConnectionString

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "dbo.addNewPerson"

    Set nameFirstParam = cmd.CreateParameter("@nameFirst", adChar, adParamInput, 100, nameFirst)
    Set nameMIParam = cmd.CreateParameter("@nameMI", adChar, adParamInput, 25, nameMI)
    Set nameLastParam = cmd.CreateParameter("@nameLast", adChar, adParamInput, 100, nameLast)
    Set DOBParam = cmd.CreateParameter("@dob", adDBTimeStamp, adParamInput, , dob)
    Set sexParam = cmd.CreateParameter("@sex", adChar, adParamInput, 1, sex)
    Set deptIDParam = cmd.CreateParameter("@deptID", adBigInt, adParamInput, , deptID)

    cmd.Parameters.Append nameFirstParam
    cmd.Parameters.Append nameMIParam
    cmd.Parameters.Append nameLastParam
    cmd.Parameters.Append DOBParam
    cmd.Parameters.Append sexParam
    cmd.Parameters.Append deptIDParam

    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    rs.LockType = adLockOptimistic
    rs.Open cmd

    cnn.Close
    Set cnn = Nothing
    Set rs = Nothing

    rstForm_Click

End Sub

View The Results

If you've been following along so far you should have a linked table named dbo_vPersonDept. If you open this table (really linked to a view in SQL) you should be able to see new records as you add them. Go ahead and try out your new buttons. Then think about how form inputs should be validated and how you would implement some error handling.

Wrapping Up

Obviously I have skimmed over details that many people would expect to see here, but I think I covered all the very important stuff. What is still needed for this basic system is a form to display the data from the vPersonDepartment view, but compared to executing stored procedures that's a cake-walk! Also, this series was not intended to cover all the intimate details, but instead shows the bigger concepts of how to tie the pieces together effectively between SQL Server and MS Access.

By Ryan Lambert
Published February 07, 2014
Last Updated August 13, 2015