Professional Microsoft Access database modernization and fully customized VBA automation solutions in Office 2016

WE OFFER SERVICES FROM ADVISORY CONSULTING TO DOOR-TO-DOOR TURNKEY SOLUTIONS.  

case studies

BELOW ARE SOME EXAMPLES OF WHAT CAN BE ACCOMPLISHED WITH MICROSOFT OFFICE VBA

Azure Active Directory Authentication on Hybrid SQL Azure-Access DB

Database Administration ms-office-365, microsoft-access-programming, microsoft-access, sql-azure, windows-azure

Scope
Our customer requested the ability to integrate their on-site Office 365 active directory with their hybrid SQL Azure Backend – Access Frontend database. Azure Active Directory authentication provides an alternative to SQL Server authentication as a mechanism for connecting to a Microsoft Azure SQL database by using identities in an Azure Active Directory. Azure Active Directory is the authorization server for Office 365 and other Microsoft business services. Utilizing active directory integrated authentication on a registered Office 365 account will provide seamless authentication to the clients SQL Azure backend.

Technologies

• Microsoft Access VBA
• Microsoft SQL Azure
• Azure O365 Active Directory Authentication

Customer Benefits
Azure Active Directory authentication provides central ID management. It allows password rotation in a single place and helps stop the proliferation of user identities across database servers. Customers can manage database permissions using external Azure Active Directory groups. It can eliminate storing passwords by enabling integrated Windows authentication and certificate-based authentication

Solution
We provided a process for configuring Azure Active Directory Authentication on our clients Azure SQL server. The following diagram indicates the trust and hosting relationships that allow a client to connect to an Azure SQL database. The request is authenticated by the Azure AD authentication library and is trusted by the Azure SQL server.

Using VBA and Microsoft's ActiveX Data Objects (ADO) we designed a login form that authenticates and refreshes the users O365 credentials for every linked table in our client’s hybrid SQL Azure Backend – Access Frontend database. Allowing the Access database's forms, reports & queries to communicate with the SQL Azure data tables. 

Microsoft Access Database Migration Tool
(*.accdb, *.mdb)

Database Administration microsoft-access, sql-azure, vba, sql, microsoft-access-programming, manufacturing, mechanical-engineering

Scope
We were migrating our clients older formatted Access Database *.mdb to the modern *.accdb 2010 format. While the newer version of the database was being developed the last released stable version was being used in live production. We needed an ergonomic process to quickly transfer the entire data in every table from the stable version to the developmental version. The client then requested that we extend the functionality to both on premise and cloud based SQL databases. We accomplished this by developing a process utilizing the Microsoft bulk copy program utility (BCP).
 

Technologies

• Microsoft Access VBA
• Microsoft SQL Server
• Microsoft Command Line Utilities 13.1 for SQL Server:
  • https://www.microsoft.com/en-us/download/details.aspx?id=53591

2. Customer Benefits

• Automated data transfer from MDW secured Access 2003 database to Access 2010 or Later migration
• Quickly update developmental database files with live data
• Ergonomic data transfer from Access database to on premise or cloud based SQL Server

Solution
There were several issues using Microsoft’s built in tools to transfer the data such as the older database was using the mdw security feature which is not compatible with the newer access database format. We designed the update tool in Visual Basic as a third-party Access database file that uses the DAO object library to loop through every table, build and execute SQL statements to transfer the data as shown below in Figure 1.

Figure 1: Database Update Tool Flow Diagram

Access DB (*.mdb or *.accdb) to latest format Access DB (*.accdb)
Enter the paths of the source and the sink databases as shown below in Figure 2. If the source database is *.mdb format and it requires an *.mdw file to login, select the highlighted toggle as shown below: Fill in your MDW Path, User name and password if applicable:

Figure 2: Access to Access transfer

Access DB (*.mdb or *.accdb) to SQL (located on-site or cloud)
Enter the paths of the source and the sink SQL database connection parameters as shown below in Figure 3.

Figure 3: Access to SQL transfer

Microsoft Access Front-End SQL Back-End Credential Update Process (VBA; SSA)

Desktop Software Development windows-app-development, vba, sql-programming, microsoft-access-programming

Scope
Our client has a desktop app with a Microsoft Access front-end and SQL back-end that required additional development. Specifically, the ability to automatically update the SQL credentials for every linked table in their database.

Technologies

• Microsoft Access VBA
• Microsoft SQL Server
• Microsoft SQL ODBC Connection Strings
• Microsoft ActiveX Data Objects (ADO)
  • https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/connection-object-ado

Customer Benefits
Built in function for automatically updating the SQL credentials for every linked table instead of using the manual linked table wizard in Access

 Solution
The split database architecture using Microsoft SQL as a back-end provides many advantages over the native Access tables such as the ability to expand the database size beyond the 2 GB limitation, provide secure user security permissions and transaction logging.

We designed a SQL credential update process using Microsoft Access and the ADODB object. We added two command buttons on their main startup form to edit the current SQL credentials and another to refresh the connections.

As you can see below the ‘Edit SQL Credentials’ form contains all the necessary information to connect to any SQL server via SQL Server Authentication.

The SQL credentials are stored in a local Access table.

Upon relinking the tables the VBA code will pull the SQL credentials that are locally stored and will build the SQL connection string. It will then retrieve a list of all Access table objects from the hidden Access system tables. Looping through each linked SQL table, the code will redefine the connection string and refresh the table via the Microsoft ADODB object.

Generated SQL connection string shown below

Microsoft Access Database Documentation Tool

Database Administration microsoft-access, vba, sql, microsoft-access-programming, manufacturing

Scope
Our client inherited an older Microsoft Access database whose designer was no longer under their employment. This tool was designed to ergonomically document all tables, fields, controls, queries, reports and their dependencies. This tool was utilized to identify unused objects and provide the client a documented design of their database for future development.

Technologies

• Microsoft Access VBA
• Access Application Object
  • https://msdn.microsoft.com/en-us/library/office/ff821758.aspx
• DAO Object
  • https://msdn.microsoft.com/en-us/library/office/ff195520.aspx

Customer Benefits
Quickly document all tables, fields, controls, queries, reports and their dependencies in an Access database.

Solution
We designed a documentation tool in Visual Basic as a third-party Access database file that uses the DAO and Access Application objects. The tool documents all Microsoft Access objects and their dependencies that are to be imported in Microsoft Excel for further analysis.

Microsoft Access Documentation Tool

Document Tables and Field
The documentation tool creates a delimited text file containing all database tables and its fields using the DAO Object

Document Form Controls
Using the Access application object, the documentation tool creates a delimited text file listing all forms, form objects, object type, object name, control source or event. If it is a command button then it outputs the event, row source and default value. The forms can relate to a table or query in their ‘control source property while controls may reference the ‘control source’ and/or ‘row source’ properties.

List All Queries
Using the DAO object, the documentation tool creates a delimited text file listing all queries, SQL code, date created, and date last modified. Both stored queries and queries from forms, controls & reports are listed. GUI queries are denoted by ~sq_f, ~sq_c, ~sq_r. etc.

List All Reports
Using the Access application object, the documentation tool creates a delimited text file listing all reports, report object type, report object name, control source or event, row source, date created and date last modified.

SQL Table Creation Tool
There are many differences between Access database tables and SQL Tables. For example, Access datatypes can be quite different than SQL datatypes. Field properties such as autonumber and unique values are set at various levels of the Access UI. This script analyzes these properties and creates an analogous SQL create table statement for every table in the targeted database. If there is no primary key in the Access Table then the first autonumber field will be defined as the primary key, then unique, then first line.

SQL Table Drop Tool
This script creates an analogous SQL drop table statement for every table in the targeted Access database.

Microsoft Excel VBA Secure PDF Generation

ERP / CRM Software microsoft-excel

Scope
Our client requested that we design an automated payroll management system in Microsoft Excel that generates PDF electronic salary slips that require a password to open and a secondary password to protect the PDF signature. The system must have the capability to email the PDF salary slips to the respective employees.

Technologies

• Microsoft Excel VBA
• Microsoft Outlook Automation
• PDFtk - PDF two step security
  • https://www.pdflabs.com/tools/pdftk-the-pdf-toolkit

Customer Benefits
Automated process of generating secure pdf payment slips for every employee and manually emailing them individually.

Solution
We designed a process using Excel VBA and PDFtk to generate the electronic salary slips from an employee data worksheet. The operational interface is shown below.

Using the standard email template shown below, the script generates email drafts in Microsoft Outlook to each employee with their respective PDF salary slips attached. It can also include additional attachments and BCCs.

The script generates email drafts to be reviewed before being sent out.

The employee slip PDF file requires a password to open and a secondary password to protect the PDF signature and its contents

Secondary PW to remove security permissions

Microsoft Excel VBA Regression Algorithm

Scope
Our client had an existing LabVIEW program that calculates mechanical force coefficients from raw coast-down vehicle speed data. They requested that we reverse engineer their existing LabVIEW program and develop an analogous algorithm using Excel VBA.

Technologies

• Microsoft Excel VBA
• Polynomial Regression

Customer Benefits
Reduced process time from over 8 hours to less than a minute.

Solution
We designed a variable order linear polynomial regression algorithm in Excel VBA that calculates force coefficients (Mechanical losses, Road Friction, Aerodynamics) from raw coast-down vehicle speed data. The user will input the empirical parameters corresponding to the data to be analyzed: Vehicle Weight, Ambient Temperature, Ambient Pressure, high & low speed cutoffs.

Figure 1: Algorithm input operational interface

The algorithm contains two polynomial regression steps. It first cuts off the high and low speed data and then performs a variable order linear polynomial fit on the raw speed by time data.

Figure 2: Polynomial fit on raw speed as a function of time *All Data Fabricated

The algorithm then uses this numerical approximation to calculate acceleration force and eventually the mechanical loss coefficients through a second order polynomial fit on the net force applied on the vehicle by the numerically approximated speed data.  Thus, the better the initial polynomial regression fits the data (or minimizes the residual error) the more representative your coefficients will be of your empirical data.

 Figure 2: Polynomial fit on approximated acceleration force as a function of velocity *All Data Fabricated

The second polynomial fit of force by velocity is used to calculate the mechanical loss coefficients. The order of the polynomial regression is defaulted to ‘second order’ but it can be adjusted at the engineer’s discretion.

Figure 4: Polynomial fit on approximated acceleration force as a function of velocity *All Data Fabricated

Microsoft Excel VBA SQL Role Creation Script

Database Administration database-adminstration, sql, sql-programming, excel-vba

Scope
Our client requested an ergonomic process to generate multiple database-level roles with assigned table or field based permissions.

Technologies

• Microsoft Excel VBA
• T-SQL

Customer Benefits
Automated manual process of writing tedious and repetitive SQL scripts. Provides an ergonomic interface for monitoring custom SQL roles and their permissions.

Solution
We designed a process using Visual Basic via Microsoft Excel to generate a SQL script that creates database-level roles and assigns the appropriate permissions. Using the form shown below, the client can list the roles to be assigned and the tables for which the permissions are to be granted. If field level permissions are required, list the fields in the same manner as Student Attendance with the appropriate Grant key in column A.

Fill in the table with ‘Read’, ‘Read + Write’ or ‘Admin’ to assign permissions as follows

Running the Visual Basic code generates the appropriate SQL Script

Executing the SQL Script generates the respective roles

Increase your daily efficiency by automating your office routine with RBI, LLC