Remote Queries In Microsoft Access

Here's an article on remote queries: queries that go against tables (or queries!) in other databases. It sounds really powerful.

That's one of the things I like about Access- just when you think you're getting a handle on the breadth of what it can do you find out about a whole new topic.

Excerpt:
"This article discusses one of those hidden gems that I discovered when investigating Access Automation. Whilst looking into that technology, I stumbled across a SQL help topic in Access that described an extension that allows you to point your current query to a table in another database. This in itself didn't seem all that interesting because I had been doing this for years using Linked tables. Then I noticed that you could also point your local query to a query that was in another Access database. No links, no local query definitions, this was starting to get interesting."

The Format() Function

The VBA format() function should be an important part of your Access/VBA toolbox. It lets you convert numbers or dates to strings, specifying exactly the formatting you want.
The format() function can help answer such questions as

How do I make my numbers show with leading zeroes?

How do I get the month name instead of the month number in my dates?

This article from UtterAccess.com is one poster's attempt to bring together a bunch of different pieces of information on format(). The writer has found that Microsoft has distributed the various references you need to be able to use format() throughout the help system, and so has collected it into one document.

Best Practices When Using Microsoft Office Access 2003 in a Multi-user Environment

This Microsoft article runs through a raft of things you should think about when deploying Access in a multi-user environment. It's not comprehensive, so review my other posts on multi-user deployment as well.

Adding Next and Previous buttons to your Combo Box or List Box

I often build forms with a combo box to select an item (by item I mean whatever the form's about- a project, a person, a product...) and subforms that show the related record(s). In a form I did recently I wanted to be able to browse from product to product. So I decided to add "Next" and "Previous" buttons.

It turns out that moving to the next or previous item in a combo box or list box is as simple as adding one to, or subtracting one from, the ListIndex of the combo box or list box.

There are only a few things to watch out for:

1. you have to SetFocus to the list box or combo box before you change it's ListIndex
2. if you try to move past the end or beginning of the list you'll get an error
3. when you want to check whether or not you're at the end of the list, remember that the ListIndex is zero-based; this means you're at the end when the ListIndex is equal to one less than the ListCount (the ListCount is the number of items in the list

The code below makes the Next and Previous buttons work. The basic code is pretty simple. There are a few niceties added, such as moving to the first item if you click the button and nothing is selected.

Then there's some fancy stuff I did for fun. The buttons have their tool tip changed to the list text for the item they'll move to- it's a kind of preview. To do that I had to write a routine to find the text for the next (or previous) item- it includes as many of the columns from the list box as are shown in the list. It does that by reading through the column widths to figure out which are not zero.

Download the
demo database

One more thing I found- when you change the ListIndex the control's AfterUpdate will fire. This works out fine, I think, it's just different than what would have happened if you had assigned a value to the control

Here's the code- give it a try!

Code:

Private Sub cmbProduct_AfterUpdate()
    SetToolTips ' update product preview tool tips
End Sub

Private Sub cmdNextItem_Click()
    ' advance to next item in combo box
    
    With Me.cmbProduct
        .SetFocus ' the combobox has to have the focus to set the ListIndex
        If IsNull(.Value) Then ' if combo box is null set it to the first item
            .ListIndex = 0
        ElseIf .ListIndex < .ListCount - 1 Then ' make sure it's not at the last item already
            .ListIndex = .ListIndex + 1 ' select the next item
        End If
    
    End With
End Sub

Private Sub cmdPreviousItem_Click()
    ' move to the previous item in the combo box

    With Me.cmbProduct
        .SetFocus
        If IsNull(.Value) Then ' if combo box is null set it to the first item
            .ListIndex = 0
        ElseIf .ListIndex > 0 Then ' make sure we're not already at the first item
            .ListIndex = .ListIndex - 1 ' select the previous item
        End If
                
    End With
    
End Sub

Private Function SetToolTips()
    ' this function sets the tool tips for the next and previous buttons to a
    ' preview of the product each will move the list to
    
    With Me.cmbProduct
        '  next item button
        If .ListIndex < .ListCount - 1 Then ' if not at the end of the list
            Me.cmdNextItem.ControlTipText = ListItemText(Me.cmbProduct, .ListIndex + 1)
        Else
            Me.cmdNextItem.ControlTipText = "(At end of list)"
        End If
        
        ' previous product button
        If .ListIndex > 0 Then ' if not now at the beginning of the list
            Me.cmdPreviousItem.ControlTipText = ListItemText(Me.cmbProduct, .ListIndex - 1)
        Else
            Me.cmdPreviousItem.ControlTipText = "(At beginning of list)"
        End If
    End With
End Function

Private Function ListItemText(ByVal ctlList As Control, ByVal lngRowNumber As Long) As String
    ' this function returns a string made up of all the visible columns for the
    ' selected row of a listbox or column box
    
    
    Dim varItems As Variant ' to hold array of column widths
    Dim lngCounter As Long ' counter for loop
    
    varItems = Split(ctlList.ColumnWidths, ";") ' split the list of column widths for reading
    For lngCounter = LBound(varItems) To UBound(varItems) ' read through the list of column widths
        If Val(CStr(varItems(lngCounter))) > 0 Then ' if columns is shown (width >0)
            If ListItemText <> vbNullString Then ' if list not empty add a separator
                ListItemText = ListItemText & " | "
            End If
            ListItemText = ListItemText & ctlList.Column(lngCounter, lngRowNumber)
        End If
    Next lngCounter
End Function

Private Sub Form_Load()
    SetToolTips ' inititalize product preview tool tips
End Sub

Displaying Custom Dialog Boxes for Custom Delete Actions

Handling record delete properly can make your applications much more usable. Think about the right confirmation prompt, and about doing any associated clean-up thast goes with the deletes. I've coded some delete functions that considered all of this and truly made sure the user understood what they were going to delete and then, after confirmation, cleaned up all the associated records.

This video how-to from Microsoft walks through some of the basics and will help you get started creating more professional record delete routines

How do I create an MDE, ADE, or ACCDE file?

Here's another great "soup to nuts" post from Walter at UtterAccess.com. This one covers everything you need to know about creating end user versions of your database for deployment.

deployment, multi-user, security, splitting, network

Developing Smart Tag Solutions with Microsoft Access

Today's post is about a feature that was added in Access 2003 which I haven't seen much written about: Smart Tags.

As a user I like them in Office 2003, (hover your mouse over a person's name in Outlook and you'll get a bunch of options for interacting with that person- that's an Outlook Smart Tag) but I haven't tried developing with them yet. Here's an article Microsoft files under "Technical Articles" that walks you through how to create them. I hope to try this out soon, and will post back with what I find.

How to display and to use the File dialog box in Microsoft Access

This Microsoft article describes the use of the FileDialog method to show a select file dialog.

The FileDialog object is new (introduced with Access 2002). It is an alternative to the Common Dialog Control (which may not be available to you depending on your licensing) and the File Open/Save API (which is the generally recommended solution on the boards these days).

The FileDialog object doesn't work with the runtime version, and it requires a reference to the Microsoft Office library, but for my money it's simple and clean to code and it fits my needs.

Also note that the FileDialog method can be called from other VBA hosting applications, such as Word or Excel, with no changes becuase it's part of Office, not part of Access.

Also see: FileDialog Property [Access 2003 VBA Language Reference]  #

Adding Tabs and Controls to the Office Fluent Ribbon in Access 2007

Here's a "visual how-to" from Microsoft on adding to the Access 2007 Ribbon..   #

How do I show the data that isn't there?

Now isn't that an interesting question. It's not such an unusual request: which students haven't submitted a paper? which departments haven't sold any coats today? Or the summary report flavour: when I run my monthly sales some departments don't show up- I want to see a zero where there have been no sales.

To get the answer to a question like this in Access usually involves two steps: find out what "everything" is, and then find out what part of "everything" you don't have.

Let's tackle this one with a students and papers example. Let's say we have three tables:

tblStudents -StudentID -StudentName -etc...

tblPapers -PaperID -PaperName -etc...

tblStudentPapers -StudentID -PaperID -etc...

To find all the papers I should have, I need all the possible student/paper combinations. You do this with an unusual query. Here's the SQL:

SELECT StudentID, PaperID FROM tblStudents, tblPapers;

The query has no join specified- that's why it gives you every combination. I think this is called a Cartesian Product.
For our example let's create the query above and call it qryAllStudentPapers.

Now you just need to use that query and tblStudentPapers and do an "unmatched query". The wizard can create simple unmatched queries, but this one joins on two fields so I don't think it can do it. Here's the SQL. (You can type the SQL into the SQL window and then see how it looks in the design view, then next time create it in the design view from scratch.)

SELECT qryAllStudentpapers.StudentID, qryAllStudentpapers.PaperID
FROM qryAllStudentpapers LEFT JOIN tblStudentPapers ON 
  (qryAllStudentpapers.PaperID = tblStudentPapers.PaperID) AND   
  (qryAllStudentpapers.StudentID = tblStudentPapers.StudentID)
WHERE tblStudentPapers.StudentID Is Null;

I won't stretch this blog out with explanations of LEFT JOINs and how unmatched queries work- you can read up on those on your own. The key here is the two step process- a cartesian product to find what "everything" is, and then an unmatched query to find what part of everything is missing in your data.   #

Adding an Option for (All) to Combo Boxes or List Boxes

How do I get my combo box to show an option for "All"?

This is a pretty common question on the discussion boards.

Look around the net and you'll see one common technique for "adding to your combo box". this post at the Access Web shows you how to do it using a union query, which is the solution I've used pretty reliably.

But here's another way. This "visual how-to" on MSDN shows you how to exploit a (AFAIK) rarely discussed approach to combo boxes where a callback function provides the rows. (This "function as a row source" thing is really neat and I hope to do another post on it soon!) This approach may be overkill for most combo box scenarios. It is, IMHO, pretty elegant though. And I'm pretty sure it's more flexible too. And I think it's worth a read just to get a look at the combo box rowsource callback function.

The article discusses Access 2007, but I did the same thing in 2003 and it works just the same.

How to get at Jet warnings that are not errors

This post on Michael Kaplan's blog delves pretty deep into how the Jet database engine can return information to your program about what's going on behind the scenes.

It's not beginner stuff, but a developer could use it to make their programs smarter.   #

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**