English

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

Solution or Workaround

Why doesn't Access, as a DDE client, work on the initial DDERequest or DDEClient statement?

Cause:

The is due to Access closing the channel prematurely and then looking for additional information to be posted at that location.

Answer:

Microsoft Access is a DDE client in most application. Other reasons are peculiar to Microsoft Access, like it cannot have it's data change via a DDEPoke or similar from another application, DDEPoke to Access can ONLY send SQL query information to Access, when using Access as a DDE client the initial DDERequest or DDEExecute statement will not work--this is only on the first run of Access after start, if you duplicate the process the second time it will work (if coded correctly).

This information can be found in ArcView versions 3.1 and 3.2 online help: Help Menu, Help Topics, Find Tab, and type: Samples of DDE connectivity with MS Access.

ArcView Sample Code
--------------------
SAMPLE CODE IS INCLUDED WITH VERSIONS 3.1 AND 3.2 IN TEXT FILE FORMAT
(ddewhite.txt)
This sample is included with the software and is found in the following path:
esri\av_gis30\arcview\samples\other\ddewhite.txt

EXAMPLE CODE FOR ACCESS:

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

Notes:

For more information on DDE and Avenue please see the on-line Avenue help for DDE.