Count of unique items and count of all items in the same query

If you've scanned this blog you'll know of my fascination for subqueries. This thread at UtterAccess.com caught my eye today. The poster needs to count disticnt entries in one column, and all entries in another column, all grouped by the value in a third column.
Sounded to me like three queries. One for the each count and one to produce the final result. The poster, though, challenged the UAers to do it in one.
Van T. Dinh's post is a nice clean all in one query SQL solution. It's a good example of taking the two or three queries you would have done separately and building them up into one query, essentially by surrounding each with brackets and then putting it in the FROM clause like a table.
There are a couple of other posts here on my blog that put the whole subquery in the FROM clause.

• Writing a Count(Distinct) Query in Access
• More subqueries

** Post links cleaned up 2011-09-05 **

Returning Records around a specified ranked Record

Here's a really neat post that uses subqueries to return records "near" a particular record when a bunch of data is ranked.

Quote:
Sometimes, for example when you are dealing with a large table of data that you wish to have ranked, such as a sports league table, you may wish to return the ranking of a particular record, together with some records ranked just before this record, and some just after.

Office 2003 VBA References

Here are a couple of links to a chunk of 2003 Office and Access VBA stuff. A lot of it is just what changed in 2003, vs. a comprehensive reference, but there seem to be some useful sections there. I seem to frequently find and then subsequently loose the comprehensive reference stuff from Microsoft that I need when "I don't know exactly what I'm looking for yet." These links held a lot of promise when I first found them, but their lustre has faded somewhat.

• Office 2003 VBA Language reference
• Downloadable Office Access 2003 VBA Reference

Subqueries: include a running total in a query

Here's another great subquery example from UtterAccess. This one uses a simple subquery to add a running total to a query.

Subqueries: find top 25 by group

Here's an interesting post on UtterAccess where one of the VIPs uses a subquery in a pretty clean and simple way to find top 25 by group.

Using SnapShot files to distribute reports

View as
a full page
article

Download
sample
database
A2K3 A97

This article from Microsoft Office Online reviews the use of the Snapshot file format to distribute reports.

I've used snapshot in my organization for a couple of years now and have found them very usable. They provide all of the benefits normally associated with PDF files, and can be easily generated from Access without any additional components. Compared with saving to Word or RTF they have the advantage of preserving all your report formatting.

The article covers the basics of saving a snapshot file manually from a print preview window, but it doesn't cover automating the production of snapshot files. What makes this task harder is that the the snapshot format is not covered in the help or the list of allowed constants for the relevant commands. Despite this, it's really easy once you know how. And in this post I'll tell you how.

Read more »

Send e-mail through Outlook without a security warning

A common feature sought in Access applications is sending e-mail. If you're going to send a bunch of messages, such as reminders to each of the team members with due dates next week, you'll run acrosss Outlook's anti-spam provisions, which will require you to approve each message. There are a number of different solutions posted on the web. We'll look at two here- one from the web and one of my own.

Option one

This article caught my eye: it sees you create a function in Outlook that Access can call to send the message. Since code running within Access is trusted the security warning won't come up.

Option two

Here's another approach that also relies on the fact that code running within Outlook is trusted. Have Outlook open up the Access database to get data for the e-mails and do all the sending. This approach allows you to easily use more of Outlook's features, but may not tie into your user's workflow as it will be launched by the user from within Outlook.

For this exercise here's sample data that will be stored in an Access database:

In this case (which is really simplified for the case of the example) the column "Body" includes the HTML code for the message.

The VBA code for this solution will go into an Outlook VBA module. Just like other hosting applications, you get at the code by pressing Alt-F11. Once you have the code in and tested you can run it from Tools/Macro or you can make a toolbar button that launches it. One more thing- I tested this in Outlook2003 only. Oh, and you'll need a reference to DAO under Tools/References [show me].

Sub SendMessages()
 Dim mailMyMail As MailItem
 Dim rsMessages As DAO.Recordset
 Dim db As DAO.Database
 Dim ws As DAO.Workspace
 
 Set ws = DAO.DBEngine(0)
 Set db = ws.OpenDatabase(--- put the path and name of your database here ---)
 
 Set rsMessages = db.OpenRecordset(Name:="tblMessages", Options:=dbReadOnly)
 
 Do Until rsMessages.EOF
 Set mailMyMail = CreateItem(olMailItem)
 With mailMyMail
 .To = rsMessages!ToList
 .cc = rsMessages!CCList
 .Subject = rsMessages!Subject
 .HTMLBody = rsMessages!Body
 .Send
 End With
 rsMessages.MoveNext
 Loop
 
 Set mailMyMail = Nothing
 
 rsMessages.Close
 Set rsMessages = Nothing
 
 db.Close
 Set db = Nothing
 Set ws = Nothing
End Sub

See also:

• OL98: Developer Information About the Outlook E-mail Security Update
• Outlook Redemption v. 4.4 A product that "works around limitations imposed by the Outlook Security Patch..."
• Getting started with Outlook VBA
• Distributing Microsoft Outlook VBA Code

Cumulative percent: show me the items that contribute to the first 10% of the sales

This is a common business problem, and not one that's easily solved with simple queries or reports. Look around the web and you'll find a number of solutions. Many of those solutions only work in reports and require VBA coding. Here's one that uses only a series of queries and will not require you to get into the muck of SQL or VBA.

Here's sample data for this example:

The question at hand is: how much space is allocated to items that contribute the last 25% of the margin dollars.

Let's show how this would be done in Excel. It may illustrate the concept better:

Having sorted the data and figured out cumulative margin and the percent of the total that represents, we know that in category 2, two items contribute the first 69% of the margin dollars, and the remaining three only contribute 31%. In Excel you have to build formulas by category and so it becomes tedious for large amounts of data. As well, the rest of the analysis you're doing may be in Access so to figure out the cumulative % in Excel would mean moving data back and forth- cumbersome and error-prone!

To do this in Access we need queries that will do the same thing we did using formulas in Excel.

The first query is the hardest. We need to sort the items in descending order and figure out cumulative sales at each item. Here's what it looks like in the query design grid:

The first thing you notice is that tblPerformance is in the query twice. Just add it twice in the Show Table dialog, and Access will automatically give it another name: tblPerformance_1 so that it (and you) can keep the two copies separate.

The two copies of the table are first joined on CategoryNumber, so that the sorting and summing is done within each category. The last column is where the magic happens. Access looks at the two copies of the table, and for each item finds all the items with margin greater than  or equal to that item. Then the sales for those items are summed. This is similar to the Excel example where we sum all the items in the list above the item we're calculating for. Because tblPerformance has two different names we can tell it which table to sum the values from and how to compare the value in one table to the other.

The rest of the queries are easy. You need one that finds the total margin by category:

And one that uses those two queries to turn cumulative dollars into cumulative percent:

Now you can use that query as a filter to find values to sum to answer the original question (how much space is allocated to items that contribute the last 25% of the margin dollars)

Performance:
A query like this can be quite resource intensive. If you run it on a large table you may find it takes too long to run. Consider making the first two queries make table queries and building the third query off the tables. Or try putting indexes on your data table. For this example I built indexes like this:

Getting the path for the "Temp" folder

Way back in the days of DOS, and the early days of windows, you could usually put temporary files in C:\TEMP. Today Windows provides TEMP folders, but they won't be in the root of C:. In XP for instance the path will be C:\Documents and Settings\username\Local Settings\Temp. If you want to use that folder in your program, such as to save a copy of a report for sending via e-mail, you'll need to get the specific path name. In this post I'll show you two ways to ask Windows for the path for the TEMP folder.

Using an API

Application Programming Interfaces (APIs) provide a way for your program to call Windows functions that aren't otherwise available. There's an API called GetTempPath that does just what we want. You'll usually need to build a function around API calls to make them easier to use in your program. Paste this code into a standard module and you'll be able to use the function GetWindowsTempPath() to return the Temp folder name.

' delaration of API to get the special folder name for temp objects
' (in XP this is, for example, C:\Documents and Settings\user.name\Local Settings\Temp\)
Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
    (ByVal nBufferlength As Long, ByVal lpBuffer As String) As Long


Const MAX_PATH = 260 ' maximum length of path to return- needed by API call to provide a memory block to put result in


' function GetTmpPath
' return windows special folder for temp objects
' path will not include a backslash on the end
' path will be expressed using short (8.3) names for folders
    
Public Function GetWindowsTempPath()
    Dim strFolder As String ' API result is placed into this string
    
    strFolder = String(MAX_PATH, 0)
    If GetTempPath(MAX_PATH, strFolder) <> 0 Then
        GetWindowsTempPath = Left(strFolder, InStr(strFolder, Chr(0)) - 1)
    Else
        GetWindowsTempPath = vbNullString
    End If
    
    ' remove any trailing backslash
    If Right(GetWindowsTempPath, 1) = "\" Then
        GetWindowsTempPath = Left(GetWindowsTempPath, Len(GetWindowsTempPath) - 1)
    End If
    
End Function

Using the FileSystemObject

The FileSystemObject is provided to interact with files and folders from VBA and from Windows Scripts. One of the many things the FileSystemObject can do for you is give you the paths to some of Windows special folders. Like the previous example, paste this code into a standard module and you'll be able to use the function GetWindowsTempPath() to return the Temp folder name. Because of the use of the FSO you'll need to go into Tools/References and add a reference to the Microsoft Scripting Runtime.

Public Function GetTempFolderName() As String
    ' use a FileSystemObject to returns the name of the user's temp folder
    ' in this case FSO is early bound- it's common to late bind with this library but
    ' this works well and provides compile time checking
    ' requires a reference to Microsoft Scripting Runtime
    ' path will not include a slash
    ' path will be expressed using short (8.3) names for folders
    
    Dim fso As Scripting.FileSystemObject ' an instance of the FileSystemObject
    
    Set fso = New FileSystemObject

    GetTempFolderName = fso.GetSpecialFolder(TemporaryFolder).Path
    
    Set fso = Nothing

End Function

Additional reading:

• Working with Files, Folders, and Drives: More VBA Tips and Tricks Includes a great overview of the FileSystemObject.
• Programming the FileSystemObject
• Windows API Microsoft reference for APIs.
• Getting The Path To Windows Special Folders

Working with Files, Folders, and Drives

I just ran across this article from Microsoft. It covers off a whole list of techniques for accessing files and folders. It overviews the VBA functions available and discusses a couple of libararies that you can access for more capabilities. It doesn''t go into detail on all the functions and methods, but provides links to further information on many of the functions. Some sample code is included.

Quote:
If you want to work with files or the file system, you have several options available—and the best choice depends on what specifically you want to accomplish. The available options include using VBA functions, the Microsoft Scripting Runtime Object Library, the Office FileSearch object, and file system-related Windows API functions. The only things I will not cover are the Windows API functions.

MouseOver Status Labels

Take a look at this neat sample database posted by John Mishefske at UtterAccess. It shows some really nice object and event techniques. The result is a form with a pretty effective UI behaviour that goes way beyond “bread-and-butter” Access forms.

events, forms, object-oriented

Synchronizing Combo Boxes on Forms

This Microsoft Visual How-To walks through a solution to a very common scenario: make the available choices in one combo box depend on the selection made in another combo box.

Read more »

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

Access-Freak : Microsoft® Access 2007 Ribbon/Office menu customization

http://www.access-freak.com/tutorials.html#Tutorial05
Includes a tutorial and links to several Microsoft articles.

Microsoft Access Performance FAQ- Tony's Microsoft Access Page

This page is a collection of performance related topics from Tony Toews, a Microsoft Access MVP.

How to optimize Office Access and Jet database engine network performance with Windows 2000-based and Windows XP-based clients

From the Microsoft Knowledgebase.

When you run a Microsoft Jet database engine-based program, such as Microsoft Office Access, on your Microsoft Windows 2000-based or Microsoft Windows XP-based computer, the program may appear slower and less responsive than you expect. This article contains information about how you can optimize network performance for Windows 2000-based and Windows XP-based computers. Doing this can make Office Access and Jet database engine-based programs more responsive.

   #

Allen Browne on Converting to Access 2007

Access Team Blog Posts On Working With The Ribbon in 2007

The Office 2007 ribbon seems to be a source of frustration for some developers. I haven't tried it yet (I have 2007 but I haven't done any development there- I just converted a couple of simple databases) but these posts make it look not so hard.

Customizing the New Access UI
Creating item templates for Ribbon customizations to use in Visual Studio
Ribbon Customization - Closing the Currently Open Object
Ribbon Customization: Filling Dropdown Lists
Ribbon Customization: Repurposing Commands
And here's the tag search for their Blog since I'm sure there are more to come!

Microsoft article "Designing Your Database Application"

This looks like a great primer on application design, focusing specifically on an Access environment.

Using dates and times in Access

This article from Microsoft Office Online is a good round-up of date and time topics.

Read more »

Database normalization basics document from Microsoft

"Understanding Relational Database Design" document

Why would I want to use a class object in MS-Access?

Access Dynamic Reports

Some neat ideas here- stretches the envelope a bit beyond what I've seen tried with reports.

Allow or disallow shift-key to bypass startup options

Only let the user add one record

I've tried different options for this in the past. This discussion at UtterAccess demonstrates on technique that I haven't tried yet, but likely will.

More subqueries

Here's another cool subquery. The use of a subquery here allows a left join on a calculated field!

SELECT BatchID, NewSKUString, CleanedSKUNumber, SKUID, PROD_NBR, OFFRNG_ENG_DESC, CORP_STAT_CD
FROM
  [SELECT BatchID, NewSKUString, CleanSKUNumber(nz(NewSKUString),True) AS CleanedSKUNumber FROM tblNewSKULists]. AS NewSKUs
  LEFT JOIN tblExistingSKUData
  ON NewSKUs.CleanedSKUNumber = tblExistingSKUData.PROD_NBR;

(CleanSKUNumber is a function)

I read something on UtterAccess that said that when you use square brackets as above Access creates a separate query "behind the scenes". I don't know if that's true or what difference it makes. I can tell you that the above works just the same with round brackets:

SELECT BatchID, NewSKUString, CleanedSKUNumber, SKUID, PROD_NBR, OFFRNG_ENG_DESC, CORP_STAT_CD
FROM
  (SELECT BatchID, NewSKUString, CleanSKUNumber(nz(NewSKUString),True) AS CleanedSKUNumber 
   FROM tblNewSKULists) AS NewSKUs
  LEFT JOIN tblExistingSKUData 
  ON NewSKUs.CleanedSKUNumber = tblExistingSKUData.PROD_NBR;

I was also fascinated to see that if you key the above into the SQL view and then switch back to the QBE grid it shows the subquery as it were a sepeartely saved query.

Subqueries

I am fascinated by subqueries and keep finding things on-line that I didn't know would work. Some of the really elegant subqueries I've created have turned out to be reall performance problems. Others work fine and result in an all-in-one solutions that seems "smarter" and mean less components to manage.

I found one today in a post on the Access team blog. Here's one of the queries from the article:

SELECT C.Color, Sum(C.Value) AS Total, T2.N
  FROM
    (SELECT T.Color, Count(T.Color) AS N
     FROM
      (SELECT DISTINCT Color, Count(*) AS N
       FROM tblColors GROUP BY Color) AS T
       GROUP BY T.Color) AS T2
  INNER JOIN tblColors AS C
  ON T2.Color = C.Color 
  GROUP BY C.Color, T2.N;

This is for count of distinct items. See the blog post for more.

Visual Basic Programmer's Guide : Custom Classes and Objects

Classes and objects are a powerful and often underused part of VBA. You will find them featured as topics often on this blog. This link is to a reference on Microsoft.com

Programming the FileSystemObject

The FileSystemObject is provided to interact with files and folders from VBA and from Windows Scripts. This reference documentation from Microsoft provides details on some of its functions.

Forms: Refer to Form and Subform properties and controls

Here's a useful reference to the right way to refer to a control on a form, or a control on a subform on a form, etc. Getting the syntax just right can be tricky.

Excel won't close!

If you're using automation from Access to Excel (or I suppose between any two MS Office apps) and the application you've opened through automation won't close, it may be because of "unqualified references". For a complete explanation see http://support.microsoft.com/kb/319832.

The likely culprit is a line like:

  Cells(6,3)=SomeValue

VBA figures out that Cells must refer to the active worksheet of the excel application, but when it does so it leaves something hanging somewhere and bad things happen later.

Instead, specify what Cells relates to- something like:

  dim xlMySheet as Excel.Worksheet
  ... (you have to Set xlMySheet to the worksheet in here somewhere)
  xlMySheet.Cells(6,3)=SomeValue

The other symptom you sometimes see caused by this error is "it works once, but then doesn't work the second time".