Re: Append & Make Tables

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

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
http://I.Am/DougSteele
(no private e-mails, please)

.

Quantcast