Summary:
This tutorial helps orient you to the programming tools i.e, VBA code in Microsoft Access and point you to some resources where you can learn more about programming.
You must have used Access macros and very well know how much complex it is to work with.…..isn’t it? But do you know you can simplify this complexity just by making use of VBA codes in Access.
Now you will ask what this VBA code is and how to use it? Well to get answers of such questions you just need to get stick to this post and get the complete info and simplify your working style in MS Access.
Introduction To Access VBA
VBA is a programming language that you can use to create powerful application in Access. VBA includes numerous of commands that allows you to perform much complex operations that is quiet tougher to do by using the Access macros.
Not only this, you can extend the power of VBA by using the third party controls and writing your own functions/ procedures for own specific needs. You can also use the VBA code to integrate your access with other programs too.
Programming Microsoft Access with VBA can be a lot easier if you know the keyboard shortcuts for the most common commands and the most common bits of code that you will use in the editor after then debug your Access application.

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.
How To Activate The Built-In VBA Functions in Access
Do you know you can activate the built in VBA functions in your Access 2013/2016 application. Wants to know how then follow these step by step instructions.
In order to use the built in VBA functions in your access data base , you will need to turn on the visual basic reference called “Visual Basic For Applications”.
- For this, you just need to open the database. Press Alt+F11 to open the Microsoft Visual Basic When the Microsoft Visual Basic window appears, select References under the Tools menu.
- When the Referenceswindow appears, look for the reference called Visual Basic for Applications and make sure that it is checked.
- After then click on the OK button.
- Now your access database is able to use the built-in VBA functions.
Common Access VBA Programming And Debugging Code
Here is the list of common VBA code that helps you in performing VBA task completed, so keep it handy.
To Open A Form In Form View:
DoCmd.OpenForm “formname“,acNormal
To Make Changes To The Form Property On An Open Form:
Forms![formName].propertyName = newValue
Get value from a control on an open form:
Forms![formName]![controlName].Value
Change value of a control on an open form:
Forms![formName]![controlName].Value = newValue
Change a control property on an open form:
Forms![formName]![controlName].propertyName = newValue
Close a form, saving changes:
DoCmd.Close acForm, “formName“, acSaveYes
Print a report:
DoCmd.OpenReport “reportName“, acViewNormal
Run an action query:
DoCmd.RunSQL “SQLstatement“
To display a simple message
MsgBox “yourMessage“
To ask yes/no question onscreen.
variable = MsgBox(“yourMessage“, vbQuestion + vbYesNo)
ALSO READ:
How To Fix MS Access Reserved Error 7713, 7748, 7711 In Access 2016/ 2013/2010/2007
Navigating And Selecting Code In Vba
You can use theses VBA shortcuts in the code window and the immediate window (that let you run code any time, right on the spot) to simplify your working style.
Action | Shortcut Key |
Move cursor right one character | → |
Select character to right | Shift+→ |
Move cursor right one word | Ctrl+→ |
Select to end of word | Ctrl+ Shift+→ |
Move cursor left one character | ← |
Select character to left of cursor | Shift+← |
Move cursor left one word | Ctrl+← |
Move cursor to start of line | Home |
Select text to start of line | Shift+Home |
Move cursor to end of line | End |
Select text to end of line | Shift+End |
Move cursor up a line | ↑ |
Move cursor down a line | ↓ |
Move cursor to next procedure | Ctrl+↓ |
Move cursor to previous procedure | Ctrl+↑ |
Scroll up one screen | PgUp |
Scroll down one screen | PgDn |
Go to top of module | Ctrl+Home |
Select all text to top of module | Ctrl+ Shift+Home |
Go to bottom of module | Ctrl+End |
Select all text to bottom of module | Ctrl+ Shift+End |
Cut selection | Ctrl+X |
Copy selection | Ctrl+C |
Paste selection | Ctrl+V |
Cut current line to Clipboard | Ctrl+Y |
Delete to end of word | Ctrl+Delete |
Delete character or selected text | Delete (Del) |
Delete character to left of cursor | Backspace |
Delete to end of word | Ctrl+Delete |
Delete to beginning of word | Ctrl+Backspace |
Undo | Ctrl+Z |
Indent line | Tab |
Outdent line | Shift+Tab |
Find | Ctrl+F |
Replace | Ctrl+H |
Find Next | F3 |
Find Previous | Shift+F3 |
Get help with currently selected word | F1 |
Quick Info | Ctrl+I |
General VBA Editor Shortcut Keys
The following table will give you the knowledge of the shortcut keys and their functions available throughout the Visual Basic editor, where you will write , modify and run your VBA code.
Action | Shortcut Key |
Switch between Access and the VBA editor | Alt+F11 |
Help | F1 |
View Object Browser | F2 |
View properties/events | F4 |
View Code window | F7 |
View Immediate window | Ctrl+G |
View shortcut menu | Shift+F10 (or right-click) |
Run a Sub/UserForm | F5 |
Stop code execution | Ctrl+Break |
Close VBA Editor and return to Access | Alt+Q |
VBA Debugging Shortcut Keys
Code in programming language has to be written exactly, there can be no errors or bugs. If there is any error or bugs then it will show compiling issue. Fortunately, you can debug code in VBA quickly with these debugging shortcuts.
Action | Shortcut Key |
Toggle breakpoint | F9 |
Step into | F8 |
Step over | Shift+F8 |
Step out | Ctrl+Shift+F8 |
Run to cursor | Ctrl+F8 |
Clear all breakpoints | Ctrl+F9 |
Important Access VBA Code Along with the Examples
ChangeLinkPath
This function is mainly used to change the old path for the linked tables to a new path. The function does this by dropping the linked tables and then relinking it to the same table in the database specified in the new path.
Public Function ChangeLinkPath(strNewPath As String) As String
Dim dbs As DAO.Database
Dim strTblName As String
Dim colTbl As Collection
Dim intTbl As IntegerIf strNewPath <> “” And Dir(strNewPath) <> “” Then
Set colTbl = New Collection
Set dbs = CurrentDbFor intTbl = dbs.TableDefs.Count – 1 To 0 Step -1
If dbs.TableDefs(intTbl).Connect <> “” And _
Not dbs.TableDefs(intTbl).Connect Like “*” & strNewPath Then
colTbl.Add dbs.TableDefs(intTbl).Name
dbs.TableDefs.Delete dbs.TableDefs(intTbl).Name
End If
Next intTblFor intTbl = colTbl.Count To 1 Step -1
strTblName = colTbl(intTbl)
DoCmd.TransferDatabase acLink, “Microsoft Access”, _
strNewPath, acTable, strTblName, strTblName
Debug.Print “connection made to ‘” & strTblName & “‘”
Next intTblSet dbs = Nothing
Set colTbl = Nothing
Debug.Print “DONE!”
ChangeLinkPath = “DONE!”Else
Debug.Print “New path not provided. No changes made!”
ChangeLinkPath = “New path not provided. No changes made!”End If
Exit Function
End Function
ImportExportSpecification
This section of the Access VBA contains documentation for all the objects, properties, methods, and events contained in the access object model.
ImportExportSpecification object contain all the information that access needs to repeat an import/export operation without providing any input.
You have to Use the Add method of the ImportExportSpecifications collection to create a new ImportExportSpecification object. Use the Execute method to run saved import or export operation.
ALSO READ:
5 Fixes To Repair Access Database That Is In An Inconsistent State
ConcatenateFieldValues
Well this function is based on a function name concatenate. It is used to create a concatenated string from a single field’s values from the entire records desired to be included in the concatenated string.
The records to be included, plus the field to be used for creating the concatenated string, are given by the SQL statement that is passed to the function.
Passed SQL statement must return only one field in order for this function to work correctly. The value in the concatenated string is given by the string that is provided to the function as a delimiter string.
Public Function ConcatenateFieldValues(pstrSQL As String, _
Optional pstrDelim As String = “, “) As StringDim strConcat As String
‘======For ADO comment next 2 lines and =======
‘====== uncomment out ADO lines below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
On Error Resume Next
strConcat = “”
‘======For ADO comment out next 2 DAO lines and =======
‘====== uncomment ADO lines below =======
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)‘======For ADO uncomment next two lines and =====
‘====== comment out DAO lines above and below ======
‘ Dim rs As New ADODB.Recordset
‘ rs.Open pstrSQL, CurrentProject.Connection, _
‘ adOpenKeyset, adLockOptimistic
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
‘====== Comment next 2 lines for ADO ========
db.Close
Set db = Nothing
If Len(strConcat) > 0 Then strConcat = _
Left(strConcat, Len(strConcat) – Len(pstrDelim))
ConcatenateFieldValues = strConcat
Exit Function
End Function
DeleteAllRelationships()
DeleteAllRelationships() function code is basically used to delete all relationships in the current database.
In some circumstances, access database fails to display relations between tables in the relationship window. Since you could not view the relationship nor you could not delete them, which will take your database to an inconsistent state. So for such cases , this code is provided as a fix.
Function DeleteAllRelationships() As String
‘ WARNING: Deletes all relationships in the current database.
Dim db As Database ‘ Current DB
Dim rex As Relations ‘ Relations of currentDB.
Dim rel As Relation ‘ Relationship being deleted.
Dim iKt As Integer ‘ Count of relations deleted.
Dim sMsg As String ‘ MsgBox string.sMsg = “About to delete ALL relationships between tables in the current database.” & vbCrLf & “Continue?”
If MsgBox(sMsg, vbQuestion + vbYesNo + vbDefaultButton2, “Are you sure?”) = vbNo Then
DeleteAllRelationships = “Operation cancelled”
Exit Function
End IfSet db = CurrentDb()
Set rex = db.Relations
iKt = rex.Count
Do While rex.Count > 0
Debug.Print rex(0).Name
rex.Delete rex(0).Name
Loop
DeleteAllRelationships = iKt & ” relationship(s) deleted”
End Function
Just copy and paste this function into a module. Press Ctrl+G to open the Immediate Window. Enter:
? DeleteAllRelationships()
Conclusion:
Hopefully, you all must have grabbed much knowledge on Access VBA code. EXPLORE YOUR IDEAS ON PROGRAMMING WITH ACCESS….!

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.