Advanced VBA | Linking to data exercise | Extract a List of Events from Access to Excel

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

The answer to the exercise will be included and explained if you attend the course listed below!

Software ==> Advanced VBA  (33 exercises)
Version ==> Any MS Office versions
Topic ==> Linking to data  (3 exercises)
Level ==> Average difficulty
Course ==> Advanced VBA
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Open the database called WorldEvents.accdb in the above folder and look at the Event table.

Event table

Double-click the Event table to see its contents.

 

Your task is to write a subroutine in Excel VBA which extracts this data into a new worksheet.

Close Access, create a new Excel workbook and begin a new subroutine.

In the VBE, add a reference to the Microsoft ActiveX Data Objects 6.1 Object Library.

Declare a variable which can hold a reference to a new Connection object and set the ConnectionString property to point to the WorldEvents database.

Connection string

You'll need to edit the folder path to make sure you're pointing to the correct location.

 

Add code to test that you can open and close the connection.

Add a variable which can hold a reference to a new Recordset object and set its ActiveConnection property to refer to the connection you have created.

Set the recordset's Source property to the name of the Event table.

Add code to test that you can open and close the recordset.

Add code to create a new worksheet and use the CopyFromRecordset method to copy the data from the Event table into cell A1 on this worksheet.

Results in Excel

The first few rows of results should appear as shown here.

 

Alter the Source property to an SQL statement which selects the EventName, EventDetails and EventDate columns:

Select columns

The basic SQL statement.

Run the subroutine and check that your new worksheet has only the columns you've requested.

Alter the Source property again so that it returns only those events which have occurred since your date of birth:

Add where clause

This would return any events since 22nd May 1978

Run the subroutine and check that you have returned fewer results.

Alter the Source property again so that the results are sorted in ascending order of date:

Ordered results

Try DESC if you want the results in reverse date order.

If you still have time and energy, add code which writes the column headings into the first row of the worksheet.  You should make sure that this will still work if you change the columns you have selected from the database.

Save and close the workbook.

This page has 0 threads Add post