Tony's Microsoft Access Blog : Access Tips Website Update

Website Updates - Microsoft Access Email FAQ - CDO aka CDONTS

Tony — Mon, 28 Sep 2009 18:06:12 GMT

Added the following to the Microsoft Access Email FAQ - CDO aka CDONTS web page.

Collaboration Data Objects, version 1.2.1
Beginning in Exchange Server 2007 and Outlook 2007, CDO 1.2.1 will no longer be provided as a part of the install of the product. As a result, there is functionality missing that many applications depend upon. CDO 1.2.1 is a package providing access to Outlook-compatible objects through a COM-based API.

Microsoft Exchange Server MAPI Client and Collaboration Data Objects 1.2.1
Starting with Exchange 2007, neither the Messaging API (MAPI) client libraries nor CDO 1.2.1 are provided as a part of the base product installation. As a result, there is functionality missing that many applications depend on. Microsoft Exchange MAPI and CDO 1.2.1 provide access to these APIs

Ensuring uniform control width on forms and reports

Tony — Tue, 07 Apr 2009 01:31:21 GMT

Jim, a good friend and client complained, err commented, that the unit number was too small on one key form in the Granite Fleet Manager. He had increased the number of characters in that field on the table from six to eight. I vaguely recall him mentioning that once.

I had increased the size of that field on some forms for them a while back but clearly I hadn't done a good job of testing an example value. Turns out I also hadn't increased the field size in my testing, demo or data MDB files either. Oops. I'm sure he forgot to tell me. (I can just see Jim now rolling his eyes and giving me the appropriate finger. <smile>)

I also knew though that I would've missed a few and not made them wide enough. As far as I know Rick's Find and Replace doesn't have any way of doing this kind of analysis so I threw together some code to find where all the problems existed. And, yup, only about 5 of the 20 occurrences of that field were sufficiently wide. <sigh>

So I did some more work and realized that I should make this utility available to everyone. It has been suggested that I make this an Add-In but I don't have the time right now to figure out the quirks of the URegSysInfo table, etc, etc. Anyhow visit Ensuring uniform control width on forms and reports for details and a link to download an MDB containing the table and a form.

This code illustrates going through the collection of forms and reports then going through the collection of controls on each form or report. If the control matches certain criteria, being a text, combo or list box then various properties are read from the control and placed in a table. If a combo box or list box the field name is extracted from each column of the Row Source query.

Relinking tables

Tony — Mon, 30 Jun 2008 23:22:19 GMT

I was asked the following question

"I want a simple approach to relinking all linked tables--yes, it would be nice to relink to more than one back-end--in an application IF they are out of date. Best place to put the code, maybe in the splash screen code-behind-form? Best method? I dunno. "

As I had the answer(s) in recent newsgroup replies I decided to answer this question and I'll do some work and put this on a web page. Real Soon Now TM Jerry Pournelle

Never mind. I created the web page Relinking Tables

Updated - Microsoft Access Email FAQ - DOS Mailers

Tony — Wed, 25 Jun 2008 02:30:47 GMT

I've updated the Microsoft Access Email FAQ - DOS Mailers page. Yes, no one uses DOS any more. <smile>

Updated the Late Binding page

Tony — Sun, 02 Dec 2007 20:25:18 GMT

I've updated the Late Binding in Microsoft Access page on my web site to include conditional compiler constants and directives. These make it much easier to move between early binding for debugging and VBA Intellisense to late binding which eliminates version and weird messages when the external application doesn't exist on the target system.

Using data from the previous year

Tony — Sun, 18 Nov 2007 22:29:58 GMT

A newsgroup question prompted me to pontificate for a bit. I then decided to create a page on the topic. Using data from the previous year

Using bar codes within an application

Tony — Wed, 19 Sep 2007 02:23:13 GMT

I've added Henry Habermacher's excellent links on UPC bar codes to the Using bar codes within an application page. I also removed some dead links.

Microsoft jet database engine could not find object 'xyz.dbf'...

Tony — Tue, 11 Sep 2007 00:40:03 GMT

The following was added to the Some Microsoft Access MDB Corruption Symptoms page

4b) 3011 - Microsoft jet database engine could not find object 'xyz.dbf'... Notice the dbf file extension. A dbf file name needs to be in 8.3 DOS name format. It is quite possible that some machines can open the file with a longer file name and others it must be in 8.3 DOS name format. Thanks to Marc L. Allen for this one.

Why do I sometimes need to set the Subdatasheet property to [None]?

Tony — Fri, 07 Sep 2007 04:08:07 GMT

I've noticed that occasionally, and for no apparent reason, Access will sometimes set a few tables SubDatasheet property to [Auto] after I had updated the table definitions. I just shrugged and started running the code as mentioned at BUG: Slower performance on linked tables in Access 2000 every time I open the FE. Of course I makes sure I run the code against the backend MDB.

Tom Wickerath gently pointed out to me that "That's only true if you have not first disabled Name Autocorrupt". (Actually Name AutoCorrect.)

Now I don't run it that way very much. I mostly turn it on when I need to update some field definitions and want the queries to automatically change as well. So I've never had the problems with it that others have had.

The Performance FAQ page has been updated accordingly.

Thanks Tom, Tony

Setting Startup Options Programmatically in Microsoft Access

Tony — Thu, 06 Sep 2007 23:52:57 GMT

I saw a newsgroup request on this and I realized I had all the code in one of my MDBs somewhere. Likely took my longer to find it than it did to create the web page. So I just created Setting Startup Options Programmatically in Microsoft Access page.

Issues ... Access ...Windows Vista (or Windows Server 2008)

Tony — Thu, 06 Sep 2007 03:08:00 GMT

See Issues that you may encounter when you open or are using a Microsoft Office Access database on a computer that is running Windows Vista - 935370. Note that this is an SMB 2.0 issue and thus Windows Server 2008 could also have this problem. Looks like this could be similar in some respects to the OpLocks problem back when Windows 2000 Server was introduced.

The Access Corruption Symptoms, Causes and Access Performance pages have been updated.

Edit: Changed Small Business Server 2003 to Windows Server 2008 as per Susan Bradley's query posting. I got confused between the SMB signing problem and SMB 2.0. See New Networking Features in Windows Server 2008 and Windows Vista and do a search on SMB 2.0. Added links to various Access pages.

Added Microsoft Office Access 2007 Developer Edition FAQ page

Tony — Sun, 02 Sep 2007 17:41:33 GMT

I've created a new Microsoft Office Access 2007 Developer Edition FAQ page.

Playing music severely degrades network transfer performance in Vista

Tony — Sat, 01 Sep 2007 03:27:28 GMT

Playing music severely degrades network transfer performance in Vista

Now isn't that one interesting. Thanks to David for the posting Vista network problem. Access Performance FAQ page has been updated.

Updated Tony's Table and Field Naming Conventions

Tony — Wed, 29 Aug 2007 03:39:19 GMT

Upon some mild prodding and questioning by a fellow MVP I realized I was missing some of the reasons why I do things. So I've did some editing and added text to Tony's Table and Field Naming Conventions

Updated the Microsoft Access (Office) Developer Edition FAQ page

Tony — Mon, 06 Aug 2007 20:20:27 GMT

I've updated the Microsoft Access (Office) Developer Edition FAQ page to include more information on the Access 2007 Developer Edition

Windows Vista, ODBC and SQL Server 2005

Tony — Fri, 27 Jul 2007 00:13:31 GMT

The following has also been added to the Access Performance FAQ page.

For the best connection string for a DSN-less connection to SQL Server 2005, switch providers to the SQL Native Client with the following:
"ODBC;Driver={SQL Native Client};Server=<servername>;DATABASE=mydatabase;Trusted_Connection=Yes"
by Rick Byham of Microsoft. See the thread Vista, ODBC and SQL Server 2005

Access 2007 design view performance on Windows Vista vs Windows XP

Tony — Thu, 26 Jul 2007 23:42:35 GMT

A very interesting thread brought out some interesting differences in the networking stack in Windows Vista. The following paragraph was added to the Access Performance FAQ page.

"If I disable autotuning on the TCP stack in Windows Vista I no longer suffer from this problem. ... until I upgrade to Windows server 2008 I won't be enabling autotuning on my Vista client." See the thread Access 2007 Design view performance on Vista vs XP initiated and finished by cpf_profiles See Receive Window Auto-Tuning on Vista for more technical information including KB articles.

Added Locking fields on a form page to my website

Tony — Sun, 01 Jul 2007 20:50:00 GMT

Thanks to a newsgroup poster I located the generic code I had created for a client, did some screen shots and have created Locking fields on a form. Requires a little knowledge of VBA.

Updated the Convert Access to Visual Basic, Delphi, Java, ASP or ASP.NET page

Tony — Sat, 30 Jun 2007 18:45:00 GMT

I've added AccessForms2Web which converts Access pages to PHP & MySQL to my Convert Access to Visual Basic, Delphi, Java, ASP or ASP.NET page as well as cleaning up old links.

Updated the Calendars and Compression DLLs, OCXs, etc pages

Tony — Fri, 29 Jun 2007 01:15:00 GMT

I added the following to the Calendars and Microsoft Access page: Duane Hookum, fellow Access MVP, has an awesome and simple calendar in a report. See the Calendar Reports sample section at Hook'D on Access Samples

I added the following to the Microsoft Access Compatible Compression DLLs, OCXs, etc page: Zip a File by fellow MVP Garry Robinson is a "simple way to add a file to a zip file using Access VBA and WinZip"