Visual Basic for Applications/Access

This lesson introduces Access macros.

Objectives and Skills

Objectives and skills for Access scripting include:

• Using Microsoft Access ADO objects

Readings

1. Microsoft: Introduction to the Access 2010 Templates
2. Microsoft: Displaying Data from a Recordset in an Excel Worksheet
3. Excel-Spreadsheet: DAO/ADO Objects

Examples

'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

Activities

In these activities you will create macros which interact with Access databases.

1. Northwind Table
   1. 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.
2. Northwind Update
   1. 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 flastname@northwindtraders.com. This may either be completed using Recordset.Edit and field values or Connection.Execute and SQL UPDATE.
3. Northwind Query
   1. 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.

References

Type classification: this is a lesson resource.

← Outlook

Visual Basic for Applications