DDE function works in A2003 but not in A2007

[Greg Alessandra]

I am having trouble converting a database that uses the DDE function from
A2003 to A2007. After much frustration, I eliminated as many variables as I
could by creating a new database with no tables and just one form. The form
has only one text box control. The controlsource is
=DDE("WINROS","LAST","KSU")
In A2003, this returns 24.23 which is the LAST price for the stock symbol
KSU from Esignal's quote application WINROS.EXE which is running on my
machine. In A2007, all I get is #Name?. If I switch back and forth between
A2003 and A2007, leaving everything else alone, A2003 responds as expected
with a price from the winros app, but A2007 only returns #Name?.
To test further, I opened an Excel spreadsheet and put the number 12345 in
R1C1. I changed the controlsource of the text box in my access form to
=DDE("excel". "sheet1", "r1c1"). In A2003 the control shows "12345" but not
in A2007. All A2007 says is #Name?
The online help suggests that the DDE function is still available in A2007.
I have added the directory where this database resides to the Trusted
Locations area. I am really at wits end on this one. Any ideas would be
tremendously helpful. Could the source of my problem be a security setting,
an inherent flaw with the free trial version of A2007, or a missing
reference? Thanks in advance for any ideas.

 

[Tom van Stiphout]

On Mon, 2 Nov 2009 00:37:01 -0800, Greg Alessandra <Greg
(e-mail address removed)> wrote:

I suspect you forgot to copy your custom DDE function from the
previous app to this one. I wrote this code in A2007 and it works with
your Excel example:
Public Function myDDE()
Dim h As Long
h = DDEInitiate("excel", "Sheet1")
Debug.Assert h
Dim s As String
s = DDERequest(h, "r1c1")
DDETerminate h
myDDE = s
End Function
Then set the ControlSource to:
=myDDE()

-Tom.
Microsoft Access MVP

 

[Greg Alessandra]

I used your approach and it solved the problem. Thank you very much.
I still have the impression that the function DDE doesn't work as advertised
in A2007. http://office.microsoft.com/en-us/access/HA012288191033.aspx
In any case, you have been very helpful. Thank you.