Know the Current Working Directory in Access

In an unbound text box on an Access form, type:
=ENVIRON(“PATH”)

Note: the ENVIRON function in Access 2003 is blocked by default. To unblock it, set the Security Level for Macros to Low.

Domain Functions in Access Forms

Some useful functions for getting quick information from tables and queries are the Domain Functions.

Say you wanted to know the earliest value in field [ORDER DATE] in table [ORDERS].
In an unbound text box in a user form, type the following:
=DMIN(“[ORDER DATE]” , “[ORDERS]”)

For the latest date, type:
=DMAX(“[ORDER DATE]” , “[ORDERS]”)

For a quick count of total records in table ORDERS, type:
=DCOUNT(“[Any Field Name in table ORDERS]” , “[ORDERS]”)

For a sum of all rows in a column, type:
=DSUM(“[Any numerical field in table ORDERS]” , “[ORDERS]”)

For the average of all rows in a column, type:
=DAVG(“[Any numerical field in table ORDERS]” , “[ORDERS]”)

Link Access to Excel

A most useful feature is the ability to Link between Excel and Access.

In order to link an Access Query to an Excel spreadsheet, an ODBC connection must first be defined:
Start / Settings / Administrative Tools / Data Sources (ODBC)
System DSN Tab /Add / Choose Microsoft Access Driver (*.mdb)
Name and Describe your data source
Click Select, choose your Access Database, Click OK until the ODBC window closes.
Your ODBC connection is defined.

Now from within Excel:
From the top menu bar, choose: Data / Get External Data / New Database Query
In the Choose Data Source window, select the ODBC recently created and click OK.
Now all the tables and Queries available in the database will appear.
Make your selection and drill down to the fields you want to bring back.
Navigate the next screens by choosing any filters or sorting options.

Next time you want to refresh the data from within Excel, right click on any of the data fields and choose Refresh Data. Alternatively, you can configure the data to be refreshed automatically upon opening Excel. These configurations are found by right clicking in any of the linked fields in Excel and choosing Data Range Properties.

A word to the wise:
If linking many queries in many spreadsheets, name the queries in Access so the will appear on top when sorting by query names. And name your spreadsheets identically to the queries they link. Since the Choose Data Source window in Excel is so small, this naming convention will make maintenance easier.

Known problems:
These linked Queries sometimes start acting up and don't refresh appropriately.
Solution: Delete the rows or Sheet and link the query again--no need for creating new ODBC.

Numeric Field Overflow when importing or linking Excel to Access

When linking or importing from Excel into Access, the first few rows of a column are used to determine the Access column data type. Often times, this assumption causes a Numeric Overflow Error.

Example, the first 10 rows of the field "Project ID" contain only numbers. The remaining rows contain a mix of numbers and letters. Access will assume that the field is numeric and will error out as soon as it tries to import the first set of alphanumeric records.

One solution, in Excel, precede the "Project ID" field with an apostrophe: '
For an automated Excel VBA solution, see this post: Numeric Field Overflow error when linking Access to Excel

Another solution, sort the Excel file so that the alphanumeric records are in the first rows.

Get the User Name in Access

To know the Windows user id currently logged in, in a text box on an Access form, type =ENVIRON("USERNAME") .

This will work immediately in Access 2000. But if using Access XP, then by default it will error out because the macro security level is set to Medium. To change this, go to the top menu, choose: Tools / Macro / Security / Low.