VBA function to strip illegal characters

Here's a post from the code archive at UtterAccess with a function to strip any disallowed characters from a string. It should be usuable from queries as well as from code.

Sharing an Access Database

Here's a great post from UtterAccess.com on splitting your database into Back-End and Front-End and deploying it for multiple users.

Quote:
"Congratulations! Your database has become so popular that others would like to use it. Just copy it to a shared location on the network and create shortcuts, right? Wrong! This is perhaps the largest cause of Access database corruption. Multiple users accessing the same .MDB at the same time is just asking for problems.
... this FAQ is meant to provide the best practices for sharing a database with others. Incorporated into these best practices are the concepts of Maximum Security, Future Manageability, and Higher Degrees of Scalability. Simply put, your database will be as secure as possible, as easy to modify as possible, and as easy to migrate to a larger platform when and if you ever outgrow Access."

deployment, multi-user, splitting

Cleaner Coding: Bang vs. Dot

This article presents one writers position on the old question:
    ! or .

You may've heard that "dots are used before things that Access names, and bangs are used before things that you name." Malarkey! It's just a coincidence that most of the things you name in Access end up in collections. Use bangs because they're being identified as members of a collection, not because you named them. FormName!ControlName is actually shorthand for FormName.Controls!ControlName -- the Controls collection can be left out because it is the default property of an Access form.

   #

DAO or ADO? Which should I use?

Here's a great review of the differences between these two different libraries for accessing your database from code.

"DAO or ADO? Which should I use? This is a commonly asked question, especially in view of the changing development paths that Microsoft has taken over the last few years. Which is best for your application? The answer may not be as straightforward as you may think!"

Interactive: Access 2003 to Access 2007 command reference guide

Here's a neat resource to ease the transition to 2007. It's an interactive cross-reference from the old interface to the new. Similar to resources made available for other Office 2007 tools, you click on the 2003 menu or command and it shows you where it is in 2007.  #

Deploying an Access Application over a Local Area Network (LAN)

This page runs through a variety of topics related to deployment in a LAN environment, including (among others)performance suggestions and splitting your database.

It includes links to a variety of tips and references, as well as some specific techniques.

Note that the suggestions to use a batch file to deploy front-ends may not be "best practice", there are other alternatives to accomplish the same goal. You should review these and determine which fits your situation best.

deployment, multi-user, performance,network

Ribbon customization: repurposing commands

Here's another post from the MS-Access team on working with the Office 2007 ribbon in Access.

Poker Series Ranks Using a Simple Subquery

Here's a post at UtterAccess that demonstrates using a simple subquery to calculate ranks.

Ranks are a common challenge for folks using SQL queries to get at their data. This basic example demonstrates a simple technique and includes an example database.   #

Sample expressions to extract portion of text string in Access

This article from Microsoft reviews basic techniques for extracting part of a string, such as a first name from a full name.

It doesn't demonstrate newer techniques using the Split() function, but uses tried and tested approaches that should have broad application.

SUMMARY
This article lists sample expressions that you can use to extract a portion of a text string. These expressions are commonly used in the Update To line of an update query to place a portion of a larger field into a new field. You can adapt these expressions so that you can use them with other common formats. For example, you can use the expression that is used to extract "Doe" from "Doe, John" to extract "Seattle" from "Seattle, WA."

DLookUp Criteria Examples

Examples of DLookUp criteria as you might use them in a query or a form or report control. A good basic post with simple easy to follow examples from an Access MVP site.

Doesn't cover the VBA code you'd use to build one of these dynamically using a program variable

Reports: Print only the current record to a report

Here's a good basic method for filtering a report based on the current record on a form.

Access Basics for Programming

A longer item than I normally link to, this is a great starting point for several topics related to programming Access and VBA, from Crystal, a frequent poster at UtterAccess.com and a Microsoft Access MVP.

If you're new to Access and/or VBA this might be a great place to start. **This link updated to reflect the Feb 2008 revisions**