Tony's Microsoft Access Blog

VBA Unit testing for Office

No idea how useful this is but a posting on Stack Overflow mentioned VBAUnit  VBAUnit is an extension of Beck and Gamma's JUnit unit-testing framework designed to work in Microsoft's Office2000 using Visual Basic For Applications.

Whoohoo – guest blog entry and Office Marketplace

My guest blog entry was posted by Microsoft today titled Power Tip: Update front-end database files that have been “pinned” to the taskbar.   And the Auto FE Updater is now on the Microsoft Office Marketplace

Auto FE Updater Version 2.15 – A whole new look on the main form

  The main form has been updated to use a toolbar rather than the old style command buttons.  If you have any problems please email me or phone me.  Details at the contact page.  You can download this version at this page.   If necessary you can download the previous version from the Version History page. 

This took a while to implement as various code modules wouldn’t work for me.  I also needed to find someone who could do the graphics for me.   I’m NOT an artist.

Several new Access 2010 books

Former Access MVP and current MS Access product group tester Jeff Conrad just announced he has completed the Access 2010 Inside Out book. 

Several fellow Access MVPs are coauthors of Access 2010 Programmer's Reference

Also see Access 2010 Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs

I’m sure there are other very good books out but these authors I know and have met.

(Note that there are no referrer links in the Amazon URL as I do not want to be perceived as being biased.  Support your local independent bookseller by seeing if they are price competitive.)  

Auto FE Updater version 2.13

Lots of minor stuff in version 2.13 with lots of updates for the Enterprise Edition which aren’t quite ready yet to be released.  You can download the latest version at the downloads page or from within the Auto FE Updater utility by clicking on the Tools menu and then Check for Updaters.

Added FE Started by user? check box to handle users pinning the Access FE database file to the task bar in Windows 7 and Windows 2008 Server R2.

Added Add Server to local Intranet Zone check box to handle annoying Publisher not verified message encountered by some networks when starting this utility

You can create shortcuts in My Documents for IT departments which don't allow creating shortcuts on desktops or elsewhere.  

Only master account and workstation can update server shortcut and cmd/bat files.    Users can muck with the UNC settings on the shortcut and change them to drive letters.  Whereupon the next time they ran the Auto FE Updater utility the server shortcut and/or cmd/bat files on the server would be changed.  Not a good idea. 

Added Update Master Auto FE Updater config file screen.

Both userid and workstationID must match for the developer to be considered a master user of this utility. In the past this was only the workstationID which was too limiting.  This is automatically set on first entry to the utility.  You can also add two more workstation and user iD combinations for other developers.

Add a start method "Display File Count Message" which only copies files from the server folder to the target folder and displays a message when completed. It doesn't actually start any programs.

Added Jet 4.0 Sandbox Mode

Added Access 2003 Security Level

Windows 7 and Windows 2008 Server R2 Task Bar Pinning

What I would've liked to have done is have the utility go through the pinned task bar items and see if a pinned Access database file was one of the files updated by the Auto FE Updater. However Microsoft have specifically disabled all access by programmers to the task bar as the Quick Launch bar was abused by developers. (Such as Adobe Quick Time and many others but we won't go there.)

P.S.  Please ask your employer to purchase a license to support features in the future.   There are now 12,625 lines of code in the utility.

What screen resolution is the smallest Access developers use these days?

I’m working on some Access forms as part of the soon to be released Enterprise Edition of the Auto FE Updater.  (After all Access is far better than Visual Basic 6 to display or update data in the tables.)   So what screen resolution do you use when working in Access?  What do you use when you Terminal Server into a client?  Or when you visit a client?

1280x1024 is what I’m thinking of targeting these screens for.

If I’m visiting a client for more than a day or two, and I’m driving which is 90% of the time, I bring in an ergonomic keyboard, wrist rest and LCD monitor.  If in a hotel room for more than three or four days I do the same in the motel room.    I’m sure the chamber maids think I’m nuts but …   I haul my gear around in hockey sports bags as they are long enough for the keyboard boxes and wrist rests.

How secure is my password?

Geeks are sexy have a page on this topic and a link to a website.    Note the tips on not actually putting in your password but using a similar one.  

There’s no reason why the US government couldn’t force the owner of that website to log all the passwords along with the IP addresses.  Then demand from your ISP your name.  Then target you.  Why yes, I’m a paranoid pessimist. 

My current Windows login pass phrase, although I did change some of the words but made sure they were the same length, comes in at 137 trillion years.  My login to my server is only 2 trillion years.   The pass phrase on my zipped backup file is 63 trillion years.  The default random 20 character password which the open source KeePass utility generates is about 2 sextillion years.

So long as there isn’t a backdoor in the operating system, KeePass or the WinZip program  I’m happy.  (I use WinZip to make a zipped copy of my files onto an SD card and then onto a DVD. Just double checked.  I’m using WinZip 9.0 SR1 which does not appear to have any vulnerabilities.)

A very convenient means of storing and using userids and passwords

Subtitle Weaning myself off the Microsoft Fingerprint Reader

I love using my Microsoft Fingerprint Reader.  I can use it to log into Windows.  I can click on a shortcut to a website, press the fingerprint reader and I’m logged into the website.  I love using it.  One of the best features is that you can use individual loooong passwords or pass phrases on each website.

But ….

It’s not supported by Microsoft in Internet Explorer 8 (although there is a Firefox Add-in which supports it), Windows 7 or 64 bit editions of Windows.  So I’ve been exploring open source options.  Turns out that KeePass Password Safe is an excellent alternative. I like open source alternatives as I trust the open source community to review the code and ensure things are kept secure and confidential.  

Side note:  After having a few discussions on this topic in years past with Microsoft personnel I do trust them to do a darned good job of keeping things secure and confidential.  

One of the very nice features in KeePass is the Autotype feature.  Took a bit of work to get the system-wide auto-type hot key working as the documentation wasn’t very clear.  But now I can be at a web page in Firefox or IE, hit Ctrl+Alt+A and it will fill in the user id and pass word for me.   So hitting three keys on the keyboard is just about as efficient as pressing on the fingerprint reader.  Which sometimes takes two or three tries.

One problem though is that a few websites won’t take a 20 character password.   Worse some will take it when you initially setup your account and silently truncate the extra characters.  Then when you log in a second time it doesn’t truncate your password but still does the comparison so your passwords now don’t match.

The biggest inconvenience is going to be logging into Windows.  I’m going to be loosing that functionality.   Well, I’m using a pass phrase that I can just about rattle off in my sleep so I guess that won’t be too bad.  Needless to say though I’m not going to be in any hurry to upgrade my current Windows XP laptop.

Do ensure that you write down your Windows password and your KeePass master password and give to a few trusted relatives or friends who don’t live near you in a sealed envelope.   Actually I have these in a special KeePass entry in the notes section so it’s quite secure.  This way I can copy and paste into Notepad, print and distribute.

If you are going to switch passwords ensure you’ve distributed the passwords before you actually start using them.  Why?  Well, what if you are disabled or dead before you get around to notifying your trusted friends and relatives.  Also we have the best of intentions but I know that I don’t always get around to doing some things on a timely basis.

Note that one of the most important things you can do is to use a different password for every site you visit and your computer.   There are many stories of crackers getting thousands of passwords from sites with bad security and using the same userid and password at other sites.  This is why products such as KeePass is so important as they enable you to conveniently store and use different passwords for each website.

10 Things To Improve Your Development Career

Interesting blog posting at 10 Things To Improve Your Development Career  

During high school I tool a programmable logic controller course at the local college.  I was the youngest person in there by about 15 or 20 years.   The most useful part though was learning about how NAND gates and such worked which are the basic building blocks of CPUs.   I also played around a bit with assembler. 

I also don’t own a TV.  

Auto FE Updater and shortcut icons

I received the following email:

Every so often, my desktop shortcut appears as follows, even though my network connection is fine:

So, I'm wondering if it is valid to use something like this:

ShortCutIconFile=C:\Apps\ABC\Airplane.ico

I had a dim memory of someone else requesting a change in this area a while back so I dug through the code.   Oops.   I had made changes to the code to support parameters such as %user% and %userprofile% but hadn’t updated the online help page for the Shortcut Icon or the screen itself in the Auto FE Updater.  That page has been updated and the next version of the utility will have the screen updated.

Tom W, I do apologize for my negligence and thanks muchly for asking.

I've never quite trusted ADO because …

I've never quite trusted ADO because I've never understood how their versioning system works. I just couldn’t see the various versions DLLs on my system and yet the Access references window showed five or six different versions..   Jet 3.5/DAO 3.5/3.51, Jet 4.0/DAO 3.6, and now ACE, as well as Jet back in Access 2.0 and Access 95,  have always made a lot of sense to me. If there's a patch then msjet35.dll/dao350.dll or rmsjet40.dll/dao360.dll get updated.  If there's a different version you get DLLs with the different version number on them.  You can also see the DLL files in \Windows\System32 or C:\Program Files\Common Files\Microsoft Shared\DAO

ADO always had me confused. Look at the references list and you can see a bunch of different versions all pointing to a TLB file. I didn't really understand what a TLB file was until recently so that didn't help either.  The TLB acronym stands for type library file.   A TLB file, somehow, provides an interface and points to other files.  Another definition is “A Type Library is a file that describes software implemented in another file(DLL/EXE/OCX). “   I’ve been using a shelllnk.tlb file for many years in my Auto FE Updater utility so it can create short cuts.

In the context of ADO, type libraries are used to insure that the software developer does not use new features that only exist in later versions. For example, if the developer used a feature that only exists in Windows Vista and after, i.e. ADO 6.0, then the software will not work on Windows XP.

In ADO, for example, the ADO 2.7 Library TLB file has the identical binary interfaces that ADO version 2.7 itself had.  But msado27.tlb points to/interfaces with/references/whatever msado15.dll. See COM, DCOM, and Type Libraries  for a definition if that page helps or confuses.  Another definition is the first paragraph at What Is Type Library and How Is It Related to the Registry?

Thus the each version of MDAC/ADO updates all the various files. Unlike Jet/ACE there is only ever one set of files for all the versions. Although granted there are almost certainly more DLLs in later versions.

If you want to use a previous versions of ADO then you reference the appropriate version  TLB file which in turn points to the latest version of msado15.dll file.  The TLB files make the ADO interface to Access, or VB 6 or whatever, look like the same as that version of ADO when it was installed.  But it’s really using the latest version of ADO to do the work.

You can see that quite clearly in these screenshots of the Access VBA references window.    (I’m still on Windows XP SP3 if that matters.)  ADO 2.7 references the TLB file while 2.8 references msado15.dll

Windows Vista , Windows 7 and Windows DAC (WDAC) 6.0

For Windows Vista MS decided to rename MDAC to Windows DAC and give it version 6.0. FAQ About Windows DAC/MDAC (The second section of that article tells you that version information is available at a KB article but that KB article only goes to MDAC 2.8. <sigh>)

ADO is version 6.0 in Windows Vista when you view the properties of msado15.dll and in the Access references list. In Windows 7 the DLL properties show version 6.1 but version 6.0 is displayed in the Access References Window.   Confused yet about 6.0 and 6.1?  Mind you that does make a bit of sense as MS, other than the Access product group, is now on some flavour of ADO.Net, etc, etc.

Also in the above screen shot from Windows 7 you can see the ADO 2.8 reference which is now a TLB file although I didn’t grab a screen shot.

Conclusion

If you have any users in fully patched Windows XP SP3 or Windows Server 2003 SP1 then you want to stick with ADO 2.8. If all your users are on Windows Vista, Windows 2008 Server or Windows 7 then you can use ADO 6.0.  If you’re not sure if the users are on Windows XP SP3 then stick with ADO 2.7. 

If you think you might have some Windows 2000 users then stick with ADO 2.5 which is what was shipped with Windows 2000. But at this date you shouldn't have Windows 2000 users as the OS won't be getting any more security patches.   There is a redistributable MDAC 2.8 available but what’s the point?

Note that, like Jet 4.0/DAO 3.6, MDAC/WDAC/ADO are part of the OS and have been since Windows 2000. For example KB article 231943 states that Windows XP SP2 installed MDAC 2.8 SP1.  And you can’t install WDAC 6.0 on Windows XP or MDAC 2.8 on Windows Vista.  So don't bother attempting to install an older or newer version. Embrace what is already present.  (How's that for a Zen comment?)

And don’t download a version from a site other than Microsoft if you are looking to solve a problem.   When I was doing some research for this article using search engines i was amazed at the number of scummy websites.

Finally what’s the difference between ADO 2.1, 2.5, 2.8 and 6.0?  I don’t know.   I have seldom used it which is the point of this lengthy blog posting.

Somewhat Useful links

How to check for MDAC version
MDAC Utility: Component Checker
Microsoft Data Access Components (MDAC) release history
Support has ended for MDAC 2.7 Service Pack 1 and MDAC 2.8
Data Access Technologies Road Map
MDAC Downloads   (but you shouldn’t need to download anything other than the component checker)

Note

Undoubtedly I will be getting some comments.  I may change the contents of this blog so check back in a few days.  I will attempt to mark the updates by italicizing them or in some other fashion but we’ll see how that goes.

Microsoft Office Diagnostics fixed “SendObject stopped responding”

A posting titled SendObject stopped responding caught my attention.  in it Rick stated “I finally ran the microsoft office diagnostics and it found one error and repaired it. “  You can find Microsoft Office Diagnostics on your Windows XP Programs menu at Windows Office <inset year here> menu >> Microsoft Office Tools.  Or search for it in Windows Vista and Windows 7.

Thanks to Rick for posting his experience.   I also thank Len Hannam of the Visual Basic and Developers user group in Edmonton for mentioning this tool a while back.  I must confess I never noticed it before.

Kicking out users after inactivity

I just received an email thanking me for the Auto FE Updater and expressing a certain amount of annoyance at users who don’t exit Access at the end of the day.

I use the following to kick users out so I can make changes to the back end after hours.
HOW TO: Detect User Idle Time or Inactivity in Access 2000 (Q210297)

However we found that the code which runs on the timer event must be disabled for the programmers.  Otherwise weird things start happening when you're editing code.  Use the following function to enable it for users.  Assuming you give your users an MDE/ADE/ACCDE.   (I don’t know where I got the following code from.)

Public Function IsMDE() As Boolean

Dim db As DAO.Database

    On Error GoTo tagError

    Set db = CurrentDb
    If db.Properties("MDE") = "T" Then
        IsMDE = True
    End If
tagNoProperty:
    db.Close: Set db = Nothing

    On Error GoTo 0
    Exit Function

tagError:
    Select Case Err.Number
    Case 3270 ' Property not found
        IsMDE = False
        Resume tagNoProperty
    Case Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure IsMDE of Module mdltt_FixReferences"
    End Select

End Function

Also print preview would sometimes not allow the users to run a menu item to export the report to Excel or others.  So you had to right click on the Previewed report to get some type of internal focus back on the report so they could then export it.  This was also helped by extending the timer to five minutes.
The downside to extending the timer to five minutes was if a person stays in the same form and at the same control for considerable parts of the day, i.e. someone doing the same inquiries, the routine didn't realize that they had actually done something.  I'll be putting in some logic sometime to reset this timer whenever they do something in the program.

Bug with Access and SQL Server: Erroneous #Deleted bug on Insert with null values

When a new row is inserted into a view that uses identity but contains null values for at least one field and Access' subsequent attempts to locate the new row uses "a_field = NULL" and thus will always fail to find that row and return #Deleted.

As viewed in this thread Access 2007 bug with SQL Server Back End, a user AVG has an open case with Microsoft and has been told that it is a bug. Fellow Access MVP Ben Clothier can reproduce this in Access 2003 and against a base table as well so it's not restricted to views only. Because the scope includes even plain old tables this can be problematic and very confusing due to its intermittent nature.

Paragraphs added:  "and against a base table as well so it's not restricted to views only. Because the scope includes even plain old tables..."

The tables should be qualified to Non-SQL Sever linked tables. This occurs because from I can tell, Access will only look for keyword "identity" against linked tables that are said to be from SQL Server. Any other ODBC linked table will be more vulnerable to that bug because Access will not make a call to "SELECT @@identity;" if it's not a SQL Server linked tables. This bug occurs whenever Access has to fall back on the last resort of comparing every fields.  End added

As the user AVG noted, he was following advice from MSDN article Optimizing Microsoft Office Access Applications Linked to SQL Server by Andy Baron regarding using a view and INSTEAD OF trigger:

Thanks to Ben Clothier for writing the above summary and AVG for finding the problem and reporting it in the newsgroups.  (I’ll take a small amount of credit for urging AVG to post the problem when he was asking about problems in SQL Server.)   Fellow newsgroup denizens Banana and David W. Fenton did a great job with suggestions in that thread.

How do you create a mdb database file in Python?

An interesting question was asked on StackOverlow. The answer was surprisingly easy (assuming the answer was accurate of course. <smile>)  See How do you create a mdb database file in Python?

I should add that I have no idea what Python is.  I just thought this was interesting how familiar concepts such as DBEngine works in another programming language.

Puzzling problem with Access’s built in Shell function

I was testing some code, which will be available in a few days as part of a new release of the Auto FE Updater, which uses the Access shell function to start the Auto FE Updater.

I could hear the beep sound but I couldn’t see the customized form to display messages that I was expecting.   So I went to task manager and cancelled the two occurrences of StartMDB.EXE.   I tried it again and then, somehow noticed a little addition on my screen.   See the screenshot below. 

Of course I highlighted it in red for you. 

Added.  David Fenton rightfully pointed out it was too small to view so here’s just the lower left hand corner:

The heavy red lines obscure things a bit but basically what you see there is pretty much what I saw when trying to figure this out. End Added

I was quite mystified as to what was all happening and why I couldn’t see the form I was expecting to see. By a fluke I clicked on the three periods in the window title bar and was able to view the form.  But I was still puzzled.

Kept on working and suddenly the Aha moment.  (Where’s an image of a light bulb when you need one?)

Turns out the default value in Microsoft Access’s Shell WindowStyle parameter is to use the vbMinimizedFocus constant.  This default puzzles me but no big deal.  I used vbNormalFocus and all is well.

Updated to add the small middle graphic file and a paragraph of explanatory text.

Misleading error message when creating relationship between two tables with lengthy names

If you attempt to create a relationship between two tables with a combined table name length greater than 64 characters you will get the following confusing error message.

"<combined table names> is not a valid name.  Make sure that it does not include invalid characters and that it is not too long."

This message does not explain exactly where the problem lies.  Among other things the create relationships is doing is that Access tries to create an index on the foreign key in the child table which has the name of the two concatenated tables.  (Even if the index already exists.)

If you try to create the index yourself with the two concatenated tables names you will get a much better error message.

"The index name is invalid.
The index name may be too  long (over 64 characters) or contain invalid characters."

A workaround is to temporarily rename a table so the combined table names are less than the 64 characters.  Create the relationship and the rename the table back to the original name.

Thanks to inungh for asking the question titled “Number of characters in a table name”

An amusing bug – Report with 36505 pages has negative page numbers

A most amusing thread ms access negative page numbers

“I have an access report that generates 36505 pages (un filtered, and about half of each page is taken up by group headers and page headers) , though the footer at the bottom of the report page says "36505 of -29031".”

Some good sleuthing found that the Pages variable is an integer while the Page variable is a long.   I can just see the discussion in the Microsoft Access product group.  “But nobody will every have a report longer than 10,000 pages”   <smile>

Also there is a work around for this problem in another posting.   Well, I suspect if you hit 65K pages you will run into the problem again but hey, no report will ever bet that large.

Please, please backup your photos - Heartless thief steals memories of a lifetime

Heartless thief steals memories of a lifetime - “A thief skulked into a Toronto hospital for cancer patients and stole a laptop loaded with priceless family photos of a 17-year-old girl’s last three years in her fatal fight against cancer.”

Make multiple copies of the DVDs and put at least one set at a trusted relative or friend.

The Next Y2K

The Next Y2K

Karl talks about 2038 which we can all ignore, right?    After all it’s a Unix problem and it’s about 28 years away?  Not quite.    At the very least be aware of this issue. 

(Are various versions of Unix 64 bit??)