Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
549 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Software ==> | Advanced VBA (28 exercises) |
Topic ==> | Linking to data (3 exercises) |
Level ==> | Average difficulty |
Subject ==> | VBA training |
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.
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 find other training resources for the subject of this exercise here:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.