Visual Basic for Applications/Access
This lesson introduces Access macros.
Objectives and SkillsEdit
Objectives and skills for Access scripting include:
- Using Microsoft Access ADO objects
'This macro displays information from the Northwind Access database and updates information in the database. Option Explicit Sub AccessFromExcel() Dim Connection As ADODB.Connection Dim Recordset As ADODB.Recordset Dim SQL As String Set Connection = New ADODB.Connection Connection.Open "Provider=Microsoft.Ace.OLEDB.12.0; " & _ "Data Source=c:\path\northwind.accdb" SQL = "SELECT [Last Name] FROM Employees" Set Recordset = New ADODB.Recordset Recordset.Open SQL, Connection Do While Not Recordset.EOF Debug.Print Recordset("Last Name") Recordset.MoveNext Loop Recordset.Close Connection.Close End Sub
In these activities you will create macros which interact with Access databases.
- Northwind Table
- Create an Excel macro that selects data from the Northwind Access database and inserts it into the active worksheet. The macro should place in the active worksheet the full name and email address of each employee using the Employees table. Your module should include Option Explicit, Dim, and use appropriate data types for variables.
- Northwind Update
- Create an Excel macro that pulls data from an Excel worksheet and updates an Access database. The Northwind company is expecting rapid growth and has decided that first-name-only email addresses are not going to be effective going forward. Using the worksheet generated in Activity 1, create an Excel macro that updates each employee record in the database and sets the email address to a first-initial, last-name format, such as email@example.com. This may either be completed using Recordset.Edit and field values or Connection.Execute and SQL UPDATE.
- Northwind Query
- Create an Excel macro that pulls data from the Northwind Access database and inserts it into the active worksheet. The macro should place in the active worksheet the name of each employee, total items sold, and total item value sold using the Employees, Orders and OrderDetails tables. Items is a sum of the [Order Details].Quanity. Value is the sum of [Order Details].[Unit Price] * [Order Details].Quantity * (1 - [Order Details].Discount). The macro should add a total row at the bottom of the imported data. Use the Cell.FormulaR1C1 property to add the formula for the two totals. Your module should include Option Explicit, Dim, and use appropriate data types for your variables. Create a query in Access inside the Northwind database that selects and returns the required data. Then use macro code in Excel to select the query results and place them in the active worksheet.