How to Run VBA Code in Access Without Errors?- (Quick Guide)

As we all know, Microsoft Access is a powerful database tool, in which it’s quite easy to create a database with several objects like reports, forms, tables, etc. But when it comes to adding some programming for the simultaneous execution of DB objects or to automate certain processes, then this can be daunting. Running VBA code in Access helps automate such tasks, build custom functions, and manage data efficiently. This guide explains how to run VBA code in Access step by step.

Understand MS Access VBA Code With Example:

In MS Access, programming is the way by which we can add functionality to our database. Basically, it is done in two ways:

• by using Access macros
• or Visual Basic for Applications (VBA) code

Suppose, for instance, you have made a report or form in your Access database. Now you want to add a command button to that form. So that when it is clicked, it will automatically open the report.

In that case, programming is used to create a macro or VBA procedure for setting the command button OnClick event property.  When you click the command button, the macro and procedure will automatically run in the background. Thus, clicking on the form’s command button will open the report.

So, all in all, you can say, VBA code in Access lets you add automation and several functionalities to your Access database application.

What Is The VBA Editor?

• Well, for programming in Access, we use the VBA editor. To start the VBA editor, first go to the Create option.
• After then tap to the module button in your database window.
• This will open the VBA editor.

As you can see in the figure, this VBA editor has several parts. So, let’s discuss each section in detail:

• The upper left corner is called Project Window. This contains a list of all the elements and modules defined in the database.
• Below the project window, there is a properties window. In this, you can specify different attributes for the objects you have used in the project window.
• At the bottom, there is an immediate window. Here you can test your procedure parts meanwhile the development. Other than it can also be used for getting immediate results of various commands. The immediate window is very useful during testing and debugging procedures.

Benefits Of Using VBA Code In Access:

Here are some basic reasons why you need to use MS Access VBA code areas as follows.

1. Manage smaller re-usable procedures (globally)
2. Create your own defined functions
3. in memory hold variables (globally)
4. Attach code to an event i.e. a button on a form
5. Manage more powerful procedures that an Access macro lacks
6. Easily wind up with other applications and platforms
7. smartly handle Access runtime errors

To learn Access VBA Code, you don’t need to be a programmer. The important factor in successfully programming with Access VBA is how well you know the structure of MS Access, often called tables, queries, forms, reports, and macros. It’s a very powerful features of Access to identify where VBA must overcome and improve on functionality.

How To Run VBA Code In MS Access?

VBA Code To Run A Query In MS Access

Looking for the VBA code to run a query in MS Access? If so, then check out the complete steps to accomplish this task.

You can use the VBA syntax to run a query in MS Access.

DoCmd.OpenQuery “Query Name”

Keep it in mind that “query name” must not be kept within the brackets “[]”.

Steps To Create A VBA To Run A Query In MS Access

Suppose you have an Access database which contains a query named “max_sales”.

So, here are the complete steps that you need to follow to create a VBA to execute the “max_sales” query:

Step 1: Add an Access Form

First of all, open your MS access application, after then add an Access Form.

To add a form, you have to go to the create tab, after then tap on the form icon presented over the MS Access Ribbon:

Step 2: Place a Button

In next step, you need to put a button on the Form. For this task, make a click on the ‘button icon’ present on the Access Ribbon. After that position that button on your form.

You will notice that after placing button on Access form, Command Button Wizard will appear on your screen. If you don’t need this Command Button Wizard then you can press the cancel button.

Step 3: Open the VBA Screen

For writing down the VBA code for the button, you need to open the VBA editor screen. Make a right-tap on the button and then choose the ‘Build Event…‘option.

After then choose the Code Builder option, and tap to the OK option:

Step 4: Write the VBA to Run the Query

It’s time to write the VBA code to run the query.

Check out the complete VBA code as shown in example below (where “max_sales”  is the query’s name):

Private Sub Command0_Click() 

DoCmd.OpenQuery “max_sales” 

End Sub

This is how VBA will appear in Access:

Step 5: View the Results

After writing off this VBA code, its time save your work.  Now get back to your Access form and shift to the form view.

For easy switching to form view, make a click on the view icon present under the design tab. Now, from the drop-down list, choose the form view.

At last make a tap the button to run “max_sales” query.  Now you can run a query using VBA code just by simply tapping to the button.

VBA Code To Run Report In Access

DoCmd.OpenReport method is used for opening report. You can use this DoCmd.OpenReport method to open the report in design view, Print Report, View Report, Print Preview, Layout View.

Here is the syntax that you have to use:

DoCmd.OpenReport (ReportName, View, Filter, WhereCondition, DataMode, WindowMode, OpenArgs)

Description of the above syntax:

– ReportName:

It indicates the report name in current database.it needs argument and it should be written within the double quotes.

– View:

Some common types of report’s view such as acViewNormal, acViewPreview, acViewLayout, acViewDesign and so on.

You can select any one of these types for specifying your report when it gets open.

– Filter:

It is a query name which present in the current database. so, set it between double quotes.

– WhereCondition:

In this place SQL clause is used without the word WHERE. So, you need to keep it in between double quotes.

– DataMode: ​

in this argument you have to specify data entry mode for your report. well the mode can be either the editing mode, or just the read mode.

– WindowMode:

When a report gets open, specify the window mode.

Besides that, you can open the report in following mode: acDialog, acHidden, acIcon or acWindowNormal.

     + acDialog: The open report is fixed and can’t be move.

     + acHidden: in this mode, the open report is hidden.

     + acIcon: The opened report appears like an icon.

     + acWindowNormal: in this mode, the open report appears as the view report.

– OpenArgs:

Using this argument, you need to specify the record when report gets open.

And you need to keep it in between the double quotes.

Remark: 

All the arguments mentioned above in DoCmd.OpenReport syntax are optional excluding ReportName argument.

suppose you have a form like this:

if you are opening a form for first time, the current record is Cate001. To print this category, tap to the Print Report button. It will display the report Cate001 along with the products it contained.

For printing off the Cate002 record, you need to firstly switch to this record. After then tap to the Print Report button.

But if you need to print Cate003, Cate004, Cate005, and so on. Then you have to create master detail report as shown in figure:

After then you need to apply the VBA code in the CmdPrint_Click event procedure, like this:

Private Sub CmdPrint_Click()
DoCmd.OpenReport “RptProductByCategory” _
, acViewReport, , “CategoryID='” & txtCategoryID & “‘”
End Sub

For Calling the Report from VBA, try the following VBA code:

Sub runReport()

    Dim con As ADODB.Connection

     Set con = New ADODB.Connection

     con.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _

       “Data Source=C:\mydb.mdb;”

     DoCmd.OpenReport “rptCustomer”, acViewPreview

End Sub

MS Access VBA Code To Open Another Database

Do you know you can launch another database from being within the current database? Well this can be done in three different ways. So just check it out…!

• FollowHyperlink Method
• Shell
• Access Automation

Method 1: FollowHyperlink Method

FollowHyperlink is a native method of Access database and user can open any file type using the default program associated with it.

As, it is a native command of MS Access, so you are not allowed to use for other programs like Word, Excel, PowerPoint …).

Public Function OpenDb3(sDb As String)  

 On Error GoTo Error_Handler    

Application.FollowHyperlink sDb Error_Handler_Exit: 

   On Error Resume Next  

 Exit Function Error_Handler:   

MsgBox “The following error has occurred” & vbCrLf & vbCrLf & _         

 “Error Number: ” & Err.Number & vbCrLf & _         

 “Error Source: OpenDb3” & vbCrLf & _        

   “Error Description: ” & Err.Description & _         

  Switch(Erl = 0, “”, Erl <> 0, vbCrLf & “Line No: ” & Erl) _       

    , vbOKOnly + vbCritical, “An Error has Occurred!”  

 Resume Error_Handler_Exit

End Function

Method 2:  Shell

Shell is universal command which can be used to perform many task. Apart from having long list of capabilities, it is also used to start programs and for opening files. You have to assign the .exe file after then what so ever command line switches recognizes by each of the individual exe file.

Since MS Office is well registered one and it’s a part of path variable. So, you don’t need to give the path for msaccess.exe file just provide the file or path to get open.

If you are having multiple MS Office version installed on your PC and you need to use any specific one. then in that case you need to include the path/file of the executable Access database.

Public Function OpenDb2(sDb As String)

    On Error GoTo Error_Handler

     Shell “cmd /c ” & Chr(34) & sDb & Chr(34), vbHide Error_Handler_Exit:  

 On Error Resume Next  

 Exit Function Error_Handler:  

  MsgBox “The following error has occurred” & vbCrLf & vbCrLf & _       

    “Error Number: ” & Err.Number & vbCrLf & _      

     “Error Source: OpenDb2” & vbCrLf & _        

   “Error Description: ” & Err.Description & _     

      Switch(Erl = 0, “”, Erl <> 0, vbCrLf & “Line No: ” & Erl) _

    , vbOKOnly + vbCritical, “An Error has Occurred!”  

 Resume Error_Handler_Exit

End Function

Method 3: Access Automation

Access Automation is the superb option to perform any action you want and obviously opening database also.

Public Function OpenDb(sDb As String)  

 On Error GoTo Error_Handler   

‘Early binding  

 ‘Use the following line if being used in Access or using Access reference 

   ‘   provides intellisense!  

 Dim oAccess               As Access.Application   

  ‘Late binding  

 ‘Use the following line if being used outside of Access without an Access reference’   

Dim oAccess        As Object  

   Set oAccess = CreateObject(“Access.Application”)    ‘Create a new Access instance  

 With oAccess      

  .OpenCurrentDatabase sDb    ‘Open the specified db     

   .Visible = True             ‘Ensure it is visible to the end-user       

.UserControl = True‘    

    .DoCmd.OpenForm “YourFormName”   ‘Open a form?’     

   .DoCmd.RunMacro “YourMacroName”  ‘Run a Macro?    

End With 

Error_Handler_Exit: 

 On Error Resume Next 

   If Not oAccess Is Nothing Then Set oAccess = Nothing  

 Exit Function 

Error_Handler: 

   MsgBox “The following error has occurred” & vbCrLf & vbCrLf & _     

      “Error Number: ” & Err.Number & vbCrLf & _      

     “Error Source: OpenDb” & vbCrLf & _     

      “Error Description: ” & Err.Description & _       

    Switch(Erl = 0, “”, Erl <> 0, vbCrLf & “Line No: ” & Erl) _       

    , vbOKOnly + vbCritical, “An Error has Occurred!”  

 Resume Error_Handler_Exit

End Function

Automate Access From Other MS Office Application Using VBA

VBA Access Object Library

In order to automate Access from other MS office application using VBA. Or if you require to use objects of other applications like Microsoft Excel or Word in Access. To make such tasks possible, add object library references. Basically, there are two options to perform this task:

1. Early binding
2. Late binding

First method, is used to add reference to Microsoft Access Object Library, just before execution. It executes faster, and you will get easy access to the VBA editor intellisense.

Apart from this there is chances to have a compatibility issues while running a PC program with different version of installed library.

Method 1: Early Binding

In this specific method you need to add reference to Microsoft Access Object.

1. Go to the VBA editor window, and tap to the “Tools” button present in menu bar.
2. Now from the drop-down list, choose the “References” option.
3. Afterward, the “References – Project 1” dialog box will appear.
4. In this dialog box, you can drag the scroll bar until you fetch what you want like “Microsoft Access14.0 Object Library”.
5. Now put a mark in the checkbox present in front of this object.
6. At last, tap on the “OK” option.
7. After this step, you have successfully added the Access object library reference.

Well you can make use of the above steps to add object libraries as per your requirement.

Note:

Use the following code for defining an Access.Application object and initiating it:

Dim objAccess As Access.Application
set objAccess = new Access.Application

Method 2: Late Binding

This method doesn’t require any additional reference to the object library. The Access Application object is declared as a normal object and initiated as shown here:

Dim objAccess As Object
Set objAccess = CreateObject(“Access.Application”)

How To Connect Access And Excel Through VBA?

In this section, we will discuss about how to connect Access database with the Excel by making use of the VBA code.

Here is how you can setup a connection with Access database. Connection string used for access 2003(*.mdb) and Access 2007/2010 (*.accdb) is different because both database version has different drivers.

For Access 2003 Database the Provider is: Provider = Microsoft.Jet.OLEDB.4.0.

For Access 2007/2010 the Provider is Provider = Microsoft.ACE.OLEDB.12.0

Before executing the below mentioned code, you need to add references for ADO DB Connection.

 Follow down these steps for Adding Reference:

How to add References in excel

• Open the VB Editor Screen by pressing Alt+F11
• Tools –> References…

• In the List of References Available over there, search for the “Microsoft ActiveX Data Objects 2.0 Library”. Choose either 2.0 Version or any other reference of a higher version.
• Tap to the OK option.

Connection with Access 2003 Database

Sub ADO_Conn() 

Dim conn As New Connection

Dim rs As New Recordset    

strcon = “Provider=Microsoft.Jet.OLEDB.4.0;” & _   

“Data Source=E:\Student.accdb;” & _  

“User Id=admin;Password=”    

conn.Open (strcon)    

qry = “SELECT * FROM students”  

rs.Open qry, conn, adOpenKeyset    

rs.Close 

conn.Close 

End Sub

Connection with Access 2010/2007 Database

Sub ADO_Conn()

Dim conn As New Connection

Dim rs As New Recordset    

strcon = “Provider=Microsoft.ACE.OLEDB.12.0;” & _  

 “Data Source=E:\Student.accdb;” & _ 

“User Id=admin;Password=”     conn.Open (strcon)  

qry = “SELECT * FROM students”   

rs.Open qry, conn, adOpenKeyset    

rs.Close   

conn.Close

End Sub

What Are the Best Practices for Running Access VBA Code?

Here are the best practices for running Access VBA code:

1. Always back up your crucial database before running new code.
2. Test your VBA code in a sample database.
3. Use meaningful names for procedures & modules.
4. Avoid long events. Break them into smaller functions.
5. Use error handling with On Error GoTo statements.

Frequently Asked Questions:

How Do I Enable VBA in Access?

To enable VBA in Access, click on the File >> Options. Under the navigation pane, choose Trust Center. In a navigation pane, choose Macro Settings. Make sure that Trust access to a VBA project object model is checked.

How to Run A VBA Code Step by Step?

To run a VBA code step by step, follow the steps below:

• Press F5 to run your project.
• On a Developer tab, under the Code group, choose Visual Basic.
• Go to the Insert menu, click on the Module.
• Add the code to the new module & press F5.
• Verify that a new table was added to the document.
• Exit Word without saving your changes.

How Do I Activate A VBA Code?

You can easily activate a VBA interface by pressing the ALT + F11 keyboard shortcut. Otherwise, you can go to the Developer tab & click on Visual Basic.

Final Thoughts

Running VBA code in MS Access is simple once you know the process. Whether you use the VBA editor, macros, or buttons, you can automate tasks with ease. Start with the small procedures mentioned above in this post and expand your knowledge step by step. With these practices, you will unlock the full power of Microsoft Access.