DDEInitiate

[mtompkins]

OK - I know this is harder stuff but...

I have 3 lines of code that work in Excel

 channelNumber = Application.DDEInitiate("CQGPC", Topic)
 returnList = Application.DDERequest(channelNumber, Item)
 Application.DDETerminate channelNumber

but fails in Access

It connects to a program "CQGPC" and returns an array of values.

In Excel the channelNumber returns a value that is correct (a small integer)

In Access (2003) it returns a large long number.

Thoughts anyone??

I would really like this to connect in Access so I don't have to code the Excel linkage.

 

[RoyVidar]

According to this article DDEInitiate Function (same as the help file), it should return a long representing the unique channel number.

Roy-Vidar

 

[mtompkins]

Hi Roy - I have read this article.

The interesting result is as follows -

With excel it returns a channel number in the range of 1 to 5 - then the DDE connects and returns an array.

With Access, it returns a channel number in the range of 10000000 - 20000000 and returns no data.

Any thoughts?

 

[Miquella]

I have gotten DDE to work in access... I just can't find my database right now, I think I have a copy at work, give me a few hours and I'll post my code.

It initiates a DDE connection to Procomm Plus (Telnet) and queries for information from one of our servers.

-Bean

 

[mtompkins]

FBean -

That would be awesome - if you could also tell me what can of channel number is returned during the connnection that would be HUGE!

Thanks much.

mark

 

[Miquella]

Alright, I found the code sooner than I though I would... Be fore warned, some of this code may look a little strange, I had to have very specific control of the program I was connecting to...

Dim ProcommN As String
Dim DDEChan As Long

Private Function StartDDE()
    'Check if connection has already been established
    If ProcommN <> "" Then
        StartDDE = 1
        Exit Function
    End If

    'Program to connect to
    ProcommN = "PW5"
    
    'Allow errors to be handled
    On Error Resume Next
    Err.Clear
    
    'Attempt a connection
    DDEChan = DDEInitiate(ProcommN, "system")
    'If a connection is successful, prompt the user for
    'permission to close the program (need exclusive control)
    If Err.Number = 0 Then
        If MsgBox("All Procomm Plus windows must be closed to continue," + Chr(13) + "do you wish to continue?", vbYesNo, "Procomm Interface") = vbNo Then
            StartDDE = 2
            On Error GoTo 0
            Exit Function
        End If
        'Tell the program to HALT all scripts and CLOSE
        'continue until a new DDE connection cannot be made
        While DDEChan
            DDEExecute DDEChan, "HALT"
            DDEExecute DDEChan, "PWEXIT"
            DDETerminate DDEChan
            DDEChan = 0
            DDEChan = DDEInitiate(ProcommN, "system")
        Wend
    End If
    
    'Clear errors, so we don't get confused
    Err.Clear

    'Initiate our own copy of the program, running our
    'interactive server script, so we can pass/get values
    PWPI = Shell("C:\program files\procomm plus\programs\pw5.exe C:\Access.wax", vbMinimizedNoFocus)
    If Err.Number Then
        StartDDE = Err.Number
        On Error GoTo 0
        Exit Function
    End If

    'Wait for the DDE connection to be established
    Do While 1
        DoEvents
        DDEChan = DDEInitiate(ProcommN, "system")
        If DDEChan Then Exit Do
    Loop
    
    'Reset the error function
    On Error GoTo 0
    StartDDE = 0

End Function

And since my server is set up to recieve data as well as return it, I use the following:

    'Send the username:
    DDEPoke DDEChan, "s1", sUser
    'Wait for response from the server:
    Do: DoEvents: Loop While DDERequest(DDEChan, "s0") <> ""

And when I'm done I just:

    DDETerminate DDEChan

I changed very little of my code, to protect my company's interests, but nothing that will affect useability of the code. And I put comments throughout all of it, if you have any questions on how it works, please ask, I'd be more than willing to help.

I really hope this helps! I remember how long it took me to get this much working (which can still use a little work, but I have a bigger project I'm working on for now)

-Bean

 

[mtompkins]

Bean -

Thanks again for yours.

From what I see it has the same structure as yours for the DDE connect. I've opted to process the request thru Excel to get it done - but I hate these types of shortcuts.

As I indicated, I am using the same structure in Excel and it is working fine.

Is your module currently working? (meaning can you check the channel number assigned with your connection?) so I have a benchmark on what to expect as a channel number?

Then I can nail down the issue to be the return information (which is an array) which might be complicating matters.

Thanks.
Mark

 

[Miquella]

Mark-
It is working, but I will have to run it at work, I don't have Procomm here at home, I'll post a couple channel numbers when I get to work, in about 3-4 hours.

-Bean (Mark, lol, that's funny)

 

[Miquella]

Mark,
I made it to work, and ran through the code, I got two different channels:
8952344
8953608
Looks like it is a Long! I don't know why Excel's is different, I haven't put DDE Code through Excel, but I will be in a few months, so hopefully I can figure it out then! (Assuming that what you're saying is that it's different)
Good luck to you!
-Mark (Bean)

 

[mtompkins]

Thats a huge help - thanks.

It must be pulling the data in to an array - which is the return that I get. Perhaps it can only return a single result.

Kindest,
Mark

 

[mtompkins]

OK - then out for anyone who would care to comment ---

The DDE connection returns an array (5 X 300) of string data.

Any thoughts on how to properly parse this into Access?

Kindest,
Mark