DDE to MS Access: DDEPoke not working (Error 2023)

[Ruddles]

I'm using Access as a DDE server. If I do this, everything works fine:-

Sub Test1()
    
  Dim iChannel1 As Integer
  Dim iChannel2 As Integer
    
  Dim sSQL As String
  Dim vData As Variant

  Shell "MSAccess " & dbPath & "database1.mdb", vbMinimizedNoFocus
    
  iChannel1 = DDEInitiate("MSAccess", "System")
  DDEExecute iChannel1, "[OpenDatabase database1]"
  
[COLOR=red][B]  iChannel2 = DDEInitiate("MSAccess", "database1;SQL SELECT * FROM tblCars;")
[/B][/COLOR][COLOR=black]  vData = DDERequest(iChannel2, "All") 
[/COLOR]  DDETerminate iChannel2
  
  DDEExecute iChannel1, "[CloseDatabase]"
  DDEExecute iChannel1, "[Quit]"
  DDETerminate iChannel1
    
End Sub

vData is an array and I can get the data from it without any problem.

However if I do this:-

Sub Test2()
    
  Dim iChannel1 As Integer
  Dim iChannel2 As Integer
    
  Dim sSQL As String
  Dim vData As Variant

  Shell "MSAccess " & dbPath & "database1.mdb", vbMinimizedNoFocus
    
  iChannel1 = DDEInitiate("MSAccess", "System")
  DDEExecute iChannel1, "[OpenDatabase database1]"
  
[COLOR=red][B]  iChannel2 = DDEInitiate("MSAccess", "database1;SQL")[/B][/COLOR]
[COLOR=red][B]  DDEPoke iChannel2, "SQLText", "SELECT * FROM tblCars;"
[/B][/COLOR][COLOR=black]  vData = DDERequest(iChannel2, "All")
[/COLOR]  DDETerminate iChannel2
  
  DDEExecute iChannel1, "[CloseDatabase]"
  DDEExecute iChannel1, "[Quit]"
  DDETerminate iChannel1
    
End Sub

vData is now type String and contains "Error 2023". The only differences between the two sets of code (apart from the procedure name) are the bits in red.

I've based my code on the code samples on MSDN (2003: http://msdn.microsoft.com/en-us/library/aa172266(v=office.11).aspx; 2007: http://msdn.microsoft.com/en-us/library/bb242443(v=office.12).aspx). All the other code I've copied off these pages has worked first time except for the sample which uses DDEPoke. I've treble-checked it and I can't see that I've made any typos in adapting it to suit my database.

I've tried the DDEInitiate with and without a trailing space at the end of "database1;SQL" (just in case). I've tried DDERequest with "All", "Data", "FieldNames", "FieldCount" and "FirstRow": all attempts result in an Error 2023.

I've tried stepping through the code to check it's not a sequencing or timing problem but everything seems to happen and complete in the correct order.

Googling the error on the basis that it's an Access error code suggests the problem is: "The file name you specified for the data access page already exists". However people have also reported this error code using Excel as the DDE server.

Has anyone successfully sent SQL to Access using DDEPoke?

Even if you haven't, I'm prepared to listen to (almost) any suggestions!

 

[Ruddles]

I'll just bump this once - in case someone with the solution missed it the first time.

 

[Ruddles]

Okay I lied: I'm bumping this once more - but it'll be the last time, I promise.

 

[xenou]

I'm no help with DDE but I'm curious - why not just use ADO or DAO?
ξ

 

[Ruddles]

I wanted to add DDE to my armoury. All the other DDE methods I copied from the MSDN worked so readily, I couldn't believe this one refused to. Now I just want to beat it into submission.

If nothing else, it can be used to run a query which is already in the database in Access itself rather than building and running it in Excel, or even a macro which runs a sequence of queries.

As I said, I just wanted to satisfy myself that it could be done.

 

[xenou]

Okay, makes sense. I suspected that was your motivation! I've never touched (poked?) DDE I'm afraid.

 

[xenou]

Well, help me out on this one. According to the link in your first post:

the client can use the DDEPoke statement to send data to the server application.

So it sounds to me like a Poke would be used not to get data from Access but to send data to Access (though how or what that means I don't know). I assume the Access DB "listening" would have to use or process the data sent ... ?

Note by the way that in regard to your reason for using DDE:
If nothing else, it can be used to run a query which is already in the database in Access itself rather than building and running it in Excel, or even a macro which runs a sequence of queries. This can be done with DAO/ADO (run stored queries) or a few lines of VBA or VBS scripting (run macros in the database). Though I can't argue with being interested to see how it works with DDE.

 

[Ruddles]

So it sounds to me like a Poke would be used not to get data from Access but to send data to Access (though how or what that means I don't know). I assume the Access DB "listening" would have to use or process the data sent ... ?

Yes, a program or process which is capable of acting as a DDE server is listening on the DDE channel for commands it recognises. (At the moment I'm aware Access, Excel and Word are all DDE-compliant.) When it sees something coming in, it checks that it's a valid command or 'item' and tries to fulfill the request.

The result of fulfilling the request may be to send some data back on the DDE channel. For example, if you send it 'TableList", it sends back a list of tables, and when you have that you can send 'FieldNames' for a table and it send back the field names in that table.

The MSDN link explains everything - almost.

Note by the way that in regard to your reason for using DDE:
If nothing else, it can be used to run a query which is already in the database in Access itself rather than building and running it in Excel, or even a macro which runs a sequence of queries. This can be done with DAO/ADO (run stored queries) or a few lines of VBA or VBS scripting (run macros in the database).

I don't think I knew that, or if I did I'd forgotten it. I've certainly done this in the past: extracted the SQL string from a stored query using its .SQL property then sent it back using DoCmd.RunSQL. Not very elegant!

Though I can't argue with being interested to see how it works with DDE.

Indeed, I just found it frustrating that everything else on that page worked so smoothly and yet this one function refused steadfastly to work and there didn't seem to be anything on the Web which helped.

I get the impression that DDE is very much a 'last resort' sort of tool. Certainly MS Office products can manage without it when talking to each other, but I originally took an interest when someone asked a question about getting Excel to talk to a non-MS product called SolidWorks.

When I Googled the name the results indicated that it might be DDE-compliant so I suggested this to the person who'd posed the question and then quickly brushed up on the topic in case there were any follow-up questions.

I don't think there were though.

 

[xenou]

I have nothing very informed to say about DDE (I think you are be right that it still has its uses but it's not a preferred technology now).

On the topic of running SQL commands or macros from VBA in Excel some notes follow (I apologize you already know some of this but I'll give a short recap for the benefit of anyone following with interest):

Strangely (although Access doesn't call them such) you can run saved queries in Access with ADO using the adCmdStoredProc CommandType option - passing a query name rather than the sql text. If there are parameters these can be created and passed (I believe you must invoke an ADO Command object rather than an ADO Recordset object in these cases). You can even use ADO to create stored procedures in Access - I believe these will be invisible to the UI ("very hidden" queries, as it were).

I've used ADO Command objects frequently to run delete, updates, or pass parameters to Access queries. I don't usually bother with ADO "stored procedures" and Access (as such). A command object returns a Recordset (Set rs = MyCommandObject.Execute), but if you don't have records being returned then you just ignore that (MyCommandObject.Execute) - for instance, to run Access "Action Queries".

As far as running macros in Access, a favorite trick of mine is to open the DB (invisibly) and use Application.Run to run whatever macro I want - usually a "Main" sub that takes care of the rest. I do this with VBS scripts and scheduled tasks quite a lot - the vbs script opens the database and runs the "Main" sub. The "Main" sub in the database handles all the rest. It is true that you have to "open" the database to run these queries, but I would assume that even with DDE the database also must be "open" in some way to be listening or running its macros. It's usually very clean but I make sure to handle errors, not display popups, and close what I open - so I can run such routines in the middle of the night if I like. I've set macro security to low so I never have problems with the macro security question about "this database contains potentially unsafe macros blah blah blah".

VBS Script (save as RunMe.vbs and let er rip - the necessary changes being made - would work from Excel or Access VBA too of course):

[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Call[/COLOR] Do_Imports
 
[COLOR="Navy"]Sub[/COLOR] Do_Imports()

    Write_Log (vbNewLine & CStr(Now()) & vbNewLine & "Starting scheduled task Do_Imports ...")
    ret = 1
    [COLOR="Navy"]Set[/COLOR] objAccess = CreateObject("Access.Application")
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] objAccess [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]With[/COLOR] objAccess
            .OpenCurrentDatabase "\\server1\DatabaseImports.mdb", False
            ret = .Run("Scheduled_Imports")
            [COLOR="Navy"]If[/COLOR] ret = 0 [COLOR="Navy"]Then[/COLOR]
                msgText = msgText & "The operation succeeded."
            [COLOR="Navy"]Else[/COLOR]
                msgText = msgText & "Error: not all procedures executed successfully."
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    [COLOR="Navy"]Else[/COLOR]
        msgText = msgText & "Error: Access failed to opened."
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    Write_Log (msgText)

    [COLOR="SeaGreen"]'-----------------------------------------------------------------------[/COLOR]
    [COLOR="SeaGreen"]'Exit code - close Access[/COLOR]
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] objAccess [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]With[/COLOR] objAccess
            .CloseCurrentDatabase
            .Quit
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] Write_Log(ByRef arg)

    fPath = "C:\myTemp\Scheduled\log.txt"
    [COLOR="Navy"]Set[/COLOR] objFSO = CreateObject("Scripting.FileSystemObject")
    [COLOR="Navy"]If[/COLOR] objFSO.FileExists(fPath) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] f = objFSO.GetFile(fPath)
        [COLOR="Navy"]If[/COLOR] CLng(f.Size) > 8000000 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR] [COLOR="SeaGreen"]'//Runaway error log[/COLOR]
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] ts = objFSO.OpenTextFile(fPath, 8, True, -2)
            ts.WriteLine arg
            ts.Close
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Set[/COLOR] ts = [COLOR="Navy"]Nothing[/COLOR]
    [COLOR="Navy"]Set[/COLOR] objFSO = [COLOR="Navy"]Nothing[/COLOR]
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

HTH,
ξ

 

[xenou]

Another note on using query names with Access (rather than raw SQL) - you *should* also be able to use named queries with DAO quite easily. I.e.:

Set rs = CurrentDB.OpenRecordset "MySelectQuery" '//Get a recordset
Or
CurrentDB.Execute "MyActionQuery" '//Run an action query
Or
DoCmd.OpenQuery "MyActionQuery" '//Run an action query

Using currentDB.Execute is handy because you don't need to worry about the turning off displayalerts as you do with DoCmd.RunSQL. Also, you can trap errors using the optional options argument. On the other hand, I find that setting displayalerts to false and using OpenQuery or RunSQL with a DoCmd object allows me to run queries that I know will include some duplicates on a unique key - it will discard the dupes but append the rest. It seems that CurrentDB.Execute rolls back the entire transaction - though I should think there must be some way around that.

Hope this helps - I know most or all of it is familiar to you - frankly, I'm a big fan of DAO and ADO alike so I guess I'm all too willing to trumpet some of their virtues (at the expense of 'ole DDE I guess).