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".