ADVANCED VBA EXERCISES▼
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.
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.
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:
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:
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.
Open the Event table by double-clicking its name.
Save and close the workbook.