Using ADO with Excel Data Sources

ADO is used for performing client/server data access between a data consumer and a data provider. ADO is a good choice, in many instances, for working with Excel data because it eliminates the sometime lengthy process of having to open Excel. Using ADO also makes it possible to use SQL to retrieve and manipulate data….


Tips on Access report numbering

Starting Report page numbers from a number other than one In Design View place an unbound text box in the report footer or header. Then enter the following in the ControlSource property of the text box: =”Page ” & [Page]+([Starting Page]-1) When you view the report Access will ask you for a value for ‘Starting…


User-Defined Functions in Excel

Excel allows you to create custom functions, called “User Defined Functions” (UDF’s) that can be used the same way you would use SUM(), VLOOKUP, or other built-in Excel functions. The Excel user who wishes to use advanced mathematics or perform text manipulation is often seriously disappointed by Excel’s limited library of formulas and functions. A…


Creating reusable code

This blog discusses just a couple of ways that you can make your code reusable.If you frequently create similar projects, you can save yourself considerable development time and ensure application consistency by creating and using object and project templates. For example, say that you use a copywrite form or common switchboard across your application. In…


Adding Additional Conditional Formatting in Access with VBA

Microsoft Access 2000 (and later) lets you conditionally format the data in combo box and text box controls based on certain conditions through the user interface or  through programming. However, each control is limited to a maximum of three conditional formats at any one time. This blog demonstrates creating conditional formatting through VBA programming. As…


Database normalization simplified?

–Making Sense of Database Normalization, Hopefully–   Following is my take on database normalization. Hopefully this simplified version will help your understand of a sometime complicated subject a little better.   When setting up a database, one of the most important steps to keep in mind in the process is normalization. Normalization is the process…


Adding a button and Click event at run-time

Many developers recognize the need to disable certain controls in their application to either keep from confusing users as to which button to click or to keep users from clicking buttons at an inappropriate time. Usually this is done by programmatically enabling/disabling the button. However there is another way. At run-time, you can create a…


Should I use a bang or dot?

One of the questions frequently asked by new Access users (and veterans as well) in the newsgroups concerns writing expessions that reference various collections, objects, and properties. When should you use a bang (!) and when should you use a dot (.) to reference the various relationships? For example, suppose that you are writing VBA code…


Improving Access Database Performance

One of the areas where I see a lot of questions in the newsgroups concerns the performance of Access databases. Access databases are used for a variety of applications; everything from relatively small to medium records management to proof of concept databases. For those of you who are new to Access as well as veterans…