Run "make table" query from Excel

[scotty2000]

This one is starting to get me down!

I've got a background Access database with an Excel front end report.

I run a "make table" query in my Access database to make sure I have the most up to date information from our data warehouse.

I then run a couple of other "select" queries from that table that are linked to my excel front end.

When I refresh the data in Excel, it runs my select queries with no problem, however I need to run the "make table" query first.
I know I could change the "make table" to a "select" query but it means that each time I run my other select queries it has to get the data again from the data warehouse which takes ages.

I'd like to do this without having to open the database and have tried all sorts.

I'm using Excel 2003 and Access 2000

Any ideas?

 

[xenou]

Query tables and the like are oriented to retrieving data. In any case, I've never tried to run an action query with MSQuery or anything like that.

However, you can do this somewhat easily with VBA. If push came to shove, you could also just have a handy link to the database so you can open it up and shoot off the make table query when you need it.

Here's an example of running your macro in the access database:

Sub RunSubFoo()
Dim AC As Object
    
    Set AC = CreateObject("Access.Application")
    With AC
        .OpenCurrentDatabase ("C:\myTemp\db7.mdb")
        .Run "Foo"
        .Quit
    End With
    

End Sub

 

[xenou]

Okay, sorry. That last was to run a sub. I'm not sure why I had that stuck in my head that you had a macro in Access.

Here's a similar version to run a query:

Sub RunQuery1()
Dim AC As Access.Application
    
    Set AC = CreateObject("Access.Application")
    With AC
        .OpenCurrentDatabase ("C:\myTemp\db7.mdb")
        .CurrentDb.Execute "Query1"
        .Quit
    End With
    

End Sub

 

[Norie]

How are you currently running the code and getting the data into Excel?

 

[scotty2000]

Thanks xenou,

I've copied and pasted your code into my Excel Macro but it I get a "Compile error: User-defined type not identified" when it reaches this line: "Set AC = CreateObject("Access.Application")"


Norie,
I'm using "Get External Data" to move the contents of my select queries into Excel. The select queries run from a table in Access which is created using a "make table" query, that needs to run first to ensure it has the current week's sales information in it.

 

[Norie]

You could do this without opening the database using either ADO or DAO code.

Don't use DAO much so here's an ADO example.

Option Explicit
 
Sub RunAccessActionQuery()
Dim cnn As Object ' ADODB.Connection
Dim rst As Object ' ADODB.Recordset
Dim strConn As String
Dim strTargetDB As String
Dim strQry As String
Dim lngRecs As Long
 
    strTargetDB = "TestPad.accdb"
 
    strQry = "MakeNewPricesTable"
 
    Set cnn = CreateObject("ADODB.Connection")
    
    strConn = "C:\AccessStuff" & Application.PathSeparator & strTargetDB
 
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open strConn
    End With
 
    Set rst = CreateObject("ADODB.Recordset") ' New ADODB.Recordset
 
    Set rst = cnn.Execute(strQry, lngRecs)
    
    Debug.Print lngRecs
 
    cnn.Close
    
    Set cnn = Nothing
        
End Sub

This worked for me with a simple make table query, and also an update query.

Don't know if there would be any problems caused by your query being linked to Oracle, but if it runs in Access OK then I don't see why this code won't work.

The code uses late-binding so doesn't need any references.(Tools>References...)

I've included the declarations you would use if it was early bound, they would need a reference to Microsoft ActiveX Data Objects 6.0 Library.

Post back if there are any problems.

 

[xenou]

Sorry ... should have been:

Sub RunQuery1()
Dim AC As [COLOR="Red"]Object[/COLOR]
    
    Set AC = CreateObject("Access.Application")
    With AC
        .OpenCurrentDatabase ("C:\myTemp\db7.mdb")
        .CurrentDb.Execute "Query1"
        .Quit
    End With
    

End Sub

Norie's ADO solution is also a good way to go.

 

[scotty2000]

That's brilliant xenou!
Works like a dream!

except....

if the table is already there and needs to be deleted, I get a run time error message.

I've tried deleting and then running the query in a macro, and then amending the line in code to say

.CurrentDb.Execute "Macro Name"

This doesn't seem to work. Does it need to say something other than "Execute" to run a macro?

 

[xenou]

Okay. Sorry, I hadn't really tested it with a make-table query, just a select query.
This looks like it works now. I've also added appropriate error-handling so we can be sure the database we open is closed:

[COLOR="Navy"]Sub[/COLOR] RunQuery1()
[COLOR="Navy"]Dim[/COLOR] AC [COLOR="Navy"]As[/COLOR] Object    

    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler:
    [COLOR="Navy"]Set[/COLOR] AC = CreateObject("Access.Application")
    [COLOR="Navy"]With[/COLOR] AC
        .OpenCurrentDatabase ("C:\myTemp\db8.mdb")
        .DoCmd.SetWarnings False
        .DoCmd.OpenQuery "Query1"
        .DoCmd.SetWarnings True
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    
My_Exit:
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] AC [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    AC.CloseCurrentDatabase
    AC.Quit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

ErrHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
[COLOR="Navy"]Resume[/COLOR] My_Exit:
    

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

Nota Bene:
In my experience if the Access macro security is set to "low" it runs invisibly - otherwise, you see a message pop up about enabling macros when you try to open the database. In Access 2007/2010 I would presume you can put the DB in a trusted location so that wouldn't matter.

 

[scotty2000]

Sorted!

Thanks a bunch!