Repository files navigation

This project is a Data Access layer that can be used in any VBA application to connect to SQL Server.

The purpose is to keep database connectivity tidy and core database commands centralized within a project.

The layer handles all actions involved with reading and writing data to SQL Server.

You must have the following VBA Project references installed in the VBE Editor reference window (minimum versions stated):

• Visual Basic For Applications
• Microsoft Excel 14.0 Object Library
• OLE Automation
• Microsoft Office 14.0 Object Library
• Microsoft Active X Data Objects 6.1 Library
• Microsoft Active X Data Objects Recordset Library 6.0
• Microsoft ADO Ext. 6.0 for DDL and Security

The layer is organized as follows:

You must specify the name of your server and the name of your database in the class DBInstance.

These are the 2 lines to amend:

objDBCredentials.SetServer = "MY_SERVER_NAME"objDBCredentials.SetDBName = "MY_DATABASE_NAME"

The layer has three main methods:

• ExecuteStoredProc - this is for running stored procs on the database for CRUD operations.
• GetRecordsetFromStoredProc - this is for retrieving data from the database via stored procs.
• GetDataFromSQLStatement - this is for retrieving data from the database via a raw SQL query.

All the methods can accept variables being passed back and forth between the layer and the database.

There are various examples of how to use the Data Access Layer in the module 'mdTestDataAccessLayer'. These examples include executingstored procs in the database to retrieve data, and for executing common CRUD operations. To give you a brief idea of how the layer works, here is anexample of getting data from SQL Server using a simple SQL statement:

Dim rstData     As New ADODB.RecordsetDim oDBInstance As New clsDBInstanceDim oDatabase   As clsDatabaseSet oDatabase = oDBInstance.GetSharedDatabase()Set rstData = oDatabase.GetDataFromSQLStatement("SELECT country_name, country_region FROM [dbo].[country]")rstData.MoveFirstDo While Not rstData.EOF    Debug.Print rstData.Fields("country_name").Value & " " & rstData.Fields("country_region").Value    rstData.MoveNextLoopoDBInstance.CloseSharedDatabase