Access
VBA routines within Microsoft Access for automation and managing the Access database, tables, queries, forms, reports, macros, and modules.

Category: Environment 
Microsoft Access application, ADP, and startup routines, managing the Access window, mouse pointer, printers, ribbons, status bar, and Microsoft Visual SourceSafe 
Module: Startup 
Startup routines for a Microsoft Access database application to open backend databases, relink tables, set error trapping, etc. using Access VBA.
For more information, read our whitepaper on Microsoft Access Split Database Architecture (http://www.fmsinc 
Procedure 
Description 
OpenAllDatabases 
Use this procedure when there are multiple backend databases to keep open; or close the previously opened databases.
NOTE: Significant performance improvements can be achieved by keeping a handle open during the entire time your application is running.

OpenBackendDatabase 
Open a handle to a backend database and keep it open; or close the previously opened database.
NOTE: Significant performance improvements can be achieved by keeping a database handle open during the entire time your application is running.
Visit http:// 
RelinkTable 
Relink (attach) one table in the current database to a new database 
SetErrorTrapping 
Set or reset error trapping setting so errors are properly handled while your application runs.
This setting can be manually set from the IDE under the Tools, Options menu, General Tab.
If error trapping is set to Break on All Errors, code with On Error 
Access Databases
VBA and VB6 code for working with Microsoft Access (Jet) databases using Data Access Objects (DAO) and ActiveX Data Objects (ADO).

Category: Table and Records 
Manage tables and data records from Microsoft Access databases for local and linked tables using ADO and DAO with VB6 and VBA. ADP support provided through ADO code. 
Module: JetTableInfo 
Determine Microsoft Access/Jet database object information using DAO in VBA and VB6. 
Procedure 
Description 
(Declarations) 
Declarations and private variables for the modJetTableInfo module 
IsRecordsetField_DAO 
Determine if a field exists in a recordset. 
IsTable 
Determine if the table in the specified database exists. 
IsTableField 
Determine if the field in the table in the specified database exists. 
IsTableLinked 
Determine if the table in a named database is a linked table. 
IsTableOrQuery 
Determine if name is a table or query within the specified database. 
Module: RecordsetDAO 
Use DAO to create recordsets for Microsoft Access database objects from VBA or VB6. 
Procedure 
Description 
(Declarations) 
Declarations and private variables for the modRecordsetDAO module 
CloseDatabaseDAO 
Close a database object, even if it's not open.
This may fail if there are recordsets open based on this database object. Use CloseRecordsetDAO to close each recordset. 
CloseRecordsetDAO 
Close a recordset, even if it's not open 
CreateRecordsetDAO 
Create a DAO recordset that is editable and works with SQL Server 
Module: TableModifyDAO 
Modify Microsoft Access tables to add, delete and rename fields; set starting number of autonumber fields; set table property for performance; and set field properties using VBA and VB6. 
Procedure 
Description 
TableFieldSetAutoNumberValue 
The autonumber value for an Access/Jet table starts at 1. This procedure sets it to a higher value and assumes the table is empty.
For more information on this technique, see http://www.fmsinc.com/MicrosoftAccess/autonumberfield/ 
General VBA/VB6
General Code for Visual Basic for Applications (VBA) and Visual Basic 6 (VB6)

Category: Date and Time 
Date and time routines, including business day math 
Module: AgeBirthdays 
Calculations for age, birthdays, anniversaries, and days since the last one or until the next one using VBA and VB6. 
Procedure 
Description 
(Declarations) 
Declarations and private variables for the modAgeBirthdays module 
AgeYears 
Get the age in years for a specified birth date. 
AgeYearsFractional 
Get the exact age in years with decimals for the specified birth date. 
DaysSinceLastBirthday 
Number of days since the last birthday. Includes handling of leap years (February 29th). 
DaysToNextBirthday 
Number of days to the next birthday. Includes handling of leap years (February 29th). 
Module: DateTime 
Date and time calculations for adding and subtracting dates, day of week or month, quarters, ISO week numbers, date/time parts, translating time to words, etc. using VBA and VB6.
VBA and VB6 provides many useful functions such as DateAdd, DateDiff, DateS 
Procedure 
Description 
AddHours 
Add or subtract a number of hours to a date/time. 
AddMonths 
Add or subtract a number of months to a date. 
AddWeekdays 
Add or subtract a number of weekdays to a date. Weekend dates are not counted in adding/subtracting days. 
AddWeeks 
Add or subtract a number of weeks to a date. 
AddYears 
Add or subtract a number of years to a date. 
DaysInMonth 
Get the number of days in a month. 
MonthLastDay 
Get the last day of the month. 
Category: Math 
General math and routines for area and volume, statistics, and trigonometry 
Module: MathAreaVolume 
Math routines for calculate the area and volume of various objects in VBA and VB6. 
Procedure 
Description 
(Declarations) 
Declarations and private variables for the modMathAreaVolume module 
AreaOfCircle 
Get the area of a circle. 
AreaOfRectangle 
Get the area of a rectangle. 
AreaOfRing 
Get the area of a ring. 
AreaOfSphere 
Get the surface area of a sphere. 
AreaOfSquare 
Get the area of a square based on the length of a side. 
AreaOfSquareDiag 
Get the area of a square based on its diagonal length. 
AreaOfTrapezoid 
Get the area of a trapezoid, 
AreaOfTriangle 
Get the area of a triangle based on the base length and height. 
AreaOfTriangle2 
Get the area of a triangle based on the length of its sides. 
LenOfRectangleDiagonal 
Get the length of the diagonal of a rectangle. 
LenOfSquareDiagonal 
Get the length of the diagonal of a square. 
VolOfCone 
Get the volume of a cone. 
VolOfCylinder 
Get the volume of a cylinder. 
VolOfPipe 
Get the volume of a pipe. 
VolOfPyramid 
Get the volume of a pyramid or cone. 
VolOfSphere 
Get the volume of a sphere. 
VolOfTruncPyramid 
Get the volume of a truncated pyramid. 
Module: MathStatistics 
Mathematical functions covering the areas of statistics and probability in VBA and VB6.
This category contains various math routines covering the areas of statistics and probability.
VBA has builtin functions for various mathematical operations. Search 
Procedure 
Description 
(Declarations) 
Declarations and private variables for the modMathStatistics module 
Factorial 
Get the factorial of a given number.
This function only works for values between 0 and 170. 170 is the maximum because the factorial of any number greater than 170 is beyond the numeric range of VBA/VB6. 
FactorialRecursive 
Get the recursive factorial of a given number.
This function only works for values between 0 and 170. 170 is the maximum because the factorial of any number greater than 170 is beyond the numeric range of VBA/VB6. 
GetArrayMean 
Get the mean of the elements in the supplied array 
GetNumberCombinations 
Get the number of combinations of items that can be derived from a population (order does not matter).
Combinations are different from permutations, because permutations count the number of ways (orders) items can be selected from the pool. For instance 
GetNumberPermutations 
Get the number of combinations of intItems that can occur from intPool (order matters).
Unlike combinations, permutations count the number of ways (orders) items can be selected from the pool.
For instance, with playing cards, there is only one combinat 
Module: MathTrigonometry 
Trigonometry functions for working with values such as cosines, tangents and secants in VBA and VB6. 
Procedure 
Description 
(Declarations) 
Declarations and private variables for the modMathTrigonometry module 
ArcCoSecant 
Get the inverse cosecant of the supplied number. 
ArcCosine 
Get the arc cosine of the supplied radians. 
ArcCoTangent 
Get the inverse cotangent of the supplied number. 
ArcSecant 
Get the inverse secant of the supplied number. 
ArcSine 
Get the inverse sine of the supplied number. 
ArcTangent 
Get the inverse tangent of the supplied numbers.
Note that passing zero for both parameters returns a Null value, and causes an "Invalid Use of Null" error. 
CoSecant 
Get the cosecant of the supplied radians.
Note that sin(Radians) cannot equal zero. This can happen if dblRadians is a multiple of PI. In this case, the function returns a Null value, and causes an "Invalid Use of Null" error. 
CoTangent 
Get the cotangent of the supplied radians.
The function is undefined if input value is a multiple of PI. 
Log10 
Get log base 10. The power 10 must be raised to equal a given number.
Log Base 10 is defined as this: x = log(y) where y = 10 ^ x 
Log2 
Get log base 2. The power 2 must be raised to equal a given number.
Log base 2 is defined as this: x = log(y) where y = 2 ^ x 
LogN 
Get log base N. The power N must be raised to equal a given number.
Log base N is defined as this: x = log(y) where y = N ^ x 
Secant 
Get the secant of the supplied radians. 
Category: Application Techniques 
Common application development code for messages boxes, error handling, and shutting down windows 
Module: MessageBox 
Routines for simplifying the use of the builtin MsgBox function in VBA and VB6.
Use these rather than calling the MsgBox command directly. 
Procedure 
Description 
(Declarations) 
Declarations and private variables for the modMessageBox module 
MsgBoxCritical 
Displays a message box with the critical (stop) icon and Ok/Cancel buttons.
If you specify a blank string ("") for the strTitle argument, the application name is used as the message box title. This application title is usually the name of the VBA host pr 
MsgBoxInfo 
Displays a message box with the information icon and OK button.
If you specify a blank string ("") for the strTitle argument, the application name is used as the message box title. This application title is usually the name of the VBA host program, such 
MsgBoxQuestion 
Beeps and displays a message box with the warning query icon and Yes/No buttons.
If you specify a blank string ("") for the strTitle argument, the application name is used as the message box title. This application title is usually the name of the VBA ho 
MsgBoxWarning 
Beeps and displays a message box with the warning icon and Yes/No buttons.
If you specify a blank string ("") for the strTitle argument, the application name is used as the message box title. This application title is usually the name of the VBA host pro 
Category: Date and Time 
Date and time routines, including business day math 
Module: Waiting 
Wait for a period of time or until a particular time using VBA and VB6.
Uses the Windows API Sleep command to avoid the DoEvents command in a loop that eats up all your CPUs. Includes support for 32 and 64 bit API calls.
For more information, read our p 
Procedure 
Description 
(Declarations) 
Declarations and private variables for the modWaiting module 
WaitForTime 
Waits until the specified date and time 
WaitSeconds 
Waits for a specified number of seconds 
Geospatial
Geospatial code for translating addresses to latitude and longitude coordinates, distance between locations, Google Earth automation, mapping on Google Maps and Bing Maps, and Zip Code lookup and fill from a table using VBA and VB6

Category: Zip Codes 
Lookup City and State values from a zip code lookup table using DAO and ADO with VBA and VB6 
Module: ZipCode_DAO (class) 
Use DAO to lookup a zip code's corresponding city and state from a zip code table in VB6 or VBA.
FMS, in conjunction with the USPS, offers a monthly subscription to current US zip codes.
See http://www.fmsinc.com/MicrosoftAccess/ZipCodeDatabase.html for 
Procedure 
Description 
(Declarations) 
Declarations and private variables for the CZipCode_DAO class 
Class_Initialize 
Initialize files for zip code lookup by opening database directly to enable SEEK command 
Class_Terminate 
Close variables 
GetCityState 
Lookup city and state based on zip code 
GetLinkedPath 
Retrieve the linked path of a table in the current database 
VerifyCityZip 
Verify that the zip code, city, and state match 