Exact same Code works in Excel VBA but not Access VBA: Why is this?

[fossx]

I am running Office 2003 which has VBA Retail 6.4.8869 running for both Excel and Access. The following code works just fine in Excel but gives a "Compile Error: Can't assign to array" error in Access. It is a DDE request that returns an array.

Function doRequest(serverName, topic, request) As Variant()
Dim chan As Integer
chan = Application.DDEInitiate(serverName, topic)
doRequest = Application.DDERequest(chan, request)
Application.DDETerminate chan
End Function

In Excel, I get an array returned. In access I get the compile error associated with the DDERequest line.

If I change the code to the following, I get two different behaviors out of Excel and Access (note the change is that I just specify a pure variant vs. a dynamic variant array for doRequest).

Function doRequest(serverName, topic, request) As Variant
Dim chan As Integer
chan = Application.DDEInitiate(serverName, topic)
doRequest = Application.DDERequest(chan, request)
Application.DDETerminate chan
End Function

If I make this change, I don't get the compile error in Access. However, Excel still returns a full array. However, Access only returns a string of the values.

It is the same VBA on the same computer. Any ideas why this is happening?

 

[The_Doc_Man]

Check your references.

Also, Access and Excel are different in concept. Access is NOT array oriented. It is RECORD-oriented. Excel is (essentially) too dumb to know better.

 

[fossx]

Solution

See http://www.tek-tips.com/viewthread.cfm?qid=1369158

The issue is that the DDERequest Objects are different in Access (returns string Variant) and Excel (returns Variant Array).

Use Excel.Application.DDERequest in Access to get an array is the bottom line.

 

[The_Doc_Man]

Yup, like I said. If you use in Access, get different than in Excel because the two are different and the routines are designed to return data to different programs, each to it's own kind.