Tony's Microsoft Access Blog

Be aware of "Privacy Manager for HP Protect Tools"

A client of a fellow Access MVP was having  "Too many databases open" errors.  

Several users reported that Access was hanging.  AppHangB1 Access

That thread in turn referenced Access 2007 hangs/stops responding

Removing  Privacy Manager for HP Protect Tools solved these problems.

The Auto FE Updater works just fine under Windows 8.

The Auto FE Updater works just fine under Windows 8.  

Now there will likely be issues if Access version next runs on the ARM CPU for tablets and other mobile devices.   But there is lots of time to figure out that solution and I have a plan.

Auto FE Updater V3.20–Uncompress zip files including subfolders

Version 3.20 of the Auto FE Updater is now available for download.

• Uncompress zip files including subfolders
• Minor bug fixes

The FAQ page was updated with a few items as well.

Access 2010/Windows 7 performance hotfix

Description of the Access 2010 hotfix package (Ace-x-none.msp): August 30, 2011 This applies to Windows 7 and Windows Server 2008 R2 systems.

Apparently this solves the Access 2010 performance problem when it comes to ACE but not Jet 4.0 performance problems.

Auto FE Updater works with Terminal Services RemoteApp

Details on configuration with the Auto FE Updater can be found at the Terminal Server RemoteApp page.

Thanks to Nick H of the U.K. for asking about this functionality.

A new version of the Auto FE Updater is available–3.19

Version 3.19 - 2011-08-24  (674 kb, 21,462 lines of code)

• Enterprise Edition can now create emails for all active users who have ever used the configuration file system.
• I realized the form showing all users active in the backend and who tried to get into the locked configuration file was technically accurate but very user unfriendly.  So rather than showing just network ids it is now showing the user name and contact information.
• Minor bug fixes

Now I have to admit that middle point is rather dumb on my part.  After all what use is just the userid when I have the user name and contact details present in the database.    It only took me a few minutes to add the fields to the query and thus to the form.  Oh well.

Auto FE Updater Version 3.18 is now available for download

Version 3.18 - 2011-08-19  (706 kb, 21,239 lines of code)

• Enterprise Edition can now create emails for all users currently in database or for all users who have tried to use the database while locked out.    
• Error message screens can now create, but not automatically send,  emails containing the error message text to the support email address as defined in the Master screen.    Master users can now create, but not automatically send, that error message directly to Tony at Auto FE Updater support.   
• Users will now get reminder screen after using the Auto FE Updater when the 14 day trial period has expired.   You can immediately then get a 7 day grace period to ask for and receive a trial license.
• Remove Lock All toolbar button as I figure no one is likely using it.  If this is a problem let me know and I'll put it back
• Minor bug fixes

Also see the version history page.

One of the biggest problems I have is feedback.  I simply don’t get much in the way of error messages and such.  So now when you the developer, but never the user, gets an error message you have the option of emailing me the message.  I hope this means I will get much more feedback as to the problems folks are encountering.

This also means I will likely be added web pages for specific commonly encountered errors and updating the FAQ pages.  But most importantly I can respond faster to problems being encountered.

Access 2010 Compact & Repair breaks autonumber seed if index is descending

While this is very unusual having more than 1000 records in an Access 2010 MDB with the autonumber index in descending sequence a compact and repair loses the seed and the next record inserted fails.

Note this is the Microsoft partner forums so if you haven’t registered as a partner you won't see this.

http://social.microsoft.com/Forums/en-US/partnerofficeaccess/thread/9dc92ff4-c5a5-47c5-ad8c-9590d3eb8e69

I have reproduced this behavior myself.

(I sure would like to know why 1000 records is required to repro this though.  <smile>)

This behavior also happened in Jet 4.0 for a while.  AutoNumber field duplicates previous values after you compact and repair a database

USB cable tips

I found retractable cables for most of my electronic devices for $3 or $5 including shipping on eBay. So now I don't have to drag along the meter long cables in the laptop bag. (That's 3 feet for my metrically challenged American friends.) While I
found a connector for my 5 year old Palm I couldn't find one for the proprietary end of my new Nikon compact camera! Shame on you Nikon for using a proprietary end with the standard USB micro connector likely would’ve worked.   Grrrr.

Of course I’ll probably still drag along my cables but I’ll put them in a Ziplock bag in my luggage.   It would really irritate me if one of those retractable cables went bad and I had to spend $20 or $30 on an overpriced retail cable.

You can also purchase adapters to go from one size to another for a few dollars.  I thought about those but then saw the retractable cables so decided to buy those instead.  For no particularly good reason.  

I put white finger nail polish on the "up" end of the black USB cable ends.   Frequently I have trouble seeing or feeling which end is “up”.  

Did you know that you can change the PCs date and time from VBA code?

I didn’t.  Until I read The Daily WTF article Trans-Atlantic Time Trap.    There’s a link leading to a 2004 thread on this topic Change System Date via VBA.  Glad to see that fellow Access MVP Truitt B got bored one Saturday   Note that one poster commented that if you used Date and Time as the names of fields in tables or controls on forms or reports you could have issues.

I tried just the following VBA code on my Windows 7 system and received a Permission Denied error message which is, in my opinion, quite reasonable.

date = #2012-01-01#

If I run Access as an administrator it does let me change the date.   (I very quickly changed it back.)

I then tried creating a control named Date on a form and then assigned it a string value. 

Date = “test”

I then got a Type MisMatch message.  Which makes sense once you realize what is happening.

Next I tried

me.Date = “text”

but this gave me an Invalid use of Me Keyword.   Which also makes sense too.   But very, very confusing to someone who doesn’t realize that these are reserved words.

Speaking of reserved words a friend was happily working on a timesheet system using a table called Union.  Which worked just fine for months until he had to create a union query using that table.

For the definitive list see Allen Browne’s Problem names and reserved words in Access.

Office 2010 SP1 now available for download

Description of Office 2010 SP1

or Microsoft’s Office 2010 Download Center

Installing the 64 bit edition of Access Database Engine 2010 when the 32 bit edition is installed

You can indeed install the 64 bit edition of the Access Database Engine 2010 when the 32 bit edition is installed.    You need to add a /passive switch and install it from the command line.   See the Microsoft Access Database Engine 2010 Redistributable blog posting for more details.

Now who was the original discoverer of this quirk I have no idea.  I couldn’t find any mention of this on the MS website but …

Thanks to Sameer Panse in the Should Access Database Engine match OS install or Office install? posting for finding this option.

Output to PDF - Run-Time error 2501: The OutputTo action was canceled

Turns out the problem was “an invalid character in the file name. “

Thanks to Septimus for raising the issue OutputTo DPF Error and figuring out the problem.

ADO error 3251– Object or provider is not capable of performing requested operation.

If you are getting the above error message check the length of the path of the Access database.   A client had this problem where the path and Access database file name was “only” 181 characters.  Testing shows that the length of the path and the Access database file must be under about 128 characters.  Bizarre.

Several VBA coding tricks

An interesting question was asked. How to make the following code simpler with Arrays   Now the poster was asking about arrays which I felt was going in the wrong direction so I replied with the “tricks” I use.

Auto FE Updater – now on YouTube

An introductory video on the Auto FE Updater is now on YouTube - Access Utilities: Tony's Auto FE Updater

I’m very grateful to Clif McIrvin and fellow Access MVP Crystal Long for spending the time.

ADO breaks when compiling in Windows 7 Service Pack 1

See the thread Breaking change in MDAC ADODB COM components in Windows 7 Service Pack 1 if you compile an app using early binding of ADO in Windows 7 SP1.   You may need to change your app to use late binding.

I didn’t thoroughly read the thread as I’m travelling but I wanted to get the word out amongst the community immediately.

Starbucks store locator UI is terrible and lousy customer response email

http://www.starbucks.ca/en-ca/_Our+Stores/  is terrible.

I figure I’ll tell them about it.  So I sent them the following email.

Folks

I'm attempting to find a Starbucks place in Edmonton or Vancouver and can't.  I have no idea what I'm doing wrong.

I choose Retail store, change the 5 kms to 50 kms, enter Edmonton, AB and do a search.  Nothing.   I have no idea what I'm doing wrong.

Also every time I get the Locations found: 0 I click on New Search and have to re-enter all the above information all over again.

This is a terrible UI experience.

Tony

Their reply

Hello Tony

Thank you for contacting Starbucks Coffee Company.

I apologize for any inconvenience you had finding one of our stores online.

Our online Store Locator www.starbucks.com/retail/find makes it easy to find Starbucks coffee around the globe, including address information and maps. The locator includes listings for our freestanding retail stores around the globe as well as our airport locations.

If you have a web-enabled mobile device, you can also access store location information at mobile.starbucks.com. 

Lastly, you can use your cell phone's text message service to find a location near you.  Simply text your zip code or postal code to 'MYSBUX' (697289).  While this is a free service, text messaging fees may be assessed by your cell phone service provider.

This information, as well as other subjects are covered in the FAQ (Frequently Asked Questions), available on the website by clicking the Customer Service button, then the FAQ button.

Thanks again for your interest in Starbucks Coffee. If you have more specific questions not answered at the Starbucks site, please feel free to e-mail us at info@starbucks.com or call (800) 23-LATTE to speak with a customer relations representative.

Warm regards,

<name removed>
Customer Relations
Starbucks Coffee Company
800 23-LATTE (235-2883)
Monday through Friday, 5AM to 8PM (PST)

My reply

Your reply completely missed the point of my email.    Please forward my email to the president of Starbucks so they can take appropriate action.

Tony

Another cause for “Disk or Network Error” & performance issues

Two machines have a duplicate IP Address.    Network problem, suggestions sought The troublesome machine had been hardcoded an IP address which was happened to be just past the end of the DHCP allocated IP addresses.  The problems started happening when the IP address was being used by two systems.

Corruption symptoms page updated.   Entry 26a.

David Fenton posted on a similar query by the same person mentioning that terribly slow performance was caused by obsolete hosts file on the workstations with an old server IP address.     Network appears to be down when it isn't   You will have to scroll down to the bottom and click on the “add/show 1 more comment” link.

A New Year, have you thought about Death and Passwords?

Very interesting topic about which I’ve talked about in the past but I’ll repeat my solution.

A New Year, have you thought about Death and Passwords?

“Earlier this year one of my friends was killed in a small plane accident.  His family still does not have access to his email and Facebook accounts.  To send messages they post to his wall for other of his “Friends” to see.”

I have all my passwords in KeePass Password Safe.   A excellent open source utility which keeps the passwords encrypted, generates new random passwords and auto fills the userID and password in most websites.   One of the entries in that file is several paragraphs explaining my backup system, key passwords, including my laptop operating system and the KeePass password and key people to notify on my death or permanent disability.    And several family members have that in a sealed envelope.