This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	Advanced VBA (28 exercises)
Topic ==>	Linking to data (3 exercises)
Level ==>	Average difficulty
Subject ==>	VBA training

Use ADO to extract a table of world events from an Access database into a new Excel worksheet.

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.

Before you can do this exercise, you'll need to download and unzip this file .

Open the database called WorldEvents.accdb in the above folder and look at the 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. You'll need to edit the folder path to make sure you're pointing to the correct location:

'change the folder path before running this code

cn.ConnectionString = _

"Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=C:\Wise Owl\WorldEvents.accdb;" & _

"Persist Security Info=False;"

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.

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:

rs.Source = _

"SELECT EventName, EventDetails, EventDate "

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 (the following, for example, would return any events since 22nd May 1978):

rs.Source = _

"SELECT EventName, EventDetails, EventDate " & _

"FROM Event " & _

"WHERE EventDate >= #1978-05-22# "

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 (try DESC if you want the results in reverse date order):

rs.Source = _

"SELECT EventName, EventDetails, EventDate " & _

"FROM Event " & _

"WHERE EventDate >= #1978-05-22# " & _

"ORDER BY EventDate ASC"

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.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

This page has 0 threads

Add a new post

EXERCISE TOPIC

VBA EXERCISES

ADVANCED VBA EXERCISES

Use ADO to extract a table of world events from an Access database into a new Excel worksheet.

Head office

London

Manchester