How To Run VBA Code In Access

Undoubtedly, creation of database in Access with several objects like reports, form, tables are quite easy. But when it comes to add some programming for simultaneous execution of database objects or to automate certain processes. Then this can be daunting task to perform…. isn’t it?

Mainly for those who are not having much knowledge of program coding. if you are also among them then this post will surely gonna help you to get a lot more information regarding execution of VBA code in Access

Understand MS Access VBA Code With Example:

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 into 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, macro and procedure will automatically run in the background. Thus, clicking on the form’s command button form will open the report.

So, all an 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?

What Is The VBA Editor

  • Well for the programming in Access we use the VBA editor. For starting the VBA editor, firstly you need to 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 that there are several parts in this VBA editor. So, let’s discuss about each section in detail:

  • the upper left corner is called Project Window. This contains listing 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 screen 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 result of various commands. Immediate window is very useful during testing and debugging procedure.

Benefits Of Using VBA Code In Access:

Benefits Of Using Vba Code In Access

Here are some basic reasons why you need to use MS Access VBA code areas 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 

How To Run VBA Code In MS Access 

VBA Code To Run A Query In MS Access

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

You can use the VBA syntax to run 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 contain a query named “max_sales”.

So, here are the complete step 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:

VBA Code To Run Query In MS Access---1

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.

VBA Code To Run Query In MS Access---2

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.

VBA Code To Run Query In MS Access---3

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

VBA Code To Run Query In MS Access---4

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 below example (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:

VBA Code To Run Query In MS Access---5

Step 5: View the Results

After writing off these 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.

VBA Code To Run Query In MS Access---6

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 Query In MS Access---7

VBA Code To Run Report In Access

VBA Code To Run Report In Access

DoCmd.OpenReport method is used for opening report. You can use use this DoCmd.OpenReport method to open 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

How To Connect Access And Excel Through VBA

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

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 addition of reference to 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:

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 connection with Access database. Connection string used for access 2003(*.mdb) and Access 2007/2010 (*.accdb) is different because both the 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

  1. Open the VB Editor Screen by pressing Alt+F11
  2. Tools –> References…
  3. 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 higher version.
  4. 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

Wrap up:

If you understand the concept of how to run VBA code in Access under various circumstances then make a good start by using these techniques. don’t be afraid of experimenting…..!

How To Run VBA Code In Access 2007/2010/2013/2016/2019