Append & Make Tables

[Guest]

Hi, Please Help...

I have a form with a comand button on to which I want to be able to click on
this and it automatically run append queries and make table queries. There
are multiple append queries and multiple make tables. How can I do this??

Thanks,
Jez

 

[Arvin Meyer [MVP]]

The easiest way would be something like (aircode):

Sub cmdButton_Click()
DoCmd.SetWarnings False

DoCmd.OpenQuery "qryFirstMakeTable"
DoCmd.OpenQuery "qrySecondMakeTable"
DoCmd.OpenQuery "qryFirstAppend"
DoCmd.OpenQuery "qrySecondAppend"

DoCmd.SetWarnings True
End Sub

 

[Guest]

To add to Arvin's answer just a little bit, if you are going to use
DoCmd.SetWarnings in code, then I recommend *always* including error handling
code so that warnings will be restored in the event that the code terminates
prematurely (ie. one of the queries fails, so you never get to the line of
code at the end that restores warnings). Something like this:

Option Compare Database
Option Explicit

Sub cmdButton_Click()
On Error GoTo ProcError

DoCmd.SetWarnings False

DoCmd.OpenQuery "qryFirstMakeTable"
DoCmd.OpenQuery "qrySecondMakeTable"
DoCmd.OpenQuery "qryFirstAppend"
DoCmd.OpenQuery "qrySecondAppend"

ExitProc:
DoCmd.SetWarnings True
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdButton_Click..."
Resume ExitProc
End Sub


You can also list the various queries by name in a table, and then use code
to run them in the order desired. Here is a post that I made earlier this
week that demonstrates this technique:

http://www.microsoft.com/office/com...43879f-f192-4d7a-9d07-4666315b3a5b&sloc=en-us


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Douglas J. Steele]

I hate to argue with my peers, but I wouldn't use OpenQuery. In fact, I'm
not sure OpenQuery is appropriate in this case.

I'd recommend using the Execute method of the QueryDef object. This not only
ensures no pop-up messages will occur, but will generate a trappable error
if something goes wrong with the query.

Option Compare Database
Option Explicit

Sub cmdButton_Click()
On Error GoTo ProcError

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("qryFirstMakeTable")
qdfCurr.Execute dbFailOnError
Set qdfCurr = Nothing
Set qdfCurr = dbCurr.QueryDefs("qrySecondMakeTable")
qdfCurr.Execute dbFailOnError
Set qdfCurr = Nothing
Set qdfCurr = dbCurr.QueryDefs("qryFirstAppend")
qdfCurr.Execute dbFailOnError
Set qdfCurr = Nothing
Set qdfCurr = dbCurr.QueryDefs("qrySecondAppend")
qdfCurr.Execute dbFailOnError
Set qdfCurr = Nothing

ExitProc:
Set dbCurr = Nothing
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdButton_Click..."
Resume ExitProc
End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Arvin Meyer [MVP]]

I agree with Doug's assessment. I stated that it's the easiest way, not the
best. I assessed Jez's experience with code as being a beginner, therefore I
wrote code that would work and be easy to understand, as well as easy to
implement.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Hi Doug,

I agree with you, so there is no argument here. I was simply showing how to
add error handling to the procedure that Arvin offered, in order to guarantee
that warnings are always turned back on. I wanted to maintain the procedure
that Arvin offered as much as possible, so that the OP could see how to build
on the original to include the error handler.

If you look at the link I provided for the post that I made last Monday, you
will see that I am using .execute method for the action queries (ie. Case 32,
48, 80).

Are you sure that you really need the Set qdfCurr statements? Why not just
use something like this instead?

Option Compare Database
Option Explicit

Sub cmdButton_Click()
On Error GoTo ProcError

Dim dbCurr As DAO.Database
Set dbCurr = CurrentDb()

dbCurr.Execute "qryFirstMakeTable", dbFailOnError
dbCurr.Execute "qrySecondMakeTable", dbFailOnError
dbCurr.Execute "qryFirstAppend", dbFailOnError
dbCurr.Execute "qrySecondAppend", dbFailOnError

ExitProc:
Set dbCurr = Nothing
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdButton_Click..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Douglas J. Steele]

Because I was too lazy to look up whether you could used a named query as
the source for the Execute method of the Database object.

Yes, your way would be more efficient.