access automation

7/22/2019 Access Automation

1/28

This is a compilation of functions to show how to create, edit, execute

and delete the main access objects such as tables, views, forms, reports,

modules and classes through automation.


2/28

Created by Estuardo Sierra [email protected]: http://www.accessextended.com 1 | P a g e

Table of Contents

Introduction .................................................................................................................................. 2

The Basics ...................................................................................................................................... 3Application Object ..................................................................................................................... 3

Objects Enumeration..................................................................................................................... 3

Tables, Views, Forms, Reports, Modules and References ........................................................ 3

Enumerate all tables.............................................................................................................. 4

Enumerate all Views .............................................................................................................. 4

Enumerate all Forms ............................................................................................................. 5

Enumerate all Reports ........................................................................................................... 5

Enumerate All Modules ......................................................................................................... 5Enumerate all References ..................................................................................................... 6

Add Reference from File ........................................................................................................ 6

Delete Reference ................................................................................................................... 6

Objects in Depth ............................................................................................................................ 7

Tables ........................................................................................................................................ 7

Create, Edit, Set relationships and Delete Tables ................................................................. 7

Views ....................................................................................................................................... 11

Create, execute and Delete ................................................................................................. 11

Forms & Reports...................................................................................................................... 13

Create, Edit, Open and Delete ............................................................................................. 13

Modules & Class Modules ....................................................................................................... 19

Enumerate all Modules ....................................................................................................... 20

Create Module ..................................................................................................................... 20

Create Class ......................................................................................................................... 21

Edit Module/Class ............................................................................................................... 22

Delete module ..................................................................................................................... 23

Useful Functions: ................................................................................................................. 23

Document References ............................................................................................................. 27
mailto:[email protected]://www.accessextended.com/http://www.accessextended.com/mailto:[email protected]


3/28


Introduction

This document is intended to be a quick guide to automation; you wont find detailed

explanations of any kind, or any reference any object model. This is not a cookbook either; you

wont find code to address any specific solution.

What you will find here are the basics, the foundation to let you start right away working on

any of your automation projects stored in the shelves due to lack of documentation. If you

ever asked any of this questions: How to automate MS Access? Where can I start to search foraccess automation documentation? How to create a form, report, module, class with

automation? This document will help you.

One of reasons to make this document is to save you from the tedious, boring and frustrating

waste of time to find just the bits needed to perform a given task.

The second reason is to give you another approach to automate Access. If you have never

worked with the Microsoft Visual Basic For Applications Extensibility 5.3 library there is no

better place to start to work than Automation.

I hope this document achieve those objectives and help you build better applications.
mailto:[email protected]://www.accessextended.com/http://www.accessextended.com/mailto:[email protected]


4/28


The Basics

Application Object

The Application object is the top-level object in the Microsoft Access object model. It

provides properties and methods you can use to create and work with other Access objects. It

also provides several built-in functions you can use to work with the objects in your database.

In essence, the Application object serves as the gateway to all other Access objects.

Objects Enumeration

Tables, Views, Forms, Reports, Modules and References

All these objects belong to collections. To properly enumerate them you need to set a

reference to its collection.

To Enumerate Tables and Views, most of the times examples are with the DAO object, here Ill

do it with ADOX (Microsoft ADO Ext x.x for DDL and Security)

Before start, for all the examples well be using the following two Procedures and local

variables. The cp Variable is your gateway to automation.

Privatecp AsNewAccess.ApplicationPrivateConstmstrDbPath AsString ="C:\Northwind.accdbPrivatemintTotalLines AsIntegerPrivateConstmstrCon AsString="Provider=Microsoft.ACE.OLEDB.12.0;UserID=Admin;Data Source=C:\Northwind.accdb;"

PublicSubConnect()OnErrorGoToHandler

Withcp.VBE.MainWindow.Visible = False.OpenCurrentDatabase mstrDbPath,True.Application.DoCmd.Minimize

EndWith

ExitHere:ExitSubHandler:MsgBox Err.Number &VBA.vbCrLf &Err.DescriptionResumeExitHereEndSubPrivateSubCloseDb()

OnErrorResumeNextIfNot(cp IsNothing)Thencp.CloseCurrentDatabasecp.Application.QuitSetcp =Nothing

EndIfEndSub
mailto:[email protected]://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/mailto:[email protected]


5/28


Enumerate all tables

Enumerate all ViewsBecause Views are virtual Tables the procedure is exactly the same.

PrivateSubGetAllTables()DimcatDB AsADOX.CatalogDimtbl AsADOX.Table

SetcatDB =NewADOX.Catalog

catDB.ActiveConnection =mstrCon

ForEachtbl IncatDB.TablesIftbl.Type"VIEW"Andtbl.Type"SYSTEM TABLE"Then

Debug.Print tbl.Name &vbTab &tbl.TypeEndIf

Next

SetcatDB =NothingEndSub

PrivateSubGetAllTables()DimcatDB AsADOX.CatalogDimqry AsADOX.Table

SetcatDB =NewADOX.Catalog

catDB.ActiveConnection =mstrCon

ForEachqry IncatDB.TablesIfqry.Type ="VIEW"Then

Debug.Print qry.Name &vbTab &qry.Type

EndIfNext

mailto:[email protected]://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/mailto:[email protected]


6/28


Enumerate all Forms

Enumerate all Reports

Enumerate All Modules

PrivateSubGetAllForms()

OnErrorGoToErrHandlerDimofrm AsObjectCallConnect

ForEachofrm Incp.CurrentProject.AllFormsDebug.Print ofrm.Name

Next

ExitHere:CallCloseDbExitSubErrHandler:MsgBox Err.Number &VBA.vbCrLf &Err.DescriptionResumeExitHereEndSub

PrivateSubGetAllReports()Dimrpt AsObjectCallConnect

ForEachrpt Incp.CurrentProject.AllReportsDebug.Print rpt.Name

NextCallCloseDb

EndSub

PrivateSubGetAllModules()

OnErrorGoToErrHandlerDimMdl AsObject

CallConnect

ForEachmdl Incp.CurrentProject.AllModulesDebug.Print "Module/Class Name "&mdl.Name

Next

ExitHere:

CallCloseDbExitSubErrHandler:MsgBox Err.Number &VBA.vbCrLf &Err.DescriptionResumeExitHereEndSub
mailto:[email protected]://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/mailto:[email protected]


7/28


Enumerate all References

Add Reference from File

Delete Reference

PrivateSubGetAlRefereces()

Dimref AsReferenceCallConnect

ForEachref Incp.ReferencesDebug.Print ref.Name

NextCallCloseDb

EndSub

PrivateSubReferenceFromFile()OnErrorGoToErrHandlerDimref AsReferenceCallConnectcp.References.AddFromFile ("C:\Program Files\Common

Files\System\ado\msadox.dll")'Microsoft ADO.Ext 6.0 for DLL andSecurity

ExitHere:Setref =NothingExitSub

ErrHandlerMsgBox "Exception id "&Err.Number &VBA.vbCrLf &Err.Description,vbCriticalResumeExitHere

PrivateSubDeleteReference()OnErrorGoToErrHandlerDimref AsReference

CallConnectSetref =cp.References("C:\Program Files\CommonFiles\System\ado\msadox.dll")References.Remove ref

ExitHere:Setref =NothingExitSub

ErrHandler

MsgBox "Exception id "&Err.Number &VBA.vbCrLf &Err.Description,vbCriticalResumeExitHereEndSub


8/28


Objects in Depth

Tables

Create, Edit, Set relationships and Delete Tables

The following snippets show how to create a new table, set special properties to different

columns, creates an index as primary, modify a column, delete a column and create a

relationship between two tables.

Create Table

Continue.

PrivateSubCreateAccessTable()Dimcat AsNewADOX.CatalogDimtbl AsADOX.TableDimidx AsADOX.Index

cat.ActiveConnection =mstrCon

Settbl =NewADOX.TableSetidx =NewADOX.Index

tbl.Name ="tblCustomers"

'Append the columns.Withtbl.Columns

.Append "PKCustomerID",adInteger 'Long Integer

.Append "LastName",adVarWChar,20 'Size 20)

.Append "FirstName",adVarWChar,20'Size 20)

.Append "RegistrationDate",adDate 'Date/Time

.Append "MonthlyFee",adCurrency 'Currency

.Append "Notes",adLongVarWChar 'Memo

.Append "CustomerURL",adLongVarWChar 'Hyperlink

.Append "Inactive",adBoolean 'True/False

Prepare Primary key.'AutoNumber, Seed and Description

With.Item("PKCustomerID")Set.ParentCatalog =cat.Properties("Autoincrement")=True 'AutoNumber..Properties("Seed")=CLng(10)'Set a seed.Properties("Increment")=CLng(10)'Increment by 10.Properties("Description")="Primary Key."

EndWith

'Set this column as PrimaryKeyWithidx

.Name ="PrimaryKey"

.PrimaryKey =True.Columns.Append ("PKCustomerID")tbl.Indexes.Append idx

EndWith
mailto:[email protected]://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/mailto:[email protected]


9/28


'Required field.With.Item("LastName")

Set.ParentCatalog =cat.Properties("Nullable")=False 'Required..Properties("Jet OLEDB:Allow Zero Length")=False

EndWith

'Set a validation rule.With.Item("RegistrationDate")

Set.ParentCatalog =cat.Properties("Jet OLEDB:Column Validation Rule")=_

"Is Null Or >Date()".Properties("Jet OLEDB:Column Validation Text")=_

"Registration date cannot be future."EndWith

'Hyperlink field.With.Item("CustomerURL")

Set.ParentCatalog =cat.Properties("Jet OLEDB:Hyperlink")=True'Hyperlink.

EndWithEndWith

cat.Tables.Append tblDebug.Print "Table "&tbl.Name;" created on remote db"

Settbl =NothingSetcat =NothingSetidx =Nothing

EndSub


10/28


Create Linked Table

Edit Table

PrivateSubCreateLinkedTable(strDBLinkFrom AsString,_strDBLinkTo AsString,_strLinkTbl AsString,_strLinkTblAs AsString)

DimcatDB AsNewADOX.Catalog

DimtblLink AsADOX.Table

' Open a Catalog on the database in which to create the link.catDB.ActiveConnection =mstrCon

SettblLink =NewADOX.TableWithtblLink

' Name the new Table and set its ParentCatalog property to the' open Catalog to allow access to the Properties collection..Name =strLinkTblAsSet.ParentCatalog =catDB

' Set the properties to create the link.

.Properties("Jet OLEDB:Create Link")=True

.Properties("Jet OLEDB:Link Datasource")=strDBLinkTo

.Properties("Jet OLEDB:Remote Table Name")=strLinkTblEndWith

' Append the table to the Tables collection.catDB.Tables.Append tblLink


PrivateSubEditTableColum()'WARNING: You cannot change the Defined Size of a field in this way.Dimcat AsNewADOX.Catalog

'Rename ColumnWithcat

.ActiveConnection =mstrCon

.Tables("tblCustomers").Columns("Inactive").Name ="IsActive"' To delete a colum'.Tables("tblCustomers").Columns.Delete "IsActive"

EndWith

Setcat =Nothing

EndSub


11/28


Create Relationship

Delete Table

PrivateSubCreateTableRelationship()Dimcat AsNewADOX.CatalogDimtbl AsADOX.TableDimFK AsNewADOX.Key

Setcat.ActiveConnection =mstrConSettbl =cat.Tables("tblOrders")

'Create as foreign keyWithFK

.Type=adKeyForeign

.Name ="tblAdoxContractortblAdoxBooking"

.RelatedTable ="tblAdoxContractor"

.Columns.Append "PKCustomerID"

.Columns("FKCustomerID").RelatedColumn ="PKCustomerID"

.DeleteRule =adRICascade 'Cascade Update. PossibleValues: adRINone adRICascade adRISetNull adRISetDefault

EndWith

tbl.Keys.Append FK

SetFK =NothingSettbl =NothingSetcat =Nothing

EndSub

PrivateSubDeleteTableAdox()Dimcat AsNewADOX.Catalog

cat.ActiveConnection =mstrConcat.Tables.Delete "tblCustomers"Setcat =Nothing

EndSub


12/28


Views

Create, execute and Delete

Views as virtual tables share many properties with tables; the main difference is the use of

SQL, which makes a lot easier to work with them. ADOX distinguish two types of Views. TheNo parameterized, known as View, and the parameterized known as Procedure

Create View

Execute View

PrivateSubCreateView()Dimcat AsNewADOX.CatalogDimcmd AsNewADODB.CommandDimstrSql AsString


'Build the SQL Text.

strSql ="SELECT * FROM tblCustomers;"cmd.CommandText =strSqlcat.Views.Append "qryListCustomers",cmd

Setcmd =NothingSetcat =Nothing

EndSub

PrivateSubExecuteView()

Dimcat AsNewADOX.CatalogDimcmd AsADODB.CommandDimintCount AsInteger

cat.ActiveConnection =mstrConSetcmd =cat.Views("qryListCustomers").Command

cmd.Execute intCountDebug.Print intCount &" record(s) Found."


EndSub
mailto:[email protected]://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/mailto:[email protected]


13/28


Create Procedure

Execute Procedure

Delete View/Procedure

PrivateSubCreateProcedure()Dimcat AsNewADOX.CatalogDimcmd AsNewADODB.CommandDimstrSql AsString


'Build the parameterized procedure.strSql ="PARAMETERS CustID Long; SELECT * FROM tblCustomers

WHERE PKCustomerID=CustId;"cmd.CommandText =strSqlcat.Procedures.Append "qryCustomerInfo",cmd


EndSub

PrivateSubExecuteProcedure()Dimcat AsNewADOX.CatalogDimcmd AsADODB.CommandDimintCount AsInteger

cat.ActiveConnection =mstrConSetcmd =cat.Procedures("qryCustomerInfo").Command

cmd.Parameters("CustID")=10 'Set parametercmd.Execute intCount

Debug.Print intCount &" record(s) Found"


EndSub

PrivateSubDeleteView()Dimcat AsNewADOX.CatalogDimcmd AsADODB.Command

cat.ActiveConnection =CurrentProject.Connectioncat.Procedures.Delete "qryCustomerInfo"cat.Views.Delete "qryListCustomers"Setcat =Nothing

EndSub


14/28


Forms & Reports

Create, Edit, Open and Delete

Create a form/report

This is kind of undocumented procedure. Almost every example show how to do something

with an existing object, but no how to create one. The following code shows how to create a

form and set its main properties. This form will have 3 controls; two labels and one image, plus

two events (Load Event and Timer Event).

Note that the variable strOldName is to store the automatic name given to the New form by

access (usually something like form1, form2 etc.), just after the form is actually saved with this

name is possible to change it.

PrivateSubCreateForm()OnErrorGoToHandler

DimstrOldName AsStringDimmdl AsAccess.ModuleDimf AsAccess.Form

CallConnect

Withcp This the core procedure.DoCmd.RunCommand acCmdNewObjectBlankFormstrOldName =.Forms(.Forms.Count -1).Name.DoCmd.Save Access.AcObjectType.acForm,strOldName.DoCmd.Close Access.AcObjectType.acForm,strOldName,

Access.AcCloseSave.acSaveNo.DoCmd.Rename "frmMyForm",Access.AcObjectType.acForm,

strOldNameEndSub


15/28


Open the form hidden in design view and get its module

cp.DoCmd.OpenForm "frmMyForm",Access.AcFormView.acDesign,WindowMode:=Access.AcWindowMode.acHidden

Setf =cp.Forms("frmMyForm")CallToolTipFormProperties(f)Set forms Properties

Setmdl =f.Modulemdl.InsertText AddCodeBehind()Set forms code behind

.DoCmd.Save Access.AcObjectType.acForm,"frmMyForm"EndWith

ExitHere:CallCloseDbExitSub

Handler:

MsgBox Err.Number &VBA.vbCrLf &Err.DescriptionResumeExitHere


16/28


Set forms properties

Continue.

PrivateSubToolTipFormProperties(frm AsAccess.Form)Dimlbl AsAccess.LabelDimimg AsAccess.ImageDimlbl2 AsAccess.LabelDimstrStartUpPath AsString

frm.Visible =FalseWithfrm

.DefaultView =0 'Single Form

.ViewsAllowed =1 ' Form

.ScrollBars =0 ' neither

.RecordSelectors =False

.NavigationButtons =False

.DividingLines =False

.AutoResize =True

.AutoCenter =False

.BorderStyle =0 ' None

.ControlBox =False

.MinMaxButtons =0 ' None

.CloseButton =False.Cycle =1 ' Current record

.GridX =5

.GridY =5

.PopUp =True

.Picture ="...\Resources\ToolTip\Themes\green.jpg"

.PictureType =1 ' 0=Embedded 1=Linked

.PictureSizeMode =1 '0=Clip '1=Stretch 3=Zoom

.InsideWidth =3255

.InsideHeight =2490

.Width =3260

.Section(0).Height =2506 'Detail Section

.TimerInterval =3000

.OnLoad = "[Event Procedure]"

.OnTimer = "[Event Procedure]"OnErrorResumeNext.Form.Section(1).Visible =False 'Header Section.Form.Section(2).Visible =False 'Footer Section

EndWith

cp.DoCmd.Save acForm,"frmMyForm"


17/28


Finally add the controls and code behind.

The code shows how to create a control set some properties and place it on the form. The last

part shows how to add the code-behind to the forms module.

lbl =cp.CreateControl(frm.Name,Access.AcControlType.acLabel,Access.AcSection.acDetail,Left:=945,Top:=90,Width:=2160,Height:=735)

Withlbl

.Name ="lblTipTitle"

.Caption ="Title Here"

.BackStyle =0EndWith

img =cp.CreateControl(frm.Name,Access.AcControlType.acImage,Access.AcSection.acDetail,Left:=60,Top:=75,Width:=810,Height:=735)

img.SizeMode =1 '1=Stretchimg.Name ="imgToolTip"img.PictureType =1 ' 1=Linked

lbl2 =cp.CreateControl(frm.Name,Access.AcControlType.acLabel,Access.AcSection.acDetail,Left:=88,Top:=881,Width:=3047,

Height:=1507)Withlbl2.Name ="lblTipMessage".Caption ="Message Here".BackStyle =0

EndWithcp.DoCmd.Save acForm,"frmMyForm"


18/28


This function returns the formatted text for the code behind

Forms and reports share the same model. Before create a form, make sure that any public

object such as function, sub, class, enum or type to be called from code behind must exist

before saving the form, otherwise the automation process will break.

The code behind the Load event sets several properties to the controls. Those values come

from a class. That class should exist before the code behind automation.

PrivateFunctionAddCodeBehind()AsStringDimsb AsString

sb =sb &"Private Sub Form_Load()"sb =sb &""&VBA.vbCrLf

sb =sb &" With MytoolTip"&VBA.vbCrLfsb =sb &" Me.lblTipTitle.Caption = .Title"&VBA.vbCrLfsb =sb &" Me.lblTipMessage.Caption = .Msg"&VBA.vbCrLfsb =sb &" Me.imgToolTip.Picture = .ImagePath"&

VBA.vbCrLfsb =sb &" If VBA.Len(.SetTheme & vba.vbCrLf < 2 Then

.ThemeName = Green"&VBA.vbCrLfsb =sb &" Me.Picture = .SetTheme"&VBA.vbCrLfsb =sb &" Me.Move .X, .Y"&VBA.vbCrLfsb =sb &" Me.lblTipMessage.FontSize = .SetFontSize"&

VBA.vbCrLfsb =sb &" End With"&VBA.vbCrLfsb =sb &""&VBA.vbCrLfsb =sb &"End Sub"&VBA.vbCrLf

sb =sb &""&VBA.vbCrLfsb =sb &""&VBA.vbCrLfsb =sb &"Private Sub Form_Timer()"&VBA.vbCrLfsb =sb &" MytoolTip.Clear"&VBA.vbCrLfsb =sb &" If pfrmMyForm Is Nothing Then"&VBA.vbCrLfsb =sb &" Access.DoCmd.Close acForm, Me.Name"&

VBA.vbCrLfsb =sb &" Else"&VBA.vbCrLfsb =sb &" Set pfrmMyForm = Nothing"&VBA.vbCrLfsb =sb &" End If"&VBA.vbCrLfsb =sb &"End Sub"&VBA.vbCrLfsb =sb &""&VBA.vbCrLf

FormToolTipCode =sbEndFunction


19/28


Edit

Just same as above skipping the creation part

Delete

Setf =cp.Forms("frmMyForm")f.PopUp =Truecp.DoCmd.Save acForm,"frmMyForm"

OnErrorResumeNext'Just to make sure the form is closed before deletioncp.DoCmd.Close Access.AcObjectType.acForm,"frmMyForm"c .DoCmd.DeleteOb ect Access.AcOb ectT e.acForm,"frmM Form"


20/28


Modules & Class Modules

Methods

Name Description

AddFromFile The AddFromFilemethod adds the contents of a text file to a Moduleobject.

TheModuleobject may represent a standard module or a class module.

AddFromString The AddFromStringmethod adds a string to a Moduleobject. The Moduleobjectmay represent a standard module or a class module.

CreateEventProc The CreateEventProcmethod creates an event procedure in a class module.

DeleteLines The DeleteLinesmethod deletes lines from a standard module or a class module.

Find Finds specified text in a standard module or class module.

InsertLines The InsertLinesmethod inserts a line or group of lines of code in a standard

module or a class module.

InsertText The InsertTextmethod inserts a specified string of text into a standard module or

a class module.

ReplaceLine The ReplaceLinemethod replaces a specified line in a standard module or a class

module.

Properties

Name Description

CountOfDeclarationLines The CountOfDeclarationLinesproperty returns a Longvalue indicating

the number of lines of code in the Declarations section in a standard

module or class module. Read-only Long.

CountOfLines The CountOfLinesproperty returns a Longvalue indicating the number

of lines of code in a standard module or class module. Read-only Long.

Lines The Linesproperty returns a string containing the contents of a specified

line or lines in a standard module or a class module. Read-only String.

Name You can use the Nameproperty to specify or determine the stringexpression that identifies the name of an object. Read/write String.

Parent Returns the parent object for the specified object. Read-only.

ProcBodyLine The ProcBodyLineproperty returns the number of the line at which the

body of a specified procedure begins in a standard module or a class

module. Read-only Long.

ProcCountLines The ProcCountLinesproperty returns the number of lines in a specified

procedure in a standard module or a class module. Read-only Long.

ProcOfLine The ProcOfLineproperty returns the name of the procedure that

contains a specified line in a standard module or a class module. Read-

only string.

ProcStartLine The ProcStartLineproperty returns avalue identifying the line at which a

specified procedure begins in a standard module or a class module.Read-only Long.

Type Indicates whether a module is a standard module or a class module.

Read-only AcModuleType.

Source: http://msdn.microsoft.com/en-us/library/office/ff823029.aspx
mailto:[email protected]://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://msdn.microsoft.com/en-us/library/office/ff823029.aspxhttp://www.accessextended.com/mailto:[email protected]


21/28


Enumerate all Modules

To really enumerate all modules, the AllModules is not enough, like the command says it will

get all modules not the code behind modules such as those from forms and reports. A solution

would be to read from each one of those collections but that would be too lengthy

Create Module

PrivateSubGetAllModules()

OnErrorGoToHandlerDimCodeDoc AsVBComponent

CallConnect

ForEachCodeDoc Incp.VBE.ActiveVBProject.VBComponentsDebug.Print "Object "&CodeDoc.Name

NextVBComp


Handler:MsgBox Err.Number &VBA.vbCrLf &Err.Description

ResumeExitHereEndSub

PrivateSubCreateModule()OnErrorGoToHandler

Dimmdl AsNewVBComponent

CallConnectSetmdl =cp.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule)

' Or the wizhook way

'Set mdl=cp.WizHook.DbcVbProject.VBComponents.Add(vbext_ct_StdModule)

Withmdl.Name ="modAutomated"cp.DoCmd.Save acModule,mdl.Name

EndWith


Handler:MsgBox Err.Number &VBA.vbCrLf &Err.DescriptionResumeExitHere

EndSub


22/28


Create Class

Notice that the only difference between create a module and a class module are the constants:

vbext_ct_StdModule and vbext_ct_ClassModule.

If you try the straight forward way with the wizhook, those constants wont appear unless you

have referenced the Microsoft Visual Basic For Applications Extensibility 5.3library

PrivateSubCreateClassModule()OnErrorGoToHandler

Dimmdl AsNewVBComponent

CallConnectSetmdl =cp.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_ClassModule)

' Or the wizhook way'Set mdl

=cp.WizHook.DbcVbProject.VBComponents.Add(vbext_ct_ClassModule)Withmdl

.Name ="clsAutomated"cp.DoCmd.Save acModule,mdl.NameDebug.Print .VBE.SelectedVBComponent.Saved

EndWith


Handler:MsgBox Err.Number &VBA.vbCrLf &Err.Description

ResumeExitHereEndSub


23/28


Edit Module/Class

PrivateFunctionEditModule(strModuleName AsString,_strSearchText AsString,_strNewText AsString)AsBoolean

Dimmdl AsModuleDimlngSLine AsLongDimlngSCol AsLong

DimlngELine AsLongDimlngECol AsLongDimstrLine AsStringDimstrNewLine AsStringDimintChr AsIntegerDimintBefore AsIntegerDimintAfter AsIntegerDimstrLeft AsStringDimstrRight AsString

'Code from MSDN http://msdn.microsoft.com/en-us/library/office/ff195471.aspx

DoCmd.OpenModule strModuleName

' Return reference to Module object.Setmdl =Modules(strModuleName)

' Search for string.Ifmdl.Find(strSearchText,lngSLine,lngSCol,lngELine,lngECol)Then

' Store text of line containing string.strLine =mdl.Lines(lngSLine,Abs(lngELine -lngSLine)+1)' Determine length of line.intChr =Len(strLine)' Determine number of characters preceding search text.intBefore =lngSCol -1' Determine number of characters following search text.intAfter =intChr -CInt(lngECol -1)' Store characters to left of search text.strLeft =Left$(strLine,intBefore)' Store characters to right of search text.strRight =Right$(strLine,intAfter)' Construct string with replacement text.strNewLine =strLeft &strNewText &strRight' Replace original line.mdl.ReplaceLine lngSLine,strNewLineFindAndReplace =True

ElseMsgBox "Text not found."FindAndReplace =False

EndIf

Exit_FindAndReplace:ExitFunction

Error_FindAndReplace:

MsgBox Err &": "&Err.DescriptionFindAndReplace =FalseResumeExit_FindAndReplace

EndFunction
mailto:[email protected]://www.accessextended.com/http://msdn.microsoft.com/enhttp://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://msdn.microsoft.com/enhttp://www.accessextended.com/mailto:[email protected]


24/28


Delete module

Useful Functions:Before start with this section you may want to run all your automation tasks in the background

do not forget to start your connection without this line (see page 3):

.VBE.MainWindow.Visible = False

Count module lines a better approach. Skips blank lines and comments

OnErrorResumeNextcp.DoCmd.DeleteObject Access.AcObjectType.acModule,"modToolTip"

PublicFunctionTotalCodeLines(CodeDoc AsVBIDE.VBComponent)AsLongDimi AsLongDimstrLeft AsStringDimintCodeLineCount AsInteger

IfCodeDoc.Collection.Parent.Protection =vbext_pp_lockedThen

TotalCodeLinesInVBComponent =-1ExitFunction

EndIf

WithCodeDoc.CodeModuleFori =1To.CountOfLines

strLeft =.Lines(i,1)IfNotVBA.Trim(strLeft)=vbNullString OrNot

VBA.Left(VBA.Trim(strLeft),1)="'"Then' To skip blank lines andcomments

intCodeLineCount intCodeLineCount +1

EndIfNextiEndWithTotalCodeLines =LineCount

EndFunction


25/28


Get all procedures from a given module

Returns Procedure Start Line

PrivateSubGetProcedures(mdl AsVBIDE.VBComponent)OnErrorGoToHandler

DimCodeMod AsVBIDE.CodeModuleDimintCurrentLine AsLongDimstrProcName AsString

DimProcKind AsVBIDE.vbext_ProcKind

CallConnectSetmdl =cp.VBE.ActiveVBProject.VBComponents("PurchaseOrders")SetCodeMod =mdl.CodeModule

WithCodeModintCurrentLine =.CountOfDeclarationLines +1DoUntilintCurrentLine >=.CountOfLines

strProcName =.ProcOfLine(intCurrentLine,ProcKind)Debug.Print strProcNameintCurrentLine =.ProcStartLine(strProcName,ProcKind)

+.ProcCountLines(strProcName,ProcKind)+1

LoopEndWith


Handler:MsgBox Err.Number &VBA.vbCrLf &Err.DescriptionResumeExitHere

EndSub

PrivateFunctionProcStartLine(strForm AsString,strProc AsString)as integer

ProcStartLine =cp.Forms(strForm).Module.ProcStartLine(strProc,vbext_pk_Proc)

EndSub


26/28


Get total lines in a project

Delete Procedure

PrivateFunctionTotalProjectLines()AsIntegerOnErrorGoToHandler

Dimmdl AsVBIDE.VBComponentDimintLneCount AsInteger

CallConnectIfcp.VBE.ActiveVBProject.Protection =vbext_pp_locked Then

TotalProjectLines =-1ExitFunction

EndIf

ForEachmdl Incp.VBE.ActiveVBProject.VBComponentsintLneCount =intLneCount +mdl.CodeModule.CountOfLines

Nextmdl

TotalProjectLines =intLneCount

ExitHere:CallCloseDb

ExitFunctionHandler:MsgBox Err.Number &VBA.vbCrLf &Err.DescriptionResumeExitHere

EndFunction

PrivateSubDeleteProcedure(strProcName AsString,mdl AsVBIDE.VBComponent)

DimCodeMod AsVBIDE.CodeModule

DimintBLine AsIntegerDimintELine AsInteger

SetCodeMod =mdl.CodeModule

WithCodeModintBLine =.ProcStartLine(strProcName,vbext_pk_Proc)intELine =.ProcCountLines(strProcName,vbext_pk_Proc).DeleteLines StartLine:=intBLine,Count:=intELine

EndWithCallCloseDb

EndSub


27/28


Delete ALL VBA Code

Deletes a specified line in a module

PrivateSubDeleteAllVBACode()Dimmdl AsVBIDE.VBComponentDimCodeMod AsVBIDE.CodeModule

ForEachmdl Incp.VBE.ActiveVBProject.VBComponents

Ifmdl.Type =vbext_ct_Document Ormdl.Type =vbext_ct_ClassModule Ormdl.Type =vbext_ct_ClassModule Then

SetCodeMod =mdl.CodeModuleWithCodeMod

.DeleteLines 1,.CountOfLinesEndWith

Elsecp.VBE.ActiveVBProject.VBComponents.Remove mdl

EndIfNext

EndSub

FunctionDeleteWholeLine(strModuleName,strText AsString)_AsBoolean

Dimmdl AsModule,lngNumLines AsLongDimlngSLine AsLong,lngSCol AsLongDimlngELine AsLong,lngECol AsLongDimstrTemp AsString

OnErrorGoToError_DeleteWholeLineDoCmd.OpenModule strModuleNameSetmdl =Modules(strModuleName)

Ifmdl.Find(strText,lngSLine,lngSCol,lngELine,lngECol)Then

lngNumLines =Abs(lngELine -lngSLine)+1strTemp =LTrim$(mdl.Lines(lngSLine,lngNumLines))strTemp =RTrim$(strTemp)IfstrTemp =strText Then

mdl.DeleteLines lngSLine,lngNumLinesElse

MsgBox "Line contains text in addition to '"_&strText &"'."

EndIfElse

MsgBox "Text '"&strText &"' not found."EndIfDeleteWholeLine =True

Exit_DeleteWholeLine:ExitFunction

Error_DeleteWholeLine:MsgBox Err &" :"&Err.DescriptionDeleteWholeLine =FalseResumeExit_DeleteWholeLine

EndFunction
mailto:[email protected]://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/http://www.accessextended.com/mailto:[email protected]


28/28

Document References

Subject URL

Application Object http://msdn.microsoft.com/en-us/library/office/ee291795(v=office.12).aspx

Module Object http://msdn.microsoft.com/en-us/library/office/aa223124(v=office.11).aspx

Working with Microsoft

Access Objects

http://msdn.microsoft.com/en-us/library/office/aa189752(v=office.10).aspx

Add, Remove, Check

References

http://wiki.lessthandot.com/index.php/Add,_Remove,_Check_References

Using Microsoft Access

as an Automation Server

http://support.microsoft.com/kb/147816

Use ADOX to Manipulate

AutoNumber Fields

http://msdn.microsoft.com/en-us/library/office/aa155430(v=office.10).aspx

AcModuleType

Enumeration

http://msdn.microsoft.com/en-us/library/office/ff197058.aspx

How To Automate

Microsoft Access FromVisual Basic .NET

http://support.microsoft.com/kb/317113
http://msdn.microsoft.com/enhttp://msdn.microsoft.com/enhttp://msdn.microsoft.com/enhttp://wiki.lessthandot.com/index.php/Add,_Remove,_Check_Referenceshttp://support.microsoft.com/kb/147816http://msdn.microsoft.com/enhttp://msdn.microsoft.com/enhttp://support.microsoft.com/kb/317113http://www.accessextended.com/http://support.microsoft.com/kb/317113http://msdn.microsoft.com/enhttp://msdn.microsoft.com/enhttp://support.microsoft.com/kb/147816http://wiki.lessthandot.com/index.php/Add,_Remove,_Check_Referenceshttp://msdn.microsoft.com/enhttp://msdn.microsoft.com/enhttp://msdn.microsoft.com/en

access automation

Documents