Introduction:
I recently had reason to create a database on the fly if one did not exist. Since it took me some trial, error and searching (and then more trial and error,) I decided to share the method to do this. The following routine will create an Access database from any VBA enabled application, such as Word, Excel, Outlook, etc...

About the Example:
The example below creates a database at the root of the C: drive, using your MS Office Username. It also creates a new table "tblSample" with six fields in it. The most interesting part about this is that the code I provided below actually turns on the Unicode compression setting. Why is this important?

Macro Purpose:

1. Retrieve a recordset from a database and place it in an Excel worksheet, using an ADO connection to pass SQL strings.

Examples of where this function shines:

1. Works well for retrieving data from Access (or other database management system) to Excel.
2. Allows working with data in Excel, and making use of its rich features.
3. Code is robust enough to support varying amounts of columns or rows in the recordset.
4. You can supply your own SQL, allowing you to pull back only the data you need or want to work with.

Macro Purpose:

1. Retrieves data from an Access database, and fills it into a userform listbox.

Examples of where this function shines:

1. The Access database is in an Access 2000 format, but the code can be run from Excel 97-2003 with no issues.

Macro Weakness(es):

1. Does not populate listbox with column header names.
2. There is no error handling in this routine.

Versions Tested:
This function has been tested with Access & Excel 97, and Access & Excel 2003, and should also work with Access and Excel 2000 and 2002 (XP) without any modifications

Macro Purpose:

1. Exports a table of data from Excel into a database, using an ADO connection to pass SQL strings.

Examples of where this function shines:

1. Works well for archiving data from Excel to an Access database.
2. Does not insert rows if all cells in the row are blank (avoids entering completely Null records in the database).
3. Code is robust enough to support varying amounts of columns or rows.
4. The entire database table structure does not have to be reproduced in Excel, providing that the Database Management System (DBMS) has default values, or can accept NULL values, for any omitted fields.