[AccessD] Conversion A97 to A2k

Andy Lacey andy at minstersystems.co.uk
Thu Feb 5 13:17:39 CST 2015


Thanks Lambert, that's really useful. As well as me converting to A2K the
company is moving from Lotus Notes to Outlook. I have Outlook code, having
converted the other way about 5 years ago, but this will be a big help going
from A97/Notes to A2K/Outlook.

Cheers

Andy

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: 05 February 2015 18:35
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Conversion A97 to A2k


The only problem I had switching Apps from Access 2003 to 2010 was to do
with emailing using Outlook. I have a bunch of canned routines to handle all
my  mailing needs, which all reduce to a few calls to a function which
verifies the email addresses of the recipients, and then a final call to a
'SendMessage' routine.

Typically I create an Outlook object and then immediately create a Mail
item...

	Set objOutlook = CreateObject("Outlook.Application")
	Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

Then for each email address I call a routine to check the address before
adding it to an Outlook.Recipient object.

When I converted to Office 2010 the verify email address code hit problems.
The code just creates some temporary Outlook objects and used the .Resolve
method to check if the email address looked good.  This always just works
(from Access 97 onwards), but in A2010 if Outlook was NOT ALREADY RUNNING an
error resulted.


Here's the code, with the problem line indicated...

Function bVerifyEmailAddress(CM_Email As String) As Boolean
' Verify if email address is valid
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
        
   On Error GoTo bVerifyEmailAddress_Error
    If Not IsBlank(CM_Email) Then
        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")
        
        ' Create a message object
    
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        
        With objOutlookMsg
             ' Add the To recipient to the message.

	'++++++++++++++++++++++++++++++++++++++++++
             Set objOutlookRecip = .Recipients.Add(CM_Email)
	'+++++++ above line causes an error 287++++++++++

            ' Resolve Recipient's name.
            For Each objOutlookRecip In .Recipients
                If Not objOutlookRecip.Resolve = True Then
                    objOutlookRecip.Delete
                    bVerifyEmailAddress = False
                Else
                    bVerifyEmailAddress = True
                End If
            Next
        End With
        Set objOutlookRecip = Nothing
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing
    Else
        bVerifyEmailAddress = False
    End If
bVerifyEmailAddress_Exit:
   On Error GoTo 0
   Exit Function
...


The solution was to trap error 287 and then launch Outlook. To do that I had
to write a little routine to go find Outlook...

bVerifyEmailAddress_Error:
Dim strOlPath As String
    Select Case Err.Number
    Case 0 ' No Error
        DoEvents
    Case 287 ' Application-defined or object-defined error
    ' In Access 2007/2010 the call to .Recipients.Add generates this error
if Outlook is not already running
    ' So here we will locate Outlook and then run it
    strOlPath = FindOutlook()
    If strOlPath > "" Then
        Shell strOlPath, vbMinimizedNoFocus
        Resume
    Else
        MsgBox "Error sending Email message. Please start Outlook then click
the 'OK' button and we will try again.", vbOKOnly Or vbExclamation
        Resume
    End If
...

And this is how I locate Outlook...

Function FindOutlook() As String
    Dim strOlPath As String
    Dim strFile As String
    Dim strTemp As String
    Dim n As Long
    ' try the folder that Access is installed in first
    FindOutlook = ""    ' default
    strOlPath = SysCmd(acSysCmdAccessDir)
    strFile = Dir(strOlPath & "Outlook.exe")
    If strFile & "" > "" Then
        FindOutlook = strOlPath & strFile
    Else
        ' not found. Search the Program Files folder
        strTemp = Split(strOlPath, "\")(0) & "\" & Split(strOlPath, "\")(1)
        With Application.FileSearch
            .LookIn = strTemp
            .SearchSubFolders = True
            .fileName = "Outlook.exe"
            .Execute
            If .FoundFiles.count > 0 Then
                For n = 1 To .FoundFiles.count
                    If GetFileName(.FoundFiles(n)) = "Outlook.Exe" Then
                        ' got it.
                        FindOutlook = .FoundFiles(n)
                        Exit For
                    End If
                Next n
            End If
        End With
    End If
End Function

' End Code

HTH

Lambert


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
Sent: Thursday, February 05, 2015 11:57 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Conversion A97 to A2k

Changing the Subject  to be more relevant.
 
Well actually, this may be misplaced confidence but having done the
conversion a couple of weeks ago and then driven myself crazy with ribbons,
commandbars etc this is the first crash I've had. I've run most of the
biggest routines and they seem to be standing up ok, largely untouched. I
have one heavy user who has had it for a couple of days and it was he who
had the crash that started this thread, but that's the only one he's had and
he's on the system all day every day. I don't doubt there'll be some
problems down the line but for now......(touches large piece of wood)
 
Can anyone think of any banana skins they've skidded on during such a
conversion? 
 
Andy
 
 

> On 05 February 2015 at 16:38 Gustav Brock <gustav at cactus.dk> wrote:
>
>
> Hi Andy
>
> I see. So that's how it is.
> It will be a massive amount of work. Couldn't you team up with some? 
> Sounds like a job that could kill anyone.
>
> Another option is to run it off a terminal server. You know about
RemoteApp?
> This smart feature will run the application in a frame on the client 
> desktop appearing as any other desktop application. The normal user 
> won't notice the difference.
>
> /gustav
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] På vegne af Andy Lacey
> Sendt: 5. februar 2015 17:14
> Til: Access Developers discussion and problem solving
> Emne: Re: [AccessD] Change in the evaluation of IIF, or something else?
>
> Yea :-(
>
> I wouldn't even be getting into this if it weren't that it's getting 
> harder and harder to shoe-horn A97 onto new pc's.
>
>
> > On 05 February 2015 at 16:05 Gustav Brock <gustav at cactus.dk> wrote:
> >
> >
> > Hi Andy
> >
> > Uh. That will keep you busy for a while.
> >
> > /gustav
> >
> > -----Oprindelig meddelelse-----
> > Fra: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] På vegne af Andy Lacey
> > Sendt: 5. februar 2015 17:00
> > Til: Access Developers discussion and problem solving
> > Emne: Re: [AccessD] Change in the evaluation of IIF, or something else?
> >
> > Hi Rocky
> >
> > A run of a little routine tells me:
> >
> > 692 Forms of which 492 have modules
> > 354 Reports of which 120 have modules
> > 79 actual modules
> >
> > 200,000 lines of code in total
> >
> > The potential for more banana skins is.......significant, shall we say?
> >
> > Andy
>




More information about the AccessD mailing list