eLetter January 2004
As the next AccessExtra Newsletter is not coming out till February, I thought I would send you an eLetter this month with a couple of tips and some very interesting Access/Office links.
Tips:
Tip 1. Pass OpenArgs to Reports
Starting with Access 2002 the OpenArgs property can be used for reports as well as forms. You need to use VBA or a macro to pass the value to a report. Using VBA:
DoCmd.OpenReport(reportname, [view], [filtername], _
[wherecondition],[windowmode], OpenArgs)
where the OpenArgs argument accepts a Variant.
Tip 2. Decompiling an Access Database
If I get a compile error in a database and there is no specific code at fault, I try a Repair and Compact. If that doesn't work, the next step is decompiling the .mdb, using the undocumented /decompile command-line switch for Access, to discard all compiled code. This also reduces the size of the .mdb file. To decompile your database:
1. VERY IMPORTANT:
Make a backup copy of your database in a different folder
2. Close any open instances of Access.
3. From the command line, type .../msaccess.exe /decompile
4. In Access open the database you want to decompile. Depending on the
version of Access you may or may not see a message about VBA code.
5. Open up any module. Select the Compile All Modules and
Save All Modules menu items.
6. Close your database and close Access
7. After restarting Access without the command line switch and performing
a Repair and Compact on your database, any weird VBA should be gone.
8. If you still get errors, try creating a new empty .mdb, and then import
all the objects from the old .mdb into the new database.
As the /decompile switch is an un-documented Access feature, it is not supported by Microsoft. Use it sparingly and only as a last resort. Make sure you make a backup copy of the .mdb before proceeding.
My Access Control Center (ACC) application allows you to easily create a Decompile shortcut for a specified .mdb that you can use when needed. ACC is currently featured in a Tech News article on the Access Advisor site at: http://accessadvisor.net/doc/13362 . If you haven't been to Access Advisor recently, it it worth a return visit.
You should also visit the SmartAccess Newsletter site at http://www.pinpub.com/html/main.isx?sub=29 where you can download a free copy of their well-regarded monthly Newsletter, and get a free 3 months trial subscription. You can also download an excellent article on How to Drag and Drop in Access.
2. Useful links:
. The official MS Office home page http://office.microsoft.com/home/default.aspx has been revamped and has many useful links. My Access add-in SmartForm+ was recently featured on the Office home page in a Spotlight on Access section.
. I have saved the December issue of the Inside Office Newsletter from Microsoft as it contained many useful links
http://www.aadconsulting.com/inoffdec03.mht
. Tech Republic - 14 Access Articles
http://techrepublic.com.com/1200-26-5124059.html?tag=hdi
. Microsoft is considering opening up the source code underlying Office
http://news.com.com/2100-7344_3-5142661.html?tag=st_pop
. Using the Microsoft Visual Studio Tools for the Microsoft Office System
http://www.devx.com/codemag/Article/18233
. Office 2003 Code Focus from DevX
http://www.devx.com/codemag/Door/15586
. MSDN: Ten Tips for Microsoft Office VBA Library Development
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_tentipsvba.asp
. VBA AND VB.NET Code Samples
http://www.programmingmsaccess.com/Samples/
. From http://www.capastatistic.com/
As the url to their Links page is broken, the urls are listed below:
Smart Access Articles:
Access Subquery Techniques
http://msdn.microsoft.com/library/en-us/dnsmart01/html/sa01j1.asp
User Interface Standards
http://msdn.microsoft.com/library/en-us/dnsmart01/html/sa01i1.asp
Query Lists
http://msdn.microsoft.com/library/en-us/dnsmart01/html/sa01h1.asp
Access 2002 Data Projects for Developers
http://msdn.microsoft.com/library/en-us/dnsmart01/html/sa01g8.asp
Access 2002 for Developers
http://msdn.microsoft.com/library/en-us/dnsmart01/html/sa01f1.asp
Sophisticated Reports: Multiple Columns And Subreports
http://msdn.microsoft.com/library/en-us/dnsmart01/html/sa01e10.asp
Improving on VBA with Transact-SQL
http://msdn.microsoft.com/library/en-us/dnsmart01/html/sa01d1.asp
An Access E-mail Application
http://msdn.microsoft.com/library/en-us/dnsmart01/html/sa01c1.asp
You Can Do That with Datasheets?
http://msdn.microsoft.com/library/en-us/dnsmart01/html/sa01b1.asp
Everything Doesn 't Happen at Once: Loosely Coupled Events
http://msdn.microsoft.com/library/en-us/dnsmart01/html/sa01a14.asp
Inside Access Articles:
Querying for the Second-Highest or -Lowest Value in a Group
http://msdn.microsoft.com/library/en-us/dnima01/html/inAc.asp
Incorporate Windows Dialog Box Functionality in Your Applications
http://msdn.microsoft.com/library/en-us/dnima01/html/ima0201.asp
Use UNION Queries to Combine Dissimilar Data into Single Fields
http://msdn.microsoft.com/library/en-us/dnima01/html/ima0137.asp
Adding an (All) Item to an Unbound Combo Box
http://msdn.microsoft.com/library/en-us/dnima01/html/ima0143.asp
Understanding ADO 's Default Cursor Type
http://msdn.microsoft.com/library/en-us/dnima01/html/ima0501.asp
Using the Right Wildcard Characters in SQL Statements
http://msdn.microsoft.com/library/en-us/dnima01/html/ima0601.asp
Starting a Label Report with Any Label on the Sheet
http://msdn.microsoft.com/library/en-us/dnima01/html/ima0701.asp
Identify the Location of Special Folders with API Calls
http://msdn.microsoft.com/library/en-us/dnima01/html/ima0187.asp
Easily Track Changed Data with dbWatch
http://msdn.microsoft.com/library/en-us/dnima01/html/ima0193.asp
MS Office Professional Articles:
Managing SQL Server - Using the SQL-DMO Object Model from Access
http://msdn.microsoft.com/library/en-us/dnovba01/html/ManagingSQLServer.asp
Formatting Access 2002 PivotCharts with VBA
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/OfftheChartAccess.asp
Getting Started with ADO - Or, Using Access Data (without Using Access)
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/GettingStartedwithADO.asp
Access 2002 Specific Links:
Beginning Web Development in Access 2002
http://msdn.microsoft.com/library/en-us/dnacc2k2/html/odc_adap.asp
Transforming Microsoft Access XML Files into HTML with XSLT
http://msdn.microsoft.com/library/en-us/dnacc2k2/html/odc_acc2xslt.asp
Developer Considerations When Choosing a File Format in Access 2002
http://msdn.microsoft.com/library/en-us/dnacc2k2/html/odc_acFormat.asp
More General Developer Topics:
Database design - What Do Your Customers Want?
http://msdn.microsoft.com/library/en-us/modcore/html/deovrWhatDoYourCustomersWant.asp
Where Should You Store Data?
http://msdn.microsoft.com/library/en-us/modcore/html/deovrWhereShouldYouStoreData.asp
Built-in Access Functions and Methods
http://msdn.microsoft.com/library/en-us/modcore/html/deovrWorkingWithBuiltinAccessFunctionsMethods.asp
Working with Controls on Forms and Reports
http://msdn.microsoft.com/library/en-us/modcore/html/deovrWorkingWithControlsOnFormsReports.asp
Visit AccessExtra Link Partner:
Superior Software for Windows
Access, SQLServer and .NET Downloads
http://www.ssw.com.au