Access VBA Macros

Start here

Macro to check if file exists or not

f you want to check if file is exists or not . Try this code-

 

Sub check_if_file_exits()

Dim filenm As String
filenm = “C:\Documents and Settings\user\Desktop\Access Tips & Tricks\sample.accdb”

If Dir(filenm) <> “” Then
MsgBox “File Exists”
Else
MsgBox “File Does Not Exists”
End If

End Sub

Advertisements

Macro to check if folder exists or not

If you want to check if folder is existing or already created or not . Try this code-

 

Sub check_if_directory_exits()

Dim dirname As String
dirname = “C:\Documents and Settings\user\Desktop\Access Tips & Tricks”

If Len(Dir(dirname, vbDirectory)) <> 0 Then
MsgBox “Folder Exists”
Else
MsgBox “Folder Does Not Exists”
End If
End Sub

Loop through all records of a table

Sub loop_all_records()
Dim I As Long
Dim rs As Recordset
‘ change sample as per ur table name
Set rs = CurrentDb.OpenRecordset(“sample”, dbOpenDynaset)
Do While Not rs.EOF
For I = 0 To rs.Fields.Count – 1
MsgBox rs(I)
Next
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

Macro to close all reports if open

Sub Close_all_open_report()
Dim rpt As Object
For Each rpt In Application.CurrentProject.AllReports
On Error Resume Next
DoCmd.Close acReport, rpt.Name, acSaveYes
Next
End Sub

Macro to close all queries if open

Sub close_all_queries()
Dim qry As DAO.QueryDef
For Each qry In CurrentDb.QueryDefs
On Error Resume Next
DoCmd.Close acQuery, qry.Name, acSaveYes
Next
End Sub

Macro to close all tables if open

Sub close_all_open_tables()
Dim tbl As DAO.TableDef
For Each tbl In CurrentDb.TableDefs
On Error Resume Next
DoCmd.Close acTable, tbl.Name, acSaveYes
Next
End Sub

Macro to close all forms if open

Sub close_all_open_forms()

Dim obj As AccessObject
For Each obj In Application.CurrentProject.AllForms
‘ choose acSaveyes if you want to say yes
DoCmd.Close acForm, obj.Name, acSaveNo
Next obj

End Sub