#NUM Error when linking Excel Spreadsheet to Access Table

This happens because there is a combination of numbers and letters in one of the columns of the Excel file you want to link. This will always create a numeric field overflow error. Access uses the first 8 rows of the spreadsheet to determine the data type of the column in the linked table. Say for example that your first 8 rows in column A of your spreadsheet have the following data:
1
2
3
4
5
6
7
8
9A

If you link this dataset to an Access table, value 9A will produce a #NUM Error.

Here is some vba code that will fix this problem. The code will insert an apostrophe before the value in any cell that you have selected and in order to make the apostrophe invisible, the code will activate the next cell down the column. When the code is done running on the cells you have selected, the upper left hand corner of the cell will turn green. This means that Excel thinks that this cell has a value that is an error. Specifically, the error message says:
"The number in this cell is formatted as text or preceded by an apostrophe." Clicking on ignore error will do just fine. You can apply this “ignore error” fix to all the cells you have currently selected. If the green indicator doesn’t bother you, leave it alone, it’s harmless.

Let me know if the code helped you. Because I found some code in Microsoft’s website that was supposed to fix this problem but it didn’t. Their code deleted the first character of every cell I had selected.

Here’s my solution:
Select the cells that you want to fix and run this macro:

Sub ConvertToAlphanumeric()

Dim cell As Object

For Each cell In Selection
cell.Value = "'" & cell.Value
ActiveCell.Offset(1, 0).Activate
Next

MsgBox "done!"

End Sub

How to Create a Personal Workbook for Easy Access to VBA Macros

If you have a set of macros / vba code that you would like to have readily available every time you open Excel, put them in the personal workbook. The personal workbook will automatically open but remain hidden every time you start Excel. The macros in this workbook will always be available.

Here's how:
First, record a dummy macro. This step will create the Personal Workbook for you.
>>> Tools / Macro / Record New Macro... / Store Macro in: "Personal Macro Workbook" / OK / and STOP. Your personal workbook has been created.

Now to actually go in and input vba code, press ALT + F11 and expand the PERSONAL.XLS Project. Or unhide the personal workbook first: Window / Unhide / Personal.xls and then edit the dummy macro you just created.

Loop Trough Cells in Excel and Change the Value of an Adjacent Cell

There is an Excel spreadsheet with two columns. Column A contains customer names and column B contains the number of invoices billed. Whenever the cell in column B is empty, I want to input a Zero. If the cell in column B is not empty, then I do not want to modify the existing value.
The loop will stop when it reaches an empty cell in column A.


Sub LoopThruCells()
[A1].Select
intRow = 0
Do While ActiveCell.Offset(intRow, 0).Value <> ""
If ActiveCell.Offset(intRow, 1).Value = "" Then
ActiveCell.Offset(intRow, 1).Value = 0
Else
ActiveCell.Offset(intRow, 1).Value = ActiveCell.Offset(intRow, 1).Value
End If
intRow = intRow + 1
Loop
End Sub

Converting Null values in Access

Use the Nz Function in Access to convert a null value to whatever you desire. This function comes in handy when you are doing divisions. You know you can't divide by 0, so wrap your denominator with the Nz function like this:
Syntax: Nz([Field Name], [valueifnull])

Tips for working with Complex Access Databases used for Data Audits

Most of the development I do these days involves creating Access databases to help audit data, this involves importing data from a human resources system and from PeopleSoft, comparing the data against both systems, and finally exporting the data as EDI files for invoicing. The access databases used for these procedures generally have 30 or more queries. With this amount of queries it is hard to come back 3 months later and remember the purpose each query and the queries’ dependencies. So I have become extra careful in the names I give to the queries. Here are a couple of tips.

1) If the queries run in sequential order, precede the query name with a number such as 01, 02, 03. This will help identify when the query is run in the data audit process. My preference is to name the queries like this:
STEP-01-Import_Excel_File
STEP-02-Make_tbl_Excel_PeopleSoft_Comparison
STEP-03-Export_Results_To_Excel
With this naming convention, I can come back later and sort by the query name to get a quick idea of a query’s dependencies without having to open each query in design view. This has been invaluable in troubleshooting and improving the audit solutions.

2) If you inherit a database that uses a lot of consecutive queries and the names have already been defined and you don’t want to change the names, use the query’s Description field in the Properties Window to give the query an ordinal value. You can then sort by the query’s description field.

3) Always use the Description field in the Properties Window of the query. This can be accessed by right-clicking on the query. If you already use a naming convention similar to tip # 1, then use this field to put comments that tell you something significant or unique about the query. For example if the query connects to an external source such as another Access database, SQL Server or Excel file, here would be the perfect place to put the ODBC definition for future reference.

4) If the query creates a table, then in the description field or even in the query’s name, give the name of the table it creates.
For example: STEP-02-Make_tbl_PeopleSoft_Comparison.

5) Do not rely heavily on queries that query other queries, especially if the underlying query performs complex calculations. For example, say you need to come up with the unbilled percentage of invoices unbilled in PeopleSoft by Customer name. You probably could do this with a single query, but my advice is to first create a summary table that has all the numbers required to perform a percentage calculation by customer name. Then create a second query that performs the percentage calculation based on the summary table. This will make future troubleshooting easier and will help when someone questions your final percentage results.

Sarbanes Oxley Compliance tip for Access Database

I created an Access database for an audit procedure to compare data from PeopleSoft and our Human Resources Information System (HRIS). Based on this weekly audit process, a table is created; the table lists timecards that are unbilled or missing from PeopleSoft. And every week our Billing Accountants are supposed to review this table and update the status of the timecard. So to comply with Sarbanes Oxley, I created a form that logs the Billing Accountant’s Windows User ID when he or she changes the timecard’s status.

On the change event of the “Status” combo box, there is VBA code that updates two text boxes on the form. The first text box is called txtChangedBy and the second text box is called txtChangedDate. These two text boxes are linked to an underlying table and map to appropriate table fields. Whenever the Billing Accountant changes the timecard’s status, his Windows User ID and his computer’s system time are logged. And there you have it, a Sarbanes-Oxley compliant Access database.


Private Sub Billing_Status_Change()

Me!txtChangedBy = Environ("USERNAME")
Me!txtChangedDate = Now()

End Sub

**Please note that for the Environ function to work, Excel’s macro security level must be set to low. This means you better have some good anti-virus protection.

Loop through worksheets in Excel

Sub LoopThruSheets()
Dim wsSheet As Worksheet
Dim strName As String
On Error Resume Next
For Each wsSheet In Worksheets

' ***START CODE FOR WHATEVER OPERATION YOU NEED. ***
strName = wsSheet.name
MsgBox name
'*** END CODE FOR WHATEVER OPERATION YOU NEED. ***

Next wsSheet
On Error GoTo 0
End Sub

Testing your Access Program

A good way to test the VBA code behind your forms is to input values of the following three categories:

1) Expected Values -- these are the values in the range you ask for. If you ask for numbers between 1 and 10, the expected values would be 2 through 9.

2) Boundary Condition Values -- these are the values that lie in the boundaries of your range. The boundary condition values would be 1 and 10.

3) Out-of-Bounds values -- these are the values that fall anywhere outside the range of 1 through 10. For example, 0, "true", "false", (empty), !, @, &, *, etc.

How to determine who is logged on to an Access database

Found some more useful code for finding out who is currently using an Access database. The code worked perfectly on Access 2003.

http://support.microsoft.com/default.aspx?scid=kb;en-us;285822

Access 2003 How to Automatically log out idle users

Found very useful code for automatically logging out idle users in Access. It worked like a charm on Access 2003.

http://www.tek-tips.com/faqs.cfm?fid=1432

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.

Access--Stop Asking Me Questions!

Say you have a macro in Access that runs several Delete, Make Table and Append Queries. By default, Access will ask for a confirmation before it runs the above query types.

If you don't want to be bothered, use the "SetWarnings" Action in the first row of your macro. Remember to turn it back on at the last row.

Finally, just so you will know for sure that your macro ran to completion, use the "MsgBox" action at the last row.

Find Duplicate Records in Excel

Got Dups in Excel?

Say you have a list of invoices in Column A, and you need to know how many times an invoice is listed.

In column B, use the Count formula....

=COUNT(A1,A1:A8)
The first parameter says which cell to count, and the second parameter delineates the range.

Find Duplicate Records in Access or SQL Server Table

Got dups in an Access or SQL Server table?

To find out if you have duplicates of something, for example Invoices....

SELECT
Table1.Invoice, Count(Table1.Invoice) AS CountOfInvoice
FROM Table1
GROUP BY Table1.Invoice
HAVING (((Count(Table1.Invoice))>1));

Speed up your Excel VBA by not looking at it

I have code that automatically creates excel graphs. After a few months, the amount of graphs that it needed to create grew from 10 to 50 and the execution time significantly slowed down.

I realized that if you minimize the application while it is running, it speeds up dramatically. Alternatively, you could use this vba function: Application.ScreenUpdating = False, which will stop updating the screen as the code is being executed.

Just remember to turn it back on at the end of your code: Application.ScreenUpdating = True

ActiveSheet.Paste stops working when upgrading from Excel 2000 to 2003

So I have some vba code that copies and pastes non-adjacent cells from one sheet to another and it stoped working when Excel was upgraded from 2000 to 2003. I searched the Internet for a solution and found a few people with the same problem but nobody seemed to have a definite solution.

The code would halt at: "ActiveSheet.Paste".

The solution was to use: PasteSpecial xlPasteValues, SkipBlanks.