How to Program Microsoft Access With Visual Basic

Programming Access involves using VBA to create and manipulate database objects.

Microsoft Access is the database program in the Microsoft Office suite. Developers customize this suite by writing programs in its programming language, Visual Basic for Applications (VBA). Programming Access involves using VBA to create and manipulate database objects, including reports, tables, queries and data entry forms. For example, VBA can automatically populate the "gender" field in a form for entering magazine subscriber information, when the person using the form enters common names in the "first name" field. Programming Access gives you the ability to make data-entry and retrieval tasks easier for your team's users.

Things You'll Need

• Microsoft Access

Instructions

1. Open Access and press "Alt-F11" to enter the coding environment (called the "IDE" for "integrated development environment") for Access.

2. Click the "Insert" menu's "Module" item to create a new window for entering code.

3. Paste into the code window the following program code, which creates a table with VBA:

   Sub makeATable()

   Dim db As Database, td As TableDef, f As Field

   Set db = CurrentDb

   Set tbl = dbs.CreateTableDef("Userinfo")

   Set fld = tbl.CreateField("firstName", dbText)

   tbl.Fields.Append f

   dbs.TableDefs.Append tb

   'dbs.TableDefs.Refresh

   End Sub

4. Run the program by clicking on any statement in it and pressing "F5."

5. Press "Alt-F11" to return to the principal Access interface, then click the navigation pane's "<<" button, then its ">>" button. You'll see a new table created by your program. Read more information about creating tables with VBA on the MSDN page "Create and Delete tables" page.

6. Type a few sample first names in the new table to provide data for the VBA query you'll create next.

7. Press "Alt-F11" to return to the coding environment and paste this new sub-routine below the first one you wrote:

   Public Sub makeQuery()

   Dim db As DAO.Database

   Dim qd As DAO.QueryDef

   Dim str As String

   Set db = CurrentDb

   On Error GoTo DontDelete

   db.QueryDefs.Delete "qUser"

   DontDelete:

   str = "SELECT * FROM Userinfo;"

   Set qd = db.CreateQueryDef("qUser", str)

   End Sub

8. Run the new sub-routine as you did the first one, then return to Access and look for the new query your code created. It's called "qUser."

9. Double-click "qUser" to run the query. Access will display all the names you entered in the "Userinfo" table.

10. Create a report by clicking the "Userinfo" item in the navigation pane, then clicking "Report" on the toolbar. You'll write a VBA program that filters the report for a specific value you entered in step 6.

11. Right-click the new report's tab and select "Design view." Click the downward pointing arrow in the "On Load" event of the of the report's "Event" tab at screen right. This action will return you to the VBA IDE.

12. Paste the following code into the IDE. Replace the text "<ENTER VALUE OF A PARTICULAR ROW>" with any particular value you entered in step 6.

    Private Sub Report_Load()

    Me.Filter = "firstName = ""<ENTER VALUE OF A PARTICULAR ROW>"""

    Me.FilterOn = True

    End Sub

13. Run the report by pressing "Alt-F11" to return to Access, right-clicking the report's tab and selecting "Report View." Access will display the report, which will show only the record you typed for the filter in step 12. Read more about using VBA with reports on the Access Reports page.

References

• MSDN: Create and Delete Tables

Resources

• MSDN: Access Reports
• MSDN: Create and Delete Tables