Module Name

Change Access Db Links

Changes TableDef and QueryDef objects with connection strings to a DSN-less connections.

Access Db Remote ShutDown

An Access DB that uses a hidden form to shutdown database FE. to use this code:

Excel Constants for Access

A module of global constants as long values with descriptive name for use in late-binding Excel, either from Access, or internally.

XLA for Operations Management Statistical Functions

An XLA that adds additional Operations Management statistical functions to Excel: Built several years ago while I was an MBA student, it might lack some controls, i.e., validation.

Worksheet Wrapper Classes

This is an idea I developed to avoid using arrays while refactoring spaghetti code. The user had used numerous arrays across many sheets with many columns, and for some refactored features I decided to create a parameter class for the rows of select sheets, and then a class that would hold a collection of parameter classes, providing type ahead and methods to get values, as well as provide basic properties like Count and Item.

Note: Requires an additional helper function contained in System_CommonArrayFunctions

Array and Collection functions (VBA)

The collections functions used worksheets like object collections, providing persistence. Some similar code used classes to wrap worksheet information to provide type-ahead, instead of using arrays via numbers.

Collection Functions Array Functions

Power Code - Handle Sleep & Suspension

Excel C# code to reload an Excel add-in, although this should work for any office product, upon waking from suspend on Windows 7

Excel Version Check (VBA)

VBA code for returning the Excel application version, updated to include Excel 2010

Competency Matrix for VBA

A developer, Sijin Joseph, reachable @, has an online Programmer Competency Matrix, which I have taken and converted to Excel.

Technical Evaluation Matrix for VBA

An Excel 2007 worksheet to facilitate in technical interviews of Excel/Acces VBA candidates.

Design Pattern List (Updated)

An updated list of design patterns covering ideas laid out by the GoF, Code Complete, Pattern-Oriented Software Architecture, and Patterns of Enterprise Application Architecture.

Working with Document Properties (VBA)

Code module for working with document properties. Original code is from Pearson's website, with some modifications by myself.

Rename File System Objects (VBA)

Uses file system objects to rename files by moving them.

Access VBA Modules from forms, that show how to retrieve domain-related information. These are provided as examples, since you will need to decouple the code from the underlying tables and related forms to use them.

VBA for iteration an OLAP cube's metadata and values.

Sample code to perform a compact copy of an Access DB.

A simple class to do logging

Example of a class module used to interface between Access or Excel application and SQL Server.

Access class module, although should work in Excel with minor modification, that automates zipping files, with parameters for the following:

Properties Methods

An Access DB that queries the domain for object, e.g., user ID's. Requires that you modify code to specify a domain The code can be run from the supplied form or directly, and truncates and appends to the supplied table. Please note, this was last edited in Access 2007, and if you are using an earlier version, you will need to correct broken references.

A function that sends (no prompt) a Notes e-mail, based on passed values for subject, recipient, body, and attachment.

A class that encapasultes an import process, although somewhat tied to specific process, easy modifiable to decouple and/or modify it as needed. In general, the process requires a source data file, an import type specified, staging and master tables, a report date field, a level1 field (a coupled requirement that can be removed), and a cleanup bit for dirty imports that can be turnd on or off.

Changes the DB referenced in data refreshes, provided that all the linked queries are the same DB.

Listing of ASCII character representation by number, as well as appearance if formatted as W*Dings in Excel.

Access code to backup all modules, forms, and queries, as well as create tables of table features; code checks for tables' existence and creates if necessary. Dynamically creates backup folder in the folder of the Access MDB.

A very simple module to return the screen resolution of the current desktop.

Form Resizer For Microsoft Access (By Jamie's Software)
The Form Resizer is a Microsoft Access MDB (Microsoft Database) that allows you to install a module in your own Access MDBs that can resize your program's forms to fit the current screen resolution. It is intended for Access developers with basic Visual Basic for Applications (VBA) coding knowledge. Works with Access 97, 2000, 2002 and 2003.

A simple class function to provide weekday calculation, native to Excel, in Access.

A module which replaces the MkDir function, such that it verifies and creates an entire path; MkDir only creates a new directory on an existing path. Please note that this module was create by Pearson Consulting for Excel, and I simply modified it to work in Access.

Excel Constants as Global Vairables for Late-Binding

The entire list of Excel 2003 constants and values, useful when late-binding Excel in Access automation.

Verifies if a table exists; returns boolean for success; useful when users can delete files, preventing a process from executing if a table does not exist.

Verifies if a query exists; returns boolean for success; useful when users can delete files, preventing a process from executing if a query does not exist.

From Access, manipulates an Excel workbook - it defaults to the first tab but code can be improved to specify a worksheet object or a specific tab - sorting a worksheet object on a passed field and a sort order.

From Access, finds a text value in Excel and returns its range (cell). The function is passed a worksheet object, a text value to find, and an optional row to search. If no row is specified, it searches the used range of the specified worksheet.

Verifies that columns in a table exist, created to verify column heading on imported Excel spreadsheets. The function receives a table name as string, and comma-delimited column names as string, verifies existence of table, parses the column string into array, and then tests for each column, returning TRUE if all exist, else False.

Procedures (2) to compare strings in Access, either by stripping all non-numeric characters, or by stripping all non-alpha characters. Useful in queries when having to compare strings that are functionally the same, but typed differently, e.g., with hyphens or commas.

Primarily a timer, and paired with other code can be used to send reports, shut down a database, and/or perform other system activities.

Access MDB to backup stored procedures. Code checks for table's existence and creates if necessary. Requires modification of module level variables to fit your environment. Creates both a date-stamped folder of individual SQL files, as well as an export to a single HTML document.

Backup Stored Procedures

Access code to backup stored procedures. Code checks for table's existence and creates if necessary. Requires modification of module level variables to fit your environment.

VBA Functions to Validate Types

Multiple functions that validate data types.

Export Access Tables for Excel (VBA)

Procedure to export all data tables as Excel workbooks. includes a procedure to format each workbook. Note, that the internal path is fixed and will need to be modified to suit your needs, either as a new path, or as a recipient of a more dynamic, form value.

Fix Numbers Stored as Text (Excel, VBA)

Fixes numbers stored as text, a common problem with Access exports to Excel, by copying the value back into the cell, avoiding formulae.

Get User Id via Windows API (VBA)

Basic module using Windows API to get the username (UserID or login ID) of the currently logged in user.

Export Access Tables for PortgreSQL

Exports Access data into form that can be inserted in PostgreSQL database. Not my own code, but provided by third party.

Transpose Array (VBA)

MS VBA code to transpose an array, presumably used in conjunction with GetRows.

Execute and Log SQL Requests (VBA)

Instead of executing SQL literals in code, a function that receives an SQL string, the executes and logs it. Includes a module to deal with strings containing quotes.

Verify Email and URL Strings (VBA)

Two modules to verify strings, e-mail and URL. Works in either Excel or Access.

Execute SQL and Log (VBA)

Instead of executing SQL literals in code, a function that receives an SQL string, the executes and logs it. Includes a module to deal with strings containing quotes.

Export Table to Excel (Access VBA)

Exports all internal tables as Excel spreadsheets. Has hard-coded internal output path, which can be modified, as well as recoded to be directed via form.

Outlook EMail via VBA

Sends e-Mail via Outlook. Written as function, so is passed recipient string and memo body. Can be modified to suit.

Test for Form State (Open)

Function to test for the existence of a form. Useful to test if a form is open, so as to close it.

UserID using Windows API

Gets the UserID from the Windows using an API call - it is recommended that none of the syntax is modified - as well as a function to evaluate the returned value.

Export Recordsets to Excel

Three (3) procedures to export recordsets to Excel, including two (2) procedures that format Excel, either when called from within other procedures, or alone from a form.

Excel VBA Constants

Enumerates VBA constants, useful when Excel is late-bound in Access, or when simply late-binding Excel VBA code, since many methods and properties are not readily available.

Export Access information to tables and text files

Exports Access components to tables, in the case of queries and tables, or subdirectories, for modules, to a hard-coded subdirectory. Also includes function to enumerate Access data type values as strings. In conjunction with a form, the path can be dynamic, and provide additional source code backup.

Code-based ALTER TABLE statement

Pass it the table, the field, and the type, and it executes an SQL statement to modify the field's table.