Run Access code from Excel

[RichardMGreen]

Hi all

I've in middle of automating a database update for reporting.
I need to be able to run code (not a macro but actual VBA) in Access from within Excel.

This is what I have so far in Excel:-

Sub test()
    Dim conn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command, strConn As String

    file = Sheets("General_Info").Range("A5").Value
    Set conn = New ADODB.Connection
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file & ";Persist Security Info=False;"
    conn.ConnectionString = strConn
    conn.Open
    conn.Execute cmdRemove5PMFigures_Click (this is the name of the Access procedure)
    Set cmd = New ADODB.Command
    
    cmd.CommandType = adCmdText
    cmd.ActiveConnection = conn
    
    setcmd = Nothing
    Set conn = Nothing
End Sub

When it gets to the conn.Execute line, I'm getting an error message which says:-
Command text was not set for the command object

Anyone any ideas where I'm going wrong?

 

[Ruddles]

Hopefully this doesn't come across as a sort of "you don't want to do it like that, you want to do it like this" sort of post, but the only way I've ever managed to achieve what you're trying to do is as follows:-

  Dim iChannel As Integer
  Shell "msaccess c:\folder\database1.mdb", vbMinimizedNoFocus
  iChannel = DDEInitiate("MSACCESS", "system")
  Application.ActivateMicrosoftApp xlMicrosoftAccess
  Application.DDEExecute iChannel, "mcrNameOfMacro"
  Application.DDETerminate iChannel

This only works for macros, so you'd have to create a macro which runs the VBA code.

Don't know if this is any help...

 

[RichardMGreen]

All help is gratefully received, so no, it doesn't come across wrong.
Is DDE the only way to do it?
Do I need any sort of references in the Tools menu to run this?

 

[Ruddles]

Just the Microsoft Access Object Library, which you probably have already.

I really don't know if DDE is the only method - I was scrabbling around desperately at the time and I was just grateful I found something which worked!

I've just checked and you need to end your Access macro with a Quit command otherwise Access stays active. The other thing I do is use fAccessWindow to minimise Access as soon as the macro starts otherwise it stays on top whilst it's running.

fAccessWindow is third-party: Google will point you at the code (http://www.google.co.uk/search?q=faccesswindow).

What I'm not 100% sure about is whether you have to wait in some way for the Access macro to end before you can allow your Excel code can continue if you need to use the results of the macro.

 

[RichardMGreen]

OK, thanks.
Just looking at your code, does this execute a macro or a sub-procedure (i.e. VBA code)? I'm after the latter if posible.

 

[Ruddles]

What I'm not 100% sure about is whether you have to wait in some way for the Access macro to end before you can allow your Excel code can continue if you need to use the results of the macro.

No, I checked: the DDEExecute command appears to wait for the Access macro to finish executing before allowing program execution to proceed. So that's good.

Just looking at your code, does this execute a macro or a sub-procedure (i.e. VBA code)? I'm after the latter if posible.

I only ever managed to get it to execute an actual Access macro, not a query or VBA code directly, so I simply created a macro which just did an OpenQuery. You would have to create an Access macro which did a RunCode.

I'll have a play if I get a moment...

 

[RichardMGreen]

Hi Ruddles

I can create the necessary macros in Access to do that.

Thanks for all the help.

 

[Ruddles]

MSDN states that DDEExecute passes "a string expression specifying a command recognized by the server application" (http://msdn.microsoft.com/en-us/library/aa221180(v=office.11).aspx).

The name of an Access macro is evidently "a command recognized by the server application", whereas the name of a VBA procedure or query is not... unless we just don't know how to present those names to Access in an acceptable format...

When you try to pass a function name in the DDEExecute command, Access warns: "If you are trying to call a user-defined VBA function, be sure to use the following syntax: =FunctionName() or =FunctionName(argument1,argument2,...)". I can't get that to work either.

I shall do a little more poking about when I get home tonight, if only to satisfy my own curiosity, but you may have to consider using a macro for the moment.

 

[Ruddles]

I found an article on MSDN entitled "Use Microsoft Access as a DDE Server":-

• 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

Still can't coax a query into running though!

 

[RichardMGreen]

It's fine, I've got a macro to do all the donkey-work now.
Thanks for pointing me in the right direction.