Direct Data Exchange (DDE) Call Access Macro

[Rudolfmdlt]

Hi All,

I need to pass a string from one application to another and execute based on that string. I just cannot get this to work in Access and am finally at my wits end!
I have the following code in Excel:


Sub DDETest()
Dim chanNum As Variant
chanNum = DDEInitiate("WinWord", "System")
DDEExecute chanNum, "[DDETesting.Test(5)]"
DDEExecute chanNum, "[DDETesting.Test2(""John"",""Doe"", 12)]"
DDETerminate chanNum
End Sub


And the following in Word in a module called DDETesting


Sub Test(iNum As Integer)
MsgBox "The value is: " & iNum
End Sub


Sub Test2(strFname As String, strLname As String, iNum As Integer)
MsgBox "The values are: " & strFname & "," & strLname & "," & iNum
End Sub






And it works. :)

Now, I have been busy for the last 2 days trying to port the Word side code to Access. :banghead:

In Excel,



Sub DDETest() Dim chanNum As Variant
chanNum = DDEInitiate("MSACCESS", "System")
DDEExecute chanNum, "[DDETesting.Test(5)]"
DDEExecute chanNum, "[DDETesting.Test2(""John"",""Doe"", 12)]"
DDETerminate chanNum
End Sub




In Access, in a module called DDETesting as well:


Option Compare Database

Sub Test(iNum As Integer)
MsgBox "The value is: " & iNum
End Sub


Sub Test2(strFname As String, strLname As String, iNum As Integer)
MsgBox "The values are: " & strFname & "," & strLname & "," & iNum
End Sub


When I execute the Excel code while pointing the DDE at Access, Access pops up with the error "Microsoft Access cannot find the object 'DDETesting.' "

I have tried almost a hundred permutations of DDETesting.Test(5), Test(5), Database1.DDETesting.Test(5) ect but I cannot get Access to execute my code.

The only time I can get Access to do anything is when I create Point-and-Click macro using the GUI editor. These Macros Access sees, but anything I write by hand seems to be invisible!?

I would really appreciate any help!

Thanks for your time,

Regards,

Rudolf

PS: I'm actually trying to do this using C++ and access, but after 2 days of failures I tried using just Office application to check whether it wasn't an environment issue, and exactly the same issue.
I'm using this HowTo for the Excel and Word testing: support.microsoft.com/kb/274284

[Rudolfmdlt]

Anybody? :)

It's a really simple and silly problem - I honestly think I'm missing something basic.

Regards,

Rudolf

[jonh]

http://msdn.microsoft.com/en-us/library/office/aa172266(v=office.11).aspx

I've got to say I have no idea why anybody would ever want to use this approach for anything but after a quick skim read of the above I came up with this.

Since you can only run code through a macro it needs to be a public function (but you can call subs from that function) e.g.

(module1 in dde_test.mdb)


Public Function DDEMain()
SubTest
End Function

Private Sub SubTest()
MsgBox "hello"
End Sub


So you would create a macro, select the runcode option and set the function name to your main function, i.e. DDEMain()

Now in the app you you want to run the code from you use...


Dim chanNum As Variant
chanNum = DDEInitiate("MSACCESS", "dde_test.mdb")
DDEExecute chanNum, "[Macro1]"
DDETerminate chanNum

HTH

[Rudolfmdlt]

Hi Jonh,

:) Ha ha, I tried very hard to convince the client not to stick to his old tools. anyway.

I need to pass a string to MS Access and then execute a function in Access based on that string. Do you have any other suggestions other than DDE?

Anyway, you can call a function or a macro in MS Word and Excel AND pass in a parameter using DDE, but with MS Access you cannot pass in a parameter as well. Does this limitation make sense or serve any purpose?

Regards,

Rudolf

[jonh]

It does seem odd that it works so differently between applications. I've no idea why it is so.

Well it's hard to suggest anything really because all you've told us is that you want to use some really weird method of automating Access.

If you still want to do it that way you could write to a temp table or log file rather than passing the string direct.

If you don't have to use C++, you could link the database containing your code into another Access db as an add-in. This seems to be the closest to what you're asking for.

Depends what your code does really. If it's just database manipulation stuff that you don't want to duplicate between applications, write a dll and reference it.

[HiTechCoach]

See:
Use Microsoft Access as a DDE Server (http://msdn.microsoft.com/en-us/library/office/ff821067.aspx)