The example I am going to use writes to a table within an Access database from inside an
Excel workbook. VBA works exactly the same in all Microsoft Office applications, except
that the object model is obviously too different from application to application to take the
functionality of the application into account.
First, you will need to create a sample table in an Access database for your code to send
data to. To do this, click Create in the menu bar and then click the Table Design icon in the
Tables group of the ribbon.
Create a single text field called MyText. Data Type must be specified as text. Save the
table by giving it the nameTestAccess. A dialog box will appear requesting a primary key to
be defined. For the purposes of this example, a primary key is not required so answer “No.”
Load Excel. Once you load Excel, you need to enter the VBA code window. This works
exactly the same as in Access: pressALT+F11. The code window has exactly the same
functionality as the one you are used to in Access.
When you used a different application from Access, you had to put in a reference to the
Object Library file first. We have to do the same thing in Excel by putting in a reference to
the Access Object Library in order to tell Word how to find the Access object model.
However, because Access uses more than one object library, we need to put a second
reference in to DAO (Data Access Objects) as well.
Use Tools | References from the Visual Basic Editor menu as before, but this time select
the Microsoft Access Object Library and the Microsoft DAO Object Library and check the
boxes next to these libraries, as shown in Figure 17-3. In terms of version numbers for the
object libraries, select the highest version number available.
This now gives your code all it needs to manipulate Access. Insert a module by selecting
Insert | Module from the Code menu, and then enter the following code. The code assumes
that you have a database at "C:\Temp\TestAccess.accdb":
Sub Test_Access()
Dim oAApp As Access.Application, oRSet As DAO.Recordset
Set oAApp = CreateObject("Access.Application")
oAApp.OpenCurrentDatabase "C:\Temp\TestAccess.accdb"
Set oRSet = oAApp.CurrentDb.OpenRecordset("MyTable")
oRSet.AddNew
oRSet!MyText = "Test String"
oRSet.Update
oRSet.Close
Set oRSet = Nothing
Set oApp = Nothing
End Sub
When this is run, it will open a database called TestAccess in the path C:\Temp\ and will then
add a new record to your sample table MyTest.
The code creates an objectoAAppto hold the Access application object, and an object
calledoRSetto hold the DAO recordset. The variableoAAppis set to an Access application
Chapter 17: Using Access to Interact with Other Office Programs 227