Run Append query from Excel

[maw230]

I have a macro in Excel that refreshes several of the queries in my DB. i need to add to it a command that will refresh an Append query in the same DB. i know you cant do this through the ODBC link. is there another way?

 

[SOS]

So these are Append, Delete or Update queries we're talking about here that need "refreshing?"

 

[maw230]

I am just seeing if there is a way to run an Append query in Access from a macro in Excel.
I need the records to be stored in an Access table before i can run the rest of my macro.

 

[SOS]

Yes, you can.

Function RunAppendQuery(strDatabaseName As String, strAppendQueryName As String)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
         Set db = OpenDatabase(strDatabaseName)
         Set qdf = db.QueryDefs(strAppendQueryName)
 
            qdf.Execute
 
            qdf.Close
 
    db.Close
    Set qdf = Nothing
    Set db = Nothing
End Function

And you pass the name of the database and the query to it like:

Call RunAppendQuery("H:\Test2.mdb","qryAppendTest")

 

[SOS]

Oh, and you'll have to use this in a MODULE (don't name it the same as the function) in your Excel File and you will need to set a reference to Microsoft DAO 3.x (where x is a number like 51 or 6).

 

[maw230]

ok. i put the function in a module.

do i need to enter the names of the db and query here: "(strDatabaseName As String, strAppendQueryName As String)"

or is that not necessary since i use the names in the Call command?

sorry my VBA is very lacking; how might i set a reference to DAO. furthermore, what is DAO?

 

[SOS]

ok. i put the function in a module.

do i need to enter the names of the db and query here: "(strDatabaseName As String, strAppendQueryName As String)"

or is that not necessary since i use the names in the Call command?

sorry my VBA is very lacking; how might i set a reference to DAO. furthermore, what is DAO?

Okay, this part of the function:

"(strDatabaseName As String, strAppendQueryName As String)"

is called the Input Parameters. So this is what you use when you want code to be "reusable" (in other words you don't have to specify at the time you design it what objects it will work on). You PASS these parameters at runtime when you CALL the function.

So you do NOT change that at all. You pass the database path and name and the query name at the place you are CALLING it.

As for setting a reference to DAO you go into the VBA Window and then up to TOOLS > REFERENCES and then scroll down the list until you see

MICROSOFT DAO 3.x Object Library (and if you have 3.6 listed pick that, if not pick the 3.51) then close the dialog and you are set.

 

[maw230]

great, SOS. thanks for the quick lesson. that alone should help me to understand VBA a little more.

 

[SOS]

If you really want to know about VBA, check out this great set of FREE tutorials from FunctionX.

Access 2007 VBA

Access 2003 VBA

 

[maw230]

ok, i got everything working great. but i have one more small issue.
i would like to be able to run the Call to the append query, and then only run the rest of the module when the append query in access is finished. any ideas? thanks again, hopefully you are still subscribed to this thread...

 

[SOS]

Do a search here for Shell and Wait.

 

[sivani_007]

hi there, i used this function but i get this error

Microsoft visual basic for application

with cross and white background and it only has two button OK and HELP, doesn't matter which one i select it doesn't do anything ..


how can i resolve this issue?

thanks