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]

For interest, "The DoCmd object doesn't support methods corresponding to the following actions... RunCode. Run the function directly in Visual Basic." (http://msdn.microsoft.com/en-us/library/aa223114(v=office.11).aspx)

How you do that I don't know!

To run a query:-

Option Explicit
Sub RunAccessQuery()
  Dim iChannel As Integer
 
  Shell "MSAccess " & "c:\folder\database1.mdb", vbMinimizedNoFocus
  
  iChannel = DDEInitiate("MSAccess", "System")
  
  Application.DDEExecute iChannel, "[OpenDatabase " & dbPath & "scratch_database1.mdb]"
  Application.DDEExecute iChannel, "[SetWarnings 0]"
  Application.DDEExecute iChannel, "[OpenQuery qmakMyNewTable]"
  Application.DDEExecute iChannel, "[CloseDatabase]"
  Application.DDEExecute iChannel, "[Quit]"
  
  Application.DDETerminate iChannel
 
End Sub

 

[RichardMGreen]

Hi Ruddles, I've come across a small flaw with this code and can't seem to sort it.

Sub Process_Data()
    Dim db As Object
    file = Sheets("General_Info").Range("A5").Value

    Set db = CreateObject("Access.Application")
    db.Visible = True
    db.opencurrentdatabase (file)
    db.docmd.runmacro "mcr_Process_Data"
    db.Quit
    Set db = Nothing
End Sub

It's not releasing the database lock (the ldb file) when it's finished. It only releases when I close the spreadsheet.
Any ideas?

 

[Ruddles]

I've run your code and the LDB gets deleted okay when the db.Quit command executes.

I suppose you could try a db.CloseCurrentDatabase before the db.Quit.

 

[RichardMGreen]

Thanks for that, I'll give it a go.