Tony's Microsoft Access Blog

Access 2010 and Sharepoint discussion

2009-11-07T21:02:22Z

Bob Alston, a comp.databases.ms-access newsgroup regular, asked about Access 2010. A very interesting discussion with fellow access MVP Albert Kallal with his usual lengthy and informative postings has started. Albert has posted a nice collection of current links to the Access product group blog as well as his own video.

Windows 7 development reorganized

2009-11-02T21:02:30Z

Steve Sinofsky took over management of the Windows development team after Windows Vista. He was in charge of Office development for a number of years prior. I’ve been reading snippets over the past few years about how the development methodology of Windows 7 had changed. And how Windows Vista development methodology had become too complex.

Now an interesting post by Larry Osterman, long time MS Windows OS developer mentions that. Windows 7 Reflections… In particular read the second last paragraph, the last long paragraph.

Let me also add that I have a great deal of respect for the Microsoft, and other successful, large team development efforts. As you can imagine I mostly work by myself on apps which I have created. With Access when you’re testing things you generally only need to test one or a few related forms or reports. Once the correct data gets into the table you’re done. You don’t need to worry about this bit of external code or that global variable. After all, in my apps, global variables come from the global options table.

But with the Auto FE Updater it’s quite different. There are a lot of global variables and a minor change over here can cause significant problems in an unexpected area over there. Another way of putting this is I really need to figure out some means of doing thorough automated testing with all the possible options. But I’m too busy right now. (Yeah, I can just see some of you shaking your head and thinking “Tony, you’re gonna get real busy if you don’t take the time now.)

My Granite Fleet Manager is being used by a number of Hutterite colonies. Good, intelligent people but they’re not that computer literate. Indeed my app may have been the reason they’re seeing a computer for the first time. Every once in a while I’ll get a phone call about a bizarre problem. Once I realize what they did I realize that I should’ve put better error checking in that particular field. What they did makes sense from their viewpoint. It just hadn’t occurred to me that someone might do whatever it is they did.

As a result I can now understand why there’s generally a developer and a tester working together. And the more “twisty minded” the tester the better.

P.S. I added an Auto FE Updater group in LinkedIn. Feel free to join the group or email me your irritants, pain points and suggestions.

resolver one – an insulting advertisement

2009-11-02T05:03:11Z

While at StackOverflow I came across the following advertisement.

Note to resolver one advertising folks. I like VBA. There are improvements I’d like to see in the VBA editor but I do like VBA.

So kiss my ***

A literary appreciation of the Olson/Zoneinfo/tz database

2009-11-01T20:06:06Z

Ok, so this posting is not about Access but it is about a database/file with a lot of interesting historical information.

A literary appreciation of the Olson/Zoneinfo/tz database

Added an Initial User Install page to the Auto FE Updater site

2009-10-31T21:06:09Z

I added an Initial User Install page to the Auto FE Updater web site.

If you use a different method of setting up initial startup for your users please let me know.

Also please let me know if you managed to figure out how to email a file share URL so Outlook doesn’t mangle it. <sigh> Either I’m dumb or Outlook is being way to frigging helpful.

Jackcess is a pure Java library for reading from and writing to MS Access databases.

2009-10-24T21:51:45Z

I just came across this interesting Open Source project - Jackcess is a pure Java library for reading from and writing to MS Access databases. Also read the FAQ. Also note the link to MDB Tools which hasn’t been updated since 2004.

Jet Engine History

2009-10-24T21:01:48Z

A question started a bit of digging and poking about. I’ve always been interested in the history of Access and Jet and the code names used way back when such as Cirrus. The Wiki Microsoft Jet Database Engine article had a footnote referencing a page titled Jet Engine: History

Regretfully all the links mentioned on that page no longer work. Although some of the MS white papers mentioned might still be available by digging.

Which reminds me. There are two versions of the Microsoft Jet Database Engine Programmer's Guide. I have the first edition which is the one with the light blue cloud cover which was sort of the theme of Windows 95. I should purchase the second edition which has a white cover just to ensure it’s part of my collection.

And I should spend a few minutes rereading the table of contents just to refresh my memory so if I ever need some information I’ll at least know that it’s available.

Office DevCon in Australia

2009-10-12T22:35:08Z

For those of you who don't know, Office DevCon (http://www.officedevcon.com.au) is the largest and most popular annual community-driven conference in the southern hemisphere. It allows Microsoft Office developers and power users to come together in one location to hear expert speakers from around the world present on a wide range of Microsoft Office-related topics.

Over 2 days, you will hear in-depth presentations on advanced Office System technologies for Office 2003 and 2007, and 2010 (the next version), including Word, Access, Excel, SharePoint, Groove, and InfoPath. In essence, we are launching Office 2010 in Australia, so if you want to get up close and personal with Office 2010, you'll see it here first!

This year, the conference will be held at the University of Queensland (St. Lucia campus) over the weekend 31st October-1st November 2009. The conference is FREE to attend, but to allow the organizers to plan resources, please register at http://www.officedevcon.com.au/2009/register.htm.

We'd love to see you there. This only happens once a year, so don't miss out.

Regards,
Graham R Seach
Microsoft Access MVP & Conference Organizer

Sites pulling sneaky Flash cookie-snoop

2009-10-08T02:13:15Z

“What's far less well known is that Adobe Flash software also features cookies that can be used in much the same way as HTTP cookies. Flash cookies can be used for storing the volume level of a Flash video but the technology can also be used as "secondary, redundant unique identifiers that enable advertisers to circumvent user preferences and self-help", the academics warn.

A significant percentage of websites including federal government sites use this Flash-based technology to track users, the researchers discovered. The technology is sometimes used as a means to "undelete" the information in browser-based cookies that a user might have thought they had cleared from their system when they deleted their browsing history, the academics explain.”

http://www.theregister.co.uk/2009/08/19/flash_cookies/

More info and a script here Online Privacy Tips

Added - Jim Mack informed me of the CCleaner (apparently the first C is short for cr*p) that cleans those up. I cleaned my system using the above mentioned script an hour or two ago and since found three flash cookies from a website ytimg.com for which no web page exists but is apparently for YouTube. Which makes sense as I had indeed visited YouTube in there.

Is Nothing

2009-10-05T11:09:04Z

I’m working on some largish updates to the Auto FE Updater and I wanted to ensure that the new routine which logs errors would properly handle the situation if there were errors in the error logging routine. A great deal of searching a dimly remembered discussion brought me to fellow Access MVP Dirk GoldGar’s posting on this topic. How to know when a Connection or Recordset is open in Programming

If Not rs Is Nothing Then
    rs.Close: Set rs = Nothing
End If
If Not gblLogdb Is Nothing Then _
    gblLogdb.Close
MsgBox "The following error occurred in procedure Log_NewSession of Module Log" & vbCrLf & vbCrLf & _
    Err.Description & vbCrLf & vbCrLf & Command()

Note that the standard error handling routine, including the sub/function name and module name is created by MZTools. An excellent free utility which I use all the time.

I was rather startled. This isn’t even documented in Access 97 help. Allen Browne has a page on this general topic - Nothing? Empty? Missing? Null?

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

2009-09-30T06:15:47Z

Added ShortCutComment to INI file for the user to see when hovering over a shortcut.
Fixed bug where if creating both a Program Files shortcut with a folder and a desktop shortcut the utility would attempt to add the shortcut to a folder to the desktop
- Thanks to Noel Mann of New Zealand for the ShortCutComment suggestion and the above bug report
Utility will now correct shortcut icon where the user has updated that field or the admin wants to change them.
Fixed various minor bugs and created better error messages mostly in the area of shortcut handling

Visit the Auto Fe Updater site to download or click here.

Website Updates - Microsoft Access Email FAQ - CDO aka CDONTS

2009-09-28T18:06:12Z

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

InStrRev

2009-09-23T21:32:04Z

I’ll be darned. I had no idea InStrRev existed in VBA code. It is new in Access 2000 so it’s been around for nine years.

I had to build this function myself in A2.0 or A97 to get the file name from a string which had the path and file. The code needed to find the right most occurrence of “\” back slash. I’ve been happily using my function ever since.

I actually learned something new about Access this year. <smile>

Queries and the 3061 "Too few parameters. Expected 1." message

2009-09-23T04:32:14Z

Your query is working just fine referencing a form to select some records:

but when you run it in code you get the dreaded 3061 "Too few parameters. Expected 1." message.

One option is to evaluate the Forms![Form name]![Field Name] expression as per the following examples.

The below example is an execute query.

Set db = CurrentDb
Set qdf = db.QueryDefs("LEMBatchesRpt Append to temp table")
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i) = Eval(qdf.Parameters(i).Name)
Next i

qdf.Execute dbFailOnError

The below example is used in my standard routine which copies the contents of a recordset into an Excel spreadsheet. Due to the requirements of basing the openrecordset on a query to which I added a Where clause I created a temporary query and then did the parameter evaluation

strSQL = "SELECT * FROM [" & strQuery & "]"
If Len(strWhere) > 0 Then _
strSQL = strSQL & " WHERE " & strWhere & ";"
Set qdf = db.CreateQueryDef("", strSQL)
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i) = Eval(qdf.Parameters(i).Name)
Next i
Set rs = qdf.OpenRecordset(dbOpenSnapshot)

….

' Insert recordset into cells starting at left hand side and second row
' Using a copyfromRecordset is much, much faster than stuffing in the cells one at a time.
.range("A2").CopyFromRecordset rs

This is used in a generic routine behind the very flexible reporting form in the Granite Fleet Manager. When the user clicks on the Export Report to Excel command button I look at the record source of the report to fetch the query name. I then build the where clause and execute the above code.

New version of the Auto FE Updater available

2009-09-23T03:52:26Z

Added ProgramsMenuFolder option to create a folder in your Start >> All Programs menu for such shortcuts.
Added buttons to open the server folder or the Main App Folder in Windows Explorer. If the user gets an error message they only see the button to open the Main App Folder.
Fixed bug where StartMDB.exe was staying in memory introduced in version 1.77
Changed StartMethod=FileExtension to use a different API call as the API call I chose to use did not handle file extensions longer than 3 characters consistently including ACCDB and ACCDE.
Remove Startmethod=FileExtensionSimple as it is now the same as StartMethod=FileExtension
Documented NewINIPathAndFile in the INI pages and updated the FAQ page. "This line is only used when moving servers or such as this will rewrite the path and name of the StartMDB exe and INI file in the target of the shortcut. This line is only placed in the old INI file."
Minor bug fixes, and some better error handling and clearer error messages in a few places.

Visit http://www.autofeupdater.com/download.htm to download

Auto FE Updater - "The publisher could not be verified. Are you sure you want to run this software?" message

2009-09-13T22:34:27Z

I came across a posting that explained how to remove this message on an Intranet for a domain. Note that I’m not a security expert. Until I get a digital certificate you’re going to have to persuade your IT department.

"Open File - Security Warning: The publisher could not be verified. Are you sure you want to run this software?"

FindExecutable API call fails for ACCDB file extensions

2009-09-13T21:22:58Z

The API call "FindExecutable: Find Exe Associated with a Registered Extension" fails on a file with the ACCDB extension. The API call does work fine with the MDB extension. Clicking on an ACCDB file in Windows Explorer does work. This is a on a new, fresh Win XP/Access 2007 install with nothing else on the system and no uninstalls, etc.

This has been confirmed by folks over in the microsoft.public.vb.general.discussion newsgroup.
Microsoft Communities - FindExecutable API call fails for ACCDB file extensions
Google Groups - FindExecutable API call fails for ACCDB file extensions

Note Mike Williams statement: "There were all sorts of strange anomalies where certain file extensions were found by FindExecutable and other file extensions were not, with (up to now) no apparent rhyme or reason behind it. "

Irony/Stupidity/Blind spot

That said ShellExecute will do what I want. I had a blind spot and forgot you could send it parameters. So I’ve spent several hours getting this API call working, or so I thought until someone hit this bug, for nothing. <sigh> Worse, I could’ve added some requested functionality to the Auto FE Updater a long time ago. <bigger sigh>

I will be issuing a new release of the Auto FE Updater in the next few days after I fix up a few other minor bugs.

A new version of the Auto FE Updater is available

2009-09-08T22:53:11Z

A new version of the Auto FE Updater is available at my new, somewhat reorganized website http://www.autofeupdater.com/

Added FileExtension and FileExtensionSimple to the StartMethod. This now means the utility can be used for ADP/ADEs or any other file extension really. This method can be easier for the developer for testing purposes than AutoSelect if the developer is using Access 2003 on their system and has Access 2007 installed as the AutoSelect starts the newest version of Access. Thus causing the annoying wait while Access 2007 installs itself.

Added a debug checkbox to the developers form so you can see what the Auto FE Updater wants to do before you run the utility

Updated website showing the CreateShortCutOnPrograms and CreateShortCutOnCommonPrograms options which have been available for some considerable length of time but weren't documented on the website. Oops.

Eagerly anticipated Access 2007 hotfix

2009-09-01T00:11:59Z

Description of the Access 2007 hotfix package (Access-x-none.msp, Ace-x-none.msp): August 25, 2009
http://support.microsoft.com/default.aspx?scid=kb;en-us;973405

Dunno what happened in the KB article title but this is a hotfix a lot of folks have been waiting for.

Dual monitors on the laptop

2009-08-13T21:20:47Z

I did some searching recently and located some inexpensive devices designed to add a second monitor to your laptop. I'm quite happy with the StarTech USB VGA External Dual or Multi Monitor Video Adapter.

Typically I move the task manager, OneNote and the VBA editor window to the right hand screen. I use OneNote for all my short term notes on what I'm doing.

Details

Note that I've been using dual monitors starting in about 2000 for programming and quite liked it. However when I dropped the one client I moved the tower system that had a Matrox dual VGA video card home. Then as I was visiting other clients for short periods of time I didn't feel like setting up the tower system instead relying on my laptop.

In the past these devices were upwards of $500 so I didn't feel like spending that much money. Matrox have some nice looking cards but they state that it only works on certain graphics adapters. Likely most of them but that seems slightly limiting.

So I purchased a StarTech USB VGA External Dual or Multi Monitor Video Adapter I've been using it at a client along with the DVI output on the laptop. While it has its relatively minor quirks it works quite nicely. That particular device is ten or twenty dollars more expensive than the cheapest version but it supports more video resolutions so I don't mind spending the extra money.

Clearly Windows is smart enough to move the apps back to the main monitor once you disconnect the multiple monitors.

The senior managers at a client started using dual monitors at the suggestion of Jim my friend and the IT manager. They liked them so much, and the price of LCD monitors is so low, that just about every office person is now using dual 22” LCD monitors. With the exception of a few people such as the receptionist or others who don’t spend much time in email or Excel.

Quirks

It is noticeably slower on the screen refreshes when moving applications around. Well, I don't do that often so no big deal. I’m sure the device would such big time at gaming. But not a problem for me.

I use a toolbar on the left hand side of my desktop to launch my favourite 20 or 30 applications. Similar to Quick Launch but it's on the left hand side. After disconnecting this device the LHS (Left Hand Side) Launch toolbar only displays a shadow. Logging off and on redisplays it properly so that's relatively painless. However this problem hasn’t happened lately so I don’t know what I’ve done to solve this.

Future

According to the product page you can have up to five of these devices hooked up simultaneously. Well,that's a bit much even for me. However there might be times I want a third monitor. OTOH what with inexpensive 24" LCD monitors now ... <shrug>

The device driver supports rotation. So I may just see if I can easily rotate the right hand monitor so I can view more code.