Replies

John,

Sounds like this has already been beat to death, but a few comments nonetheless (no magic solutions, though):

If you are going back and forth between Access 2007 and prior versions on the same machine, the delay you are observing is not surprising at all (for reasons Hans has mentioned) — I didn’t know you could even have them on the same machine. If you have Access 2003 (or 2000) on one machine and ONLY Access 2007 on another and you bring over your database (FE only presumably; BE out on a shared drive somewhere?) to the Access 2007 machine and you get the 7 minute start-up, this is indeed “curious” (as you obviously are). Do you observe the same delay if you do this with a self-contained (no BE) database (i.e., could it have something to do with establishing the links with the back end)?

When I first installed Office 2007 a month or so ago, all of my Access 2003 databases started up pretty much seamlessly with no notable delays. However, I have very few that are in a FE-BE configuration. One that is a “commercial-quality” FE-BE application, with the BE on a server and about 60 users (some 2003, some 2007) loaded without any unusual delay that I can remember.

Certainly a way to avoid the problem is to stay with 2003 or 2000, but this doesn’t answer the curiosity question — why’s Access 2007 struggling the first time it’s opening your Access 2000 database?

Hope this helps, a little anyway.

John,

Sorry to be so slow in getting back to you; obviously other Loungers have come to your rescue!

Here’s a brief accounting of my experience with the Access 2003 to 2007 transition (so far):

For my applications, we still have a mix of 2003 (less and less) and 2007 (more and more) users (who need access to the same database). To MS’s credit, this has been surprisingly painless. Of course, you have to retain the 2003 database format (.mdb) which 2007 handles satisfactorily. If you have a “buttoned-up” application (custom menu bar, database window inaccessible, etc.), Access 2007 will open the database in near 2003-look-alike format (your menu only, no ribbons, etc.). There are a few annoyances — macro/VBA security has to be dealt with when opening the database (which can be foreign to the users) — this can be dealt with by setting up trusted sites. The dead close button mentioned in this thread is annoying (I haven’t looked yet at some of the resources Wendell and Hans have suggested). On forms with list boxes, the highlighted list box entry tends to only be outlined rather than highlighted with a different background color, but this may have something to do with my VBA coding behind the form — but it does behave differently than 2003 which is an annoyance. I do use user-level security for some of my databases which works fine (again, I have retained the .mdb database format for backwards compatibility).

I have started doing development work on the .mdb databases in Access 2007, and haven’t encountered any significant problems (the biggest hurdle is getting used to the new interface); there seem to be lots of new form/control/report properties — I’m leary of how these will work for those remaining Access 2003 users, so I am avoiding them for now. Fortunately, the VBA Editor has retained it’s “classic” format, so there is no learning curve there. The biggest challenge may well be maintaining/ revising my custom menus (I haven’t had to deal with this yet using Access 2007) as Access 2007 does not have an interface for customizing menus and toolbars. They have to be maintained using VBA/Macros as described in some of the links provided by other loungers in this thread.

Once everyone is off Access 2003, it will probably make sense to bite the bullet and convert the menus/toolbars to ribbons (another learning curve, particularly since some of my applications dynamically change the content in the menus), revamp the user-level security to fit the security philosophy behind Access 2007 (I’m still ignorant on this — any good resources out there?) as user accounts/user permissions are absent from the new .accdb database format (as I understand it). It will also make sense to convert the .mdb up to .accdb (which may have to be done anyway to accommodate some of the above changes) and then deal with any other inconsistencies between the two formats. I guess I would rather spend my time adding to the capabilities of my applications, but I guess we have to move on. Of course, Access 2007 may well enable additional capabilities (e.g., RTF format for text/memo fields) that wouldn’t be possible (or at least as easy) in Access 2003. Keeps life interesting…

Regards,

Thanks for the tip. I’ll take a look at that alternative. Sounds like it might be a more elegant solution than the graphics option (even if placing the pictures were faster!). We do still have some Excel 2003 users (viewers) of this spreadsheet, so if it’s unique to the Excel 2007 file format, I’ll have to wait until everyone’s off Excel 2003.

Okay, here’s another peculiarity that maybe someone’s seen or can explain, or (better yet) can tell me how to fix!

I have a custom menu (Command Bar) that I configure during start-up based on the user’s permissions / access level. This works fine in Access 2003. When I try to run it in Access 2007 the configuration based on the user doesn’t happen during startup (I get the menu the way it was configured last via Access 2003 or manually via Access 2007 — see just ahead). If I open the application bypassing the startup code to get the Access 2007 user interface and then run the startup code manually, the custom menu gets set up properly but, of course, its in the Add-Ins tab as a Custom Toolbar. As long as I have both 2003 and 2007 users, I want (need) to retain the 2003 look-and-feel for this application, so I need to figure out a way to get this menu to get configured properly on start-up in Access 2007. Does anyone have an idea why it’s not?

Thanks.

Hans,

Yes, it is set to “Break on Unhandled Errors”. So I guess it can’t be trapped… (Does the huge negative error number indicate that it’s a “built-in” error?)

Thanks for your help.

Hans,

The line of code immediately preceding objMailItem.Send is On Error Goto Send_Error, so I think error handling is active, unless I’m overlooking something fundamental regarding error handling…

Hans & Andrew,

Thanks – certainly a more rational explanation for the behavior I was observing.

Thanks. Easy enough!

Mark,

Maybe this is a no-brainer, but how do you force the bottom one to get the focus (so the drop down shows only current staff) when the user clicks on the combo box to make a selection? Seems like the top one (with the full list) would get the focus. This is what happens for me when I test this technique.

Thanks.

John,

This technique works nicely for me — for single forms (for continuous forms, as noted in. It has the advantage that an additional control doesn’t need to be created (Hans’ solution). I’ll try Hans’ text control or Mark’s 2nd combo box solution for my continuous forms situations.

Thanks.

Hans/Mark/John –

Thanks for the tips. Very sneaky. I have this situation for both single forms and continuous forms. I’ll try Hans’ overlaid text box scheme first, although I’m wondering if this approach allows typing into the combo box to select an item or whether you have to use the drop down arrow. Also, there are a few cases where I allow new values to be typed in and, if confirmed, the new value is added to the list. I’ll have to see how this works with these “ghost” controls.

Hans & Jezza,

Thanks to you both. Between the two options, it looks like I can solve his problem!!

That’ll work… I’ll do the loop-through-the-QueryDefs thing — that’s what I was going to do with the Destination DB property anyway (until I couldn’t find it!).

Thanks, Hans!

Hans,

Progress…

No word yet from the network guys, but I’ve found an acceptable workaround, which I think may be helpful for others faced with this annoyance.

As described, I get the annoying alert when Outlook is not already open (and SpamBayes is disabled). However, one of my applications did not generate the alert when sending e-mail. I compared the code side-by-side and discovered a difference. If I add recipients to the mail message object using their Outlook name only (the Object Browser indicates the Add method for the Recipients collection needs just a string argument), the subsequent .Send method generates the alert. However, if I extract an AddressEntry object out of the namespace object (e.g., Set objAddressEntry = appOutlook.GetNamespace(“MAPI”).AddressLists(“Global Address List”).AddressEntries()) and then use this as the argument for the .Add method (e.g., Set objRecipient = objMailItem.Recipients.Add(objAddressEntry)), then the subsequent .Send method does NOT generate the alert.

I’d like to use ClickYes, but I fear deploying it in our rather controlled network environment may be an uphill battle. One consolation is that if Outlook is open to begin with, I don’t get the “A program is trying to…” warnings and, as noted in my previous post, the multiple instances are also avoided even if SpamBayes is running. This might be the lesser of the various evils. (The Network Bosses have not yet said “No” to configuring Exchange Server to bypass this annoyance, but I don’t expect them to deliver anything real soon.

Thanks again for your help.