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 in VBA to add data to an Access database from an Excel workbook.

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 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 your VBA code 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:

'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;"

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: For example:

rs.Fields("EventName").Value = Range("C4").Value

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: