Can you run multiple Append Queries Within One Command Button?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to run several (32) Append Queries to build one table. How would I do
this with Visual Basic and one "Command Button'? I know how to do it once:

Private Sub Append_Button_Click()
On Error GoTo Err_Append_Button_Click

Dim stDocName As String

stDocName = "Append Query-1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Append_Button_Click:
Exit Sub

Err_Append_Button_Click:
MsgBox Err.Description
Resume Exit_Append_Button_Click

End Sub

but how do I add the 31 other append queries I have, so with one touch of
the button, it executes all of them?
 
Well, If you name your queries correctly, then it would be pretty simple. If
your first query is Append Query-1 and the next one is Append Query-2 on
through Append Query-32, then this would do it:
Private Sub Append_Button_Click()
Click to expand...
On Error GoTo Err_Append_Button_Click

Dim stDocName As String
Dim intCount as Integer

set dbf = CurrentDB
For intCount = 1 to 32
stDocName = "Append Query-" & Cstr(intCount)
DoCmd.OpenQuery stDocName, acNormal, acEdit
Next intCount

Exit_Append_Button_Click:
Exit Sub

Err_Append_Button_Click:
MsgBox Err.Description
Resume Exit_Append_Button_Click

End Sub
 
what if I didn't name my queries correctly and they are in fact all named
differently, such as "Catalog Items - Adhesives", "Catalog Items -
Automotive", etc.
 
Tw choices.
1. Rename them
2. Create an array with all the names in it and use that. For example:
Private Sub Append_Button_Click()
On Error GoTo Err_Append_Button_Click

Dim stDocName As String
Dim intCount as Integer
DimVarQryList as Variant

varlist = Array("Catalog Items - Adhesives", "Catalog Items -
Automotive",...)
'Put each name in the array
For intCount = 1 to 32 ' May be 0 to 31 depending on your Option Base
stDocName = varQryList(intCount)
DoCmd.OpenQuery stDocName, acNormal, acEdit
Next intCount

Exit_Append_Button_Click:
Exit Sub

Err_Append_Button_Click:
MsgBox Err.Description
Resume Exit_Append_Button_Click

End Sub
 
Option 3 would be forego the loop, and simply have a series of statements.

In my opinion, you shouldn't be using DoCmd.OpenQuery. That's really
intended for opening Select queries (although why you'd want to in an
application is beyond me). More appropriate would be to use the Execute
method of the query.

Going back to your suggestion, that would mean:

Private Sub Append_Button_Click()
On Error GoTo Err_Append_Button_Click

Dim qdfCurr As DAO.QueryDef
Dim stDocName As String
Dim intCount as Integer

For intCount = 1 to 32
stDocName = "Append Query-" & Cstr(intCount)
Set qdfCurr = CurrentDb().QueryDefs(stDocName)
qdfCurr.Execute dbFailOnError
Next intCount

Exit_Append_Button_Click:
Exit Sub

Err_Append_Button_Click:
MsgBox Err.Description
Resume Exit_Append_Button_Click

End Sub

With an array, that would be:

Private Sub Append_Button_Click()
On Error GoTo Err_Append_Button_Click

Dim qdfCurr As DAO.QueryDef
Dim stDocName As String
Dim intCount as Integer
Dim varQryList as Variant

varQryList = Array("Catalog Items - Adhesives", "Catalog Items -
Automotive",...)

For intCount = 1 to 32
stDocName = varQryList(intCount)
Set qdfCurr = CurrentDb().QueryDefs(stDocName)
qdfCurr.Execute dbFailOnError
Next intCount

Exit_Append_Button_Click:
Exit Sub

Err_Append_Button_Click:
MsgBox Err.Description
Resume Exit_Append_Button_Click

End Sub

Without an array, that would be:

Private Sub Append_Button_Click()
On Error GoTo Err_Append_Button_Click

Dim qdfCurr As DAO.QueryDef
Dim stDocName As String

CurrentDb().QueryDefs("Catalog Items - Adhesives").Execute
dbFailOnError
CurrentDb().QueryDefs("Catalog Items - Automotive").Execute
dbFailOnError
.....


Exit_Append_Button_Click:
Exit Sub

Err_Append_Button_Click:
MsgBox Err.Description
Resume Exit_Append_Button_Click

End Sub


(Apologies in advance for line-wrap above)
 
didn't work..I get an error message "The expression On Click you entered as
the event property setting produced the following error: A problem occured
while TSG Test DataBase Rev. 4 was communicating with OLE server or ActiveX
Control.

The follwoing is what I have within the button:

Private Sub Append_Button_Click()
On Error GoTo Err_Append_Button_Click

Dim stDocName As String
Dim intCount As Integer
Dim VarQryList As Variant

varlist = Array("Catalog Items - Adhesives", "Catalog Items -
Automotive", "Catalog Items - Building Materials")

For intCount = 0 To 2 ( I also tried 1 to 3, but same error message)

stDocName = VarQryList(intCount)
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Append_Button_Click:
Exit Sub

Err_Append_Button_Click:
MsgBox Err.Description
Resume Exit_Append_Button_Click

End Sub
 
this one didn't work either. Got the same error message as stated before.

this is what I have...perhaps I am not writing it correctly or leaving
something out.

Private Sub Append_Button_2_Click()
On Error GoTo Err_Append_Button_2_Click

Dim qdfCurr As DAO.QueryDef
Dim stDocName As String
Dim intCount As Integer

For intCount = 1 To 32

stDocName = "Catalog Items -" & CStr(intCount)
Set qdfCurr = CurrentDb().QueryDefs(stDocName)
qdfCurr.Execute dbFailOnError
Next intCount

Exit_Append_Button_2_Click:
Exit Sub

Err_Append_Button_2_Click:
MsgBox Err.Description
Resume Exit_Append_Button_2_Click

End Sub
 
I even tried renaming my queries "correctly" and it still gave me the same
error message.
This is what I had:

rivate Sub Append_Button_2_Click()
On Error GoTo Err_Append_Button_2_Click

Dim stDocName As String
Dim intCount As Integer

Set dbf = CurrentDb
For intCount = 1 To 3

stDocName = "Append Query-" & CStr(intCount)
DoCmd.OpenQuery stDocName, acNormal, acEdit
Next intCount

Exit_Append_Button_2_Click:
Exit Sub

Err_Append_Button_2_Click:
MsgBox Err.Description
Resume Exit_Append_Button_2_Click

End Sub
 
What type of tables are you trying to update? Are they in a Jet database
(i.e. an MDB)? Is it the same MDB as the code, or have you split your
application, so that the tables are in a different MDB? What's the SQL of a
typical query?
 
Just regular tables I created using the wizard...all in the same database.

Here is the SQL of one of the Append queries:

INSERT INTO [Catalog Append Table] ( TSGPartNumber, VendorResourceNumber,
PODescription, VendorName, VendorNumber, Manufacturer, ModelNumber,
BasicCategory )
SELECT [Catalog Items - Building Materials].TSGPartNumber, [Catalog Items -
Building Materials].VendorResourceNumber, [Catalog Items - Building
Materials].PODescription, [Catalog Items - Building Materials].VendorName,
[Catalog Items - Building Materials].VendorNumber, [Catalog Items - Building
Materials].Manufacturer, [Catalog Items - Building Materials].ModelNumber,
[Catalog Items - Building Materials].BasicCategory
FROM [Catalog Items - Building Materials];
 
Sorry, nothing jumps out as being a problem. I assume that the queries run
successfully if you do it manually?

Try compacting your database, in case something's gone amiss. You could also
try starting over with a new form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jimswinder said:
Just regular tables I created using the wizard...all in the same database.

Here is the SQL of one of the Append queries:

INSERT INTO [Catalog Append Table] ( TSGPartNumber, VendorResourceNumber,
PODescription, VendorName, VendorNumber, Manufacturer, ModelNumber,
BasicCategory )
SELECT [Catalog Items - Building Materials].TSGPartNumber, [Catalog
Items -
Building Materials].VendorResourceNumber, [Catalog Items - Building
Materials].PODescription, [Catalog Items - Building Materials].VendorName,
[Catalog Items - Building Materials].VendorNumber, [Catalog Items -
Building
Materials].Manufacturer, [Catalog Items - Building Materials].ModelNumber,
[Catalog Items - Building Materials].BasicCategory
FROM [Catalog Items - Building Materials];

Douglas J Steele said:
What type of tables are you trying to update? Are they in a Jet database
(i.e. an MDB)? Is it the same MDB as the code, or have you split your
application, so that the tables are in a different MDB? What's the SQL of
a
typical query?
Click to expand...
Click to expand...
 
Back
Top