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.