Connecting SQL and MS Access - Part 1

In my previous post, Building Reliable Data Solutions, I gave a 20-mile high view of how I use SQL and MS Access to collect and provide data to end users. What I didn't do was get into any of the nitty-gritty details of connecting Access and SQL Server together effectively, so this mini-series will attempt to cover those details. By the end of the series you should be armed with the basic steps I follow when creating a database driven system for others. In order to keep this a reasonable length I must make some assumptions about prior knowledge and experience as well as keeping examples somewhat simplified. If you feel like I have skipped something please leave a comment and I will try to address it!

In this post we will create a database using SQL Server 2012 Express edition. This database will be the foundation for the MS Access front-end we will create in future posts in this series.

My Environment

My development environment for SQL and Access is a Windows 8.1 virtual machine running in Virtualbox on my MacBook Pro. I have SQL Server 2012 Express edition installed along with a few other tools I use throughout the day. Obviously, I need MS Access, and I also use Visual Studio for other .NET work. Git and Source Tree provide me with my source control, and I think that's about it on the machine. I have given this virtual machine a single processor and 2 GB of RAM which I'm pretty sure are the minimum specs for Windows 8. The only time the VM feels really sluggish is when I'm switching between two users on that VM, both utilizing SQL. But, the majority of the time it runs great.

Create the Database

For this demo project I'm going to create a database named DEMO.

CREATE DATABASE DEMO

For the database itself, I have decided it will track people in a company and what department(s) they work for so let's just jump in and start creating our tables. First comes the person table:

CREATE TABLE dbo.person
(
    id INT IDENTITY NOT NULL,
    nameFirst VARCHAR(100) NOT NULL,
    nameMI VARCHAR(25) NULL,
    nameLast VARCHAR(100) NOT NULL,
    dob DATE NULL,
    sex CHAR(1) NULL,
    CONSTRAINT PK_personID PRIMARY KEY CLUSTERED (id),
    CONSTRAINT CK_personSex CHECK (sex IN ('M','F') OR sex IS NULL)
)

Now a table to store the departments:

CREATE TABLE dbo.dept
(
    id INT IDENTITY NOT NULL,
    deptName VARCHAR(200) NOT NULL
    CONSTRAINT PK_deptID PRIMARY KEY CLUSTERED (id),
    CONSTRAINT UQ_deptName UNIQUE NONCLUSTERED (deptName)
)

The last table I'm going to create for now will link people to their departments. It is valid to have one person linked to more than one department.

CREATE TABLE dbo.personDept
(
    id INT IDENTITY NOT NULL,
    personID INT NOT NULL,
    deptID INT NOT NULL,
    CONSTRAINT PK_personDeptID PRIMARY KEY CLUSTERED (id),
    CONSTRAINT FK_personID FOREIGN KEY (personID) REFERENCES dbo.person(id),
    CONSTRAINT FK_deptID FOREIGN KEY (deptID) REFERENCES dbo.dept(id) 
)

Create Views

To provide easy access to the data in the front-end I like to create views. This allows me to handle the joins on the server, not allow direct access to the base tables, and make changes to the underlying table structure more easily. The more loosley coupled the front-end is to the underlying database schema, the easier future maintenance will be.

I want to provide two views for the front-end: one to see people with their departments, and one to list the departments with their id. First, the view to see all the person records with their associated departments:

CREATE VIEW dbo.vPersonDept 
WITH SCHEMABINDING
AS
SELECT p.id AS personID, p.nameFirst, p.nameMI, p.nameLast, p.dob, p.sex,
        pd.deptID, d.deptName
    FROM dbo.person p
    LEFT JOIN dbo.personDept pd ON p.id = pd.personID
    LEFT JOIN dbo.dept d oN pd.deptID = d.id
WITH CHECK OPTION
GO

Now a view to show all possible departments:

CREATE VIEW dbo.vDept
WITH SCHEMABINDING
AS
SELECT id as deptID, deptName FROM dbo.dept
WITH CHECK OPTION
GO

Silly Views

You might be wondering why I bother creating the dbo.vDept view because all I'm doing is explicitly selecting the only two columns from one table, it doesn't simplify anything!? Or does it?

Fast-foward to the future. Your boss has just told you the database needs to now store more information related to departments, including managers and the number of staff. Now she even wants you to modify the drop-down menu of departments to include the managers first initial and last name. (Of course, requirements never change in the real world, so just pretend for a moment with me!)

If I had just linked the drop-down menu directly to the dbo.dept table, I would have to make changes directly in the front-end application to handle displaying the new data in the drop down menu. This type of change in Access using VBA means writing SQL concatenation queries within VBA. It's completely do-able, but annoying, time consuming, and error prone. More importantly, making changes in the front-end means I need to redistribute the front-end to the end users. Again, annoying and time consuming.

But because I linked the drop-down menu to the dbo.vDept view, I simply need to alter the view on the server to concatenate the deptName field along with the manager's name in the desired format. As soon as I update the view definition on the server, the front-end drop-down menus will display the updated format. No need to write complex SQL queries in VBA, no need to update the front-end, nothing to redistribute.

Insert Some Data

I like to start with some dummy data so I can verify that the tables and views are setup properly.

INSERT INTO dbo.person (nameFirst, nameMI, nameLast, dob, sex) VALUES ('Bob',NULL,'Smith','1977-02-03','M')
INSERT INTO dbo.person (nameFirst, nameMI, nameLast, dob, sex) VALUES ('Sally','Q','Smith','1968-03-02','F')
INSERT INTO dbo.person (nameFirst, nameMI, nameLast, dob, sex) VALUES ('Gunther',NULL,'Gustav',NULL,NULL)
INSERT INTO dbo.person (nameFirst, nameMI, nameLast, dob, sex) VALUES ('Veronica','V','Valentina',NULL,'F')
INSERT INTO dbo.person (nameFirst, nameMI, nameLast, dob, sex) VALUES ('LegoMan','Block','Head','1932-01-01',NULL)
INSERT INTO dbo.dept (deptName) VALUES ('Marketing')
INSERT INTO dbo.dept (deptName) VALUES ('IT')
INSERT INTO dbo.personDept (personID, deptID) VALUES (1,2)
INSERT INTO dbo.personDept (personID, deptID) VALUES (2,1)
INSERT INTO dbo.personDept (personID, deptID) VALUES (3,2)
INSERT INTO dbo.personDept (personID, deptID) VALUES (5,2)
INSERT INTO dbo.personDept (personID, deptID) VALUES (5,1)

This creates 5 people and 2 departments. The person Veronica (id = 4) is not linked to any department, while LegoMan is in both Marketing and IT. Go ahead and verify that the view works using:

SELECT * FROM dbo.vPersonDept

Data Modifying

As I have mentioned before, I don't let Access do more than I have to at a bare minimum, and I do not let it modify data directly. Every data modifying action (INSERT, UPDATE, DELETE) is handled using stored procedures. This provides the same type of advantage as I described above: schema changes, business rule changes, etc. are mostly handled on the server, not the UI, again making future changes less intimidating.

The stored procedure below takes parameters to create a new person record and link the person to a department. Notice how I'm able to run an INSERT statement into dbo.person, grab the newly created primary key (personID) and use that in a second INSERT statement to link the new person to a department? To accomplish the same functionality using VBA in Access alone would require a LOT more code and can be tricky to ensure you truly get the id you just created.

CREATE PROCEDURE dbo.addNewPerson 
    @nameFirst VARCHAR(100),
    @nameMI VARCHAR(25) = NULL,
    @nameLast VARCHAR(100),
    @dob DATE = NULL,
    @sex CHAR(1) = NULL,
    @deptID INT = NULL
AS
BEGIN
DECLARE @personID INT;
    INSERT INTO dbo.person (nameFirst, nameMI, nameLast, 
            dob, sex)
        VALUES (@nameFirst, @nameMI, @nameLast, @dob, @sex) 
    SET @personID = SCOPE_IDENTITY();
    INSERT INTO dbo.personDept (personID, deptID)
        VALUES (@personID, @deptID)
END
GO

Finally, let's test the stored procedure to make sure it works:

EXEC dbo.addNewPerson @nameFirst = 'Reese', @nameMI = NULL, @nameLast = 'Cat',
    @dob = '2013-01-15', @sex = 'M', @deptID = 2

Ideally, you should also test your inputs for all sorts of shenanigans and ensure that your stored procedures handle them appropriately and protect your database. I have omitted my normal try/catch blocks and error logging for the sake of space.

Coming Up Next....

In my next post I go over steps to connect a new MS Access database to the database we just created in SQL. We will cover setting up DSN files and linking tables to the database. Obviously, it's impossible to cover all the details in a single post so if I missed anything you have questions about, let me know in the comments!