Simple Append Query using a command button

Hi there I have 5 tables that I want to append to each other using a command button on a form. They all have the same layout. I want to take the 5 tables append them into a new table. I would like to do this in code. Can anyone help me?!

tbl_Sponsor_table1
tbl_Sponsor_table2
tbl_Sponsor_table3
tbl_Sponsor_table4
tbl_Sponsor_table5

INTO

tbl_All_Sponsors
cmcgregorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kd0ecCommented:
Why don't you create your new table(tbl_All_Sponsers) then
use file | import to move the data into the new table. If there are no errors on import, then delete each of the old tables. Of course, you should copy the database before you do this.
0
1WilliamCommented:
Hey!
Use this:
INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor1;

INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor2;

INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor2;

0
cmcgregorAuthor Commented:
I want to do this using a command button. Can you specify the code to use?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

1WilliamCommented:
Your code would be like this:

dim strSQl1 as string
dim strSQl2 as string
dim strSQl3 as string
Dim dbs As Database
Dim qdf As QueryDef
Dim strSQL As String

strSQL1 = "INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor1;"

strSQL2 = "INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor2;"

strSQL3 = "INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor2;"
   
    DoCmd.SetWarnings False
    Set dbs = CurrentDb()

'String 1
    Set qdf = dbs.CreateQueryDef("", strSQL1) ' Create new QueryDef object.
   
    qdf.Execute ' Execute QueryDef object.
   
    MsgBox "You have appended " & qdf.RecordsAffected & " records.@  Now what shall we do?@", vbInformation

String2
Set qdf = dbs.CreateQueryDef("", strSQL2) ' Create new QueryDef object.
   
    qdf.Execute ' Execute QueryDef object.
   
    MsgBox "You have appended " & qdf.RecordsAffected & " records.@  Now what shall we do?@", vbInformation

'String 3
Set qdf = dbs.CreateQueryDef("", strSQL3) ' Create new QueryDef object.
   
    qdf.Execute ' Execute QueryDef object.
   
    MsgBox "You have appended " & qdf.RecordsAffected & " records.@  Now what shall we do?@", vbInformation
   
    DoCmd.SetWarnings True
    Set qdf = Nothing
    Set dbs = Nothing


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrtgoldCommented:
yes - run 1William's SQL statements from code like this:

Private Sub cmdAddTables_Click()
dim i, strSQL

for i = 1 to 5
   strSQL = "INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor" & i & ";"
   docmd.runsql strSQL
next i
end sub
0
cmcgregorAuthor Commented:
Wneh I click the button how would I first save the old tbl_all_sponsors table as BAK_tbl_All_Sponsors (overwriting the old back up) and then wipe out all the records in tbl_all_sponsors then append the 5 tables?
0
mrtgoldCommented:
Private Sub cmdAddTables_Click()
dim i, strSQL

strSQL = "Private Sub cmdAddTables_Click()
dim i, strSQL

docmd.runsql "DELETE BAK_tbl_All_Sponsors.* FROM BAK_tbl_All_Sponsors;"
docmd.runsql "INSERT INTO BAK_tbl_All_Sponsors SELECT * FROM tbl_All_Sponsors;"
docmd.runsql "DELETE tbl_All_Sponsors.* FROM tbl_All_Sponsors;"
 
for i = 1 to 5
  strSQL = "INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor" & i & ";"
  docmd.runsql strSQL
next i
end sub
0
cmcgregorAuthor Commented:
if I just add

docmd.runsql "DELETE BAK_tbl_All_Sponsors.* FROM BAK_tbl_All_Sponsors;"
docmd.runsql "INSERT INTO BAK_tbl_All_Sponsors SELECT * FROM tbl_All_Sponsors;"
docmd.runsql "DELETE tbl_All_Sponsors.* FROM tbl_All_Sponsors;"

after the DIMs in your code william will it work?
0
1WilliamCommented:
Yup
0
mrtgoldCommented:
yep!
0
1WilliamCommented:
Use this:
Dim strSQl1 As String
Dim strSQl2 As String
Dim strSQl3 As String
Dim dbs As Database
Dim qdf As QueryDef
Dim strSQL As String

    strSQl1 = "INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor1;"
   
    strSQl2 = "INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor2;"
   
    strSQl3 = "INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor3;"
       
    DoCmd.SetWarnings False
    Set dbs = CurrentDb()
   
    DoCmd.RunSQL "DELETE BAK_tbl_All_Sponsors.* FROM BAK_tbl_All_Sponsors;"
    DoCmd.RunSQL "INSERT INTO BAK_tbl_All_Sponsors SELECT * FROM tbl_All_Sponsors;"
    DoCmd.RunSQL "DELETE tbl_All_Sponsors.* FROM tbl_All_Sponsors;"
   
    For i = 1 To 3  ' 3 is the number of queries we are running
      strSQL = "INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor" & i & ";"
      MsgBox "You have appended " & qdf.RecordsAffected & " records from table tbl_Sponsor" & i & "to table tbl_All_Sponsors.@  Now what shall we do?@", vbInformation
      DoCmd.RunSQL strSQL
    Next i
   
   DoCmd.SetWarnings True
   Set qdf = Nothing
   Set dbs = Nothing
0
cmcgregorAuthor Commented:
if I just add

docmd.runsql "DELETE BAK_tbl_All_Sponsors.* FROM BAK_tbl_All_Sponsors;"
docmd.runsql "INSERT INTO BAK_tbl_All_Sponsors SELECT * FROM tbl_All_Sponsors;"
docmd.runsql "DELETE tbl_All_Sponsors.* FROM tbl_All_Sponsors;"

after the DIMs in your code william will it work?
0
1WilliamCommented:
Ignore my prevoius post, use this:
Dim strSQl As String
Dim dbs As Database
Dim qdf As QueryDef
Dim strSQl As String
       
    DoCmd.SetWarnings False
    Set dbs = CurrentDb()
   
    DoCmd.RunSQL "DELETE BAK_tbl_All_Sponsors.* FROM BAK_tbl_All_Sponsors;"
    DoCmd.RunSQL "INSERT INTO BAK_tbl_All_Sponsors SELECT * FROM tbl_All_Sponsors;"
    DoCmd.RunSQL "DELETE tbl_All_Sponsors.* FROM tbl_All_Sponsors;"
   
    For i = 1 To 3  ' 3 is the number of queries we are running
        strSQl = "INSERT INTO tbl_All_Sponsors SELECT * FROM tbl_Sponsor" & i & ";"
        Set qdf = dbs.CreateQueryDef("", strSQl3) ' Create new QueryDef object.
        qdf.Execute ' Execute QueryDef object.
        MsgBox "You have appended " & qdf.RecordsAffected & " records from table tbl_Sponsor" & i & "to table tbl_All_Sponsors.@  Now what shall we do?@", vbInformation
    Next i
   
   DoCmd.SetWarnings True
   Set qdf = Nothing
   Set dbs = Nothing
   
0
cmcgregorAuthor Commented:
Actually I cant use the loop because the tables dont actually end in numbers. But it worked when I did it the long way. Thanks very much. I wish I could give both of you points for helping me!
0
cmcgregorAuthor Commented:
Great thanks
0
mrtgoldCommented:
Actually you can - but the minimum is 20, so you can't split this one but you can post another Q with subject "Points for ..."
0
cmcgregorAuthor Commented:
I'll ask another question soon and I will give you extra points!
0
1WilliamCommented:
Hey CM, you promised me that too in the other questions we dealt with last week!
0
cmcgregorAuthor Commented:
shoot your right. I am sorry. I will give you more too. You have helped me so much. I am very sorry.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.