VBA: Tricks in Access VBA and VBscripting(1)

  1.  Naming of the procedures: Don’t name the procedure name the same as the module names.

module name

 

modulename

procedure name

procedurename

The following VBscripting code is trying to run a sub within Access. The sub is in a module called “RunQueries”.  The sub was named “RunQueries” initially because it is the only procedure in the module. The Run method in the VBcripting couldn’t find the sub in the Access because it has the same name as the module.  I changed the sub name to “OpenQueries” and the VPscript works fine now.

Dim objaccess
Set objaccess =CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "D:\accessdb.mdb", False
objAccess.Run ("OpenQueries")
ObjAccess.Quit
set objAccess = Nothing

2. Name of the Object: If the object has event procedure attached to it, the name of the object needs to be consistent with the object name reference in the procedure name.

Object Name

objectname

Procedure Name

objectprocedurename

When you change object name in the Property window, the corresponding procedure name in the code window doesn’t update automatically and the event procedure following the old procedure name will not run until you manually change the procedure name prefix to match the new object name.

3. Compact Access database in VPscript

objAccess.Application.SetOption "Auto compact", True