Using Access as a DDE client, the initial DDE statement will not work

Solution or Workaround

Code:

Function AVInitiate ()

On Error Resume Next                            '-- Set up error handler.

Dim chan, I, request   As Variant               '-- Declare variables.

chan = DDEInitiate("arcview", "System")         '-- Establish ArcView link.

If Err Then                                     '-- If error occurs, ArcView
    Err = 0                                     '-- isn't running. Reset error
    I = Shell("c:\av2\bin\troy C:\av-data\adesk.APR", 1)     '-- and start
ArcView.
    If Err Then
        MsgBox "DDE connection error"
        Exit Function
    End If        
    chan = DDEInitiate("ArcView", "System")     '-- Establish ArcView link.
End If
 
x = IssueRequests(chan)		'-- Call function IssueRequests

If x <> 1 Then			'-- IF the value sent back by Issue Request
    x = reinitiate(chan)			'	is not 1 then 
End If				'-- Call function reinitiate

x = Terminate(chan)		'-- Call function terminate

End Function			'-- END FUNCTION AVinitiate
Function IssueRequests (chan)
Dim request, answer As String, first As String

request = Forms![Customer Input]![TransferToAV]	'SET request = value from a
form

DDEExecute chan, "AV.run(""ADESK.Locate"",""" & request & """)"	
'-- DDEExecute a ArcView/Avenue Script
'-- The first time this line will execute, but will exit function following
the
'-- DDEExecute, the DDE conversation is still active but Access fails.
'-- This happens the first run on start of Access and if Access sits idle for a
			'-- extended period of time

IssueRequests = 1
'-- If this line is reached the DDEExecute did not bomb out, and the var 	
'-- IssueRequests is set equal to 1

countme = 0
checkone = 1
answer = "nil"

While (answer = "nil")
    answer = DDERequest(chan, "AV.run(""ADESK.Zreturn"","" "")")
'-- This DDERequest looks to an ArcView Script which just returns a global
variable
'-- if the var is still nil the user hasn't completed a screen selection yet
and it
'-- will loop back through and request again

    If Err Then
            MsgBox "Caught in chkreturn error"
            Exit Function
    End If
    
'-- Checks if the loop has been running too long, if so times out
    If countme > 5000 Then	
            MsgBox "Caught in chkreturn timeout error, try again"
            '-- MsgBox "still caught " + answer
            Exit Function
    End If
   
    countme = countme + 1

Wend

'-- This lines update a form in Access with the returned information

Forms![Customer Input]![DealerCustNo] = Left$(answer, 6)
Forms![Customer Input]![DealerFileCode] = Right$(answer, Len(answer) -
InStr(answer, " "))

End Function   '-- END of IssueRequests
Function reinitiate (chan)

x = Terminate(chan)		'-- If failed the first time close connection
MsgBox "Lost initial DDE connection, trying again"
				'-- This Line is IMPORTANT!!  Without a wait in Access
				'-- The reconnection to ArcView will fail
				'-- Seems the Access program and Windows System get
				'-- out of sync.

chan = DDEInitiate("arcview", "System")         '-- Establish ArcView link
again


If Err Then                                     '-- If error occurs, ArcView
    Err = 0                                     '-- isn't running. Reset error
    chan = DDEInitiate("ArcView", "System")     '-- Establish ArcView link.
    If Err Then
        MsgBox "DDE connection error"
        Exit Function
    End If
    
End If

answer = "nil"

'-- Following code is same as in IssueRequests

While (answer = "nil")
    answer = DDERequest(chan, "AV.run(""ADESK.Zreturn"","" "")")

    If Err Then
            MsgBox "Caught in chkreturn error"
            Exit Function
    End If
    
    If countme > 500 Then
            MsgBox "Caught in chkreturn timeout error, try again"
            '-- MsgBox "still caught " + answer
            Exit Function
    End If
    
    countme = countme + 1

Wend

'-- MsgBox answer

Forms![Customer Input]![DealerCustNo] = Left$(answer, 6)
Forms![Customer Input]![DealerFileCode] = Right$(answer, Len(answer) -
InStr(answer, " "))

End Function 		'-- END function reinitiate
Function Terminate (chan)

   DDETerminate chan   '-- Terminate DDE link.
   DDETerminateAll     '-- Terminate all links.
End Function