Project Background and Description

I am presenting, once again, a method of connecting and upgrading a SQL data source into a VBA presentation process. My previous article on SQL Server and VBA produced some varied reactions, I must say:

• “What I have found work best is to use ExcelDNA . . .”,
• “We use VBA to handle the inputs via form controls, create the connection to SQL using ADO. . . “,
• “use VBA as a major data management tool (‘somehow, that frightens me’)”,
• ” . . . whenever I hear that Excel is connecting directly to a SQL database, I get a little tic that twitches in one eye for a while”

In any case, my experience as a contract developer has shown me that data connections between SQL Server and VBA are alive and still used extensively, even in the present days of .Net, Big Data, web based data sources.

My introduction to data management was a position in tech support/software implementation/onsite software installation and instruction. This product was a front end/back end MS Access product installed on a runtime time version of MS Access. This product was gradually developed into a server based VBA product, with a SQL Server data source. Entry to this product was controlled by a security login screen triggered by the AUTOEXEC macro OnOpen. This is shown as Screen V.1 below:

Screen V.1

The backend database was connected through a login and password process. Once the MS Access backend database had reached its capacity, and more was needed, our company developed a SQL Server data source product. The data connection process was identical to the MS Access back end db.

This program was a CRM used to track prospects for residential real estate sales offices. The Data Entry form V2 was used to enter the data through the MS Access Runtime Version.

Data Entry Form V2

Project Scope

I have developed a different set of forms, queries and tables to use the product concept described above for a different product, using a front end MS Access VBA program with a connection to a SQL Server data source. The new system includes the following (attached as a compressed file):

Project Description

The Steps to this process are arranged below by numbered titles and corresponding screens.

Import the text file into a SQL Server database

Use the SQL Server Import/Export Wizard (shown below) to import the text file that will act as the data source into the SQL Server database. Adjust the record data types so that the data will be compatible between source and destination:

SQL Server Import/Export Wizard D1

Create a SQL Server table to serve as original data source

Create a SQL Server table to serve as a Fact Table. Note the Primary Key has the Identity property added to allow for joining to other (Dimension) tables. The foreign key ID data columns will need to have data types changed from varchar to smallint. 

SQL Server Data Table as Destination D2

Create a data relationship diagram

Create a data relationship diagram. After the appropriate tables have been created in SQL; the relationships between tables can be defined in a Database Diagram. The Diagram shown below (Screen D3 Database Diagram ), contains five Dimension Tables and one Fact Table. The relationships are 

Screen D3 Database Diagram 

You may receive the error message below, if the data types were not checked during the Import/Export Wizard process for compatibility.

Error Message Data Types Not Compatible

Open the Linked Table Manager in Access

Create the data relationships and database in Access as the source for data entry. Go to the Linked Table Manager to create links to the SQL tables in the SQL Server database. The table names below will be used as data source properties in the Access data entry form.

MS Access Linked Table screen as MS Access Form data sources Screen D4

Create the Access Form using the Form Wizard

Screen D5 illustrates an Access Form used to bring in data from the SQL Server database. The creation steps are using these menus: CREATE ->FORMS->FORM WIZARD.

This data will be updated dynamically back to the SQL Server Database when the data in this form is entered or edited. Add command buttons for common record operations (ADD, SAVE, DELETE, CLOSE FORM) to the form screen.

Screen D5 Create MS Access Form

Create a query as the data source for the form

This query runs a SELECT statement for the Prospects Table (FACT Table) with joins to the supplemental tables (DIMENSION Tables) that contain data that will populate combo dropdown boxes on the Access data form. Note the joins to all the Primary IDs in the Dimension tables. Remember from above that the data types for all Primary ID fields must match data types (smallint or numeric); otherwise an error message (Error Message Data Types Not Compatible) is generated. Note most of the data entry boxes (text boxes) take data entered directly through the keyboard. Five of the data entry boxes are combo boxes with a SELECT statement to choose records from other tables (Community, Sales Rep, Media types, etc.)

Screen 6 Create Query as Data Source for Form

Create links in the Prospect Record Form

We create links to connect the data in text boxes (FirstName, LastName, etc.) to the results from the query, Prospect Information.

Screen 7 - Create links in "Prospect Record" Form

Run a data check exercise to verify data updates

First go to Management Studio to check your data. This step begins an error check to make sure data entered into the Access Data Form, "Prospect Record", updates the linked data in the SQL Server database. First we check the existing data. In this view of the "Property Inventory" table "Prospects", note that FirstName, LastName is "Wlimar Alston", Media Type is "Walk-in", Interest Level is 3. 

Screen 8 Data Check Exercise to Verify Data Update

Change data via the Access form

Enter data to the form, and change FirstName to "Wilbur", InterestLevel to 2 and MediaType to "Newspaper".

Screen 9 Change Data via MS Access Form

Click Save to change the data.

Verify the data has been changed in the SQL Server database

Screen 10 displays the data that has been changed in the SQL Server database, which serves as the original data source.

Screen 10 Data Has Been Changed In SQL Database Source

Summary

After leaving a data position supporting an Access Runtime product written in VBA, I was pleasantly surprised at the popularity of the VBA product used for data analysis reporting. I assume that many small to medium-sized companies began with automating MS Office products, usually Access and Excel. As the data requirements of the company expand, Microsoft has expanded the data capacity of the Access and Excel products to keep clients satisfied with their MS Office products and delay the move to a major data platform (DB2, Oracle, etc). VBA will automate just about anything with some adjusted SQL script.

I have described the use of SQL Server as a data source for a VBA spreadsheet automation project, which uses the convenient VBA\SQL product. The dashboard I have created can also contain pivot tables and charts for more advanced data analysis. I am not sure how long the VBA product will exist but for now the product seems to provide many useful advantages for data analysis people.