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. Then this post will surely gonna help you to get a lot more information regarding how to run VBA code in Access
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?
- 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:
Here are some basic reasons why you need to use MS Access VBA code areas follows.
- Manage smaller re-usable procedures (globally)
- Create your own defined functions
- in memory hold variables (globally)
- Attach code to an event i.e. a button on a form
- Manage more powerful procedures that an Access macro lacks
- Easily wind up with other applications and platforms
- 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 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:
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 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:
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.
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 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
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:
- Early binding
- 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.
- Go to the VBA editor window, and tap to the “Tools” button present in menu bar.
- Now from the drop-down list, choose the “References” option.
- Afterward, the “References – Project 1” dialog box will appear.
- In this dialog box, you can drag the scroll bar until you fetch what you want like “Microsoft Access14.0 Object Library”.
- Now put a mark in the checkbox present in front of this object.
- At last, tap on the “OK” option.
- 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:
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
- 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 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
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…..!
This software repairs & restores all ACCDB/MDB objects including tables, reports, queries, records, forms, and indexes along with modules, macros, and other stuffs effectively.
- Download Stellar Repair for Access rated Great on Cnet (download starts on this page).
- Click Browse and Search option to locate corrupt Access database.
- Click Repair button to repair & preview the database objects.