Includes a tutorial and links to several Microsoft articles.
Includes a tutorial and links to several Microsoft articles.
Posted by Stephen at 7:46 AM | Permalink | I'm reading: Access-Freak : Microsoft® Access 2007 Ribbon/Office menu customizationTweet this! | Add This! | Blog This |
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.#
Posted by Stephen at 2:57 PM | Permalink | I'm reading: How to optimize Office Access and Jet database engine network performance with Windows 2000-based and Windows XP-based clientsTweet this! | Add This! | Blog This |
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!
This looks like a great primer on application design, focusing specifically on an Access environment.
Posted by Stephen at 12:13 PM | Permalink | I'm reading: "Understanding Relational Database Design" documentTweet this! | Add This! | Blog This |
Labels: Database design
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.
Posted by Stephen at 11:51 AM | Permalink | I'm reading: Only let the user add one recordTweet this! | Add This! | Blog This |
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.
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.
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
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.
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.
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)=SomeValueVBA 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".
Tag cloud code courtesy of phydeaux3
UtterAccess is the web's leading help forum for Microsoft Access. The community of Access enthusiaists who post there provide help to newbies and veterans alike, always in a friendly and supportive manner.
Allen Browne's tips for Microsoft Access Allen Browne is one of the web's leading authorities on Access. Several of his articles are the standard reference for the topic at hand. Allen is also the most often cited cataloguer of Access bugs and the related work-arounds.
The Access Web This site, established way back in '98, features tips and solutions from several Access MVPs. The code samples posted here are always clean and effective. Often you won't find a lot of text, but you will find code that works. The solutions here are cited all over the web as the best way to get things done.
Stephen Lebans' site is the authority on the web for some types of solutions. It's a site every Access developer should know. You'll find tips, plus downloadable working solutions ranging from zooming images in and out, to disabling the mouse scroll wheel, to a calendar, to creating PDF files. In particular I look to Stephen's site first for advanced topics related to forms and reports, especially interacting with the Windows API.Candace Tripp's Utter Angel page is another often-cited resource. she posts simple working solutions for download. No Access/VBA links page is complete without Candace's page.