Advanced VBA | Linking to data exercise | Add New Events to an Access Database from 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.

Software ==> Advanced VBA  (33 exercises)
Version ==> Any MS Office versions
Topic ==> Linking to data  (3 exercises)
Level ==> Average difficulty
Subject ==> VBA training
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 file called Add Events.xlsm from the folder shown above.  Your task is to write code which adds a new record to the WorldEvents Access database in the same folder using the details written into the worksheet.

Basic worksheet

You may have a different opinion of what constitutes an important event!


In the VBE set a reference to the Microsoft ActiveX Data Objects 6.1 Library.

Create a subroutine and declare a variable which can hold a reference to a new Connection object.  Set the ConnectionString property to point to the database in the folder shown above:

Connection string

You'll need to change the folder path to point to the correct database.

Add a variable which can hold a reference to a new Recordset object.  Use the AddNew method to add a record to the Event table of the database.

You'll need to change the LockType property of the recordset so that it isn't read-only.

Populate the EventName, EventDetails and EventDate fields of the recordset using the values of the relevant cells from the worksheet:

Populate field

The basic syntax to populate a field is shown here.

Don't forget to update the recordset and then close both it and the connection.

Attach the subroutine that you've written to the button on the worksheet and then test that it works.

Check results

Open the Event table by double-clicking its name.

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.
This page has 0 threads Add post