?
Solved

Simple Append Query using a command button

Posted on 2003-03-25
19
Medium Priority
?
217 Views
Last Modified: 2008-03-10
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
0
Comment
Question by:cmcgregor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
  • +1
19 Comments
 

Expert Comment

by:kd0ec
ID: 8203041
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
 
LVL 18

Expert Comment

by:1William
ID: 8203048
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
 

Author Comment

by:cmcgregor
ID: 8203055
I want to do this using a command button. Can you specify the code to use?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Accepted Solution

by:
1William earned 100 total points
ID: 8203084
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
 
LVL 2

Expert Comment

by:mrtgold
ID: 8203092
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
 

Author Comment

by:cmcgregor
ID: 8203168
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
 
LVL 2

Expert Comment

by:mrtgold
ID: 8203255
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
 

Author Comment

by:cmcgregor
ID: 8203308
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
 
LVL 18

Expert Comment

by:1William
ID: 8203321
Yup
0
 
LVL 2

Expert Comment

by:mrtgold
ID: 8203322
yep!
0
 
LVL 18

Expert Comment

by:1William
ID: 8203339
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
 

Author Comment

by:cmcgregor
ID: 8203344
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
 
LVL 18

Expert Comment

by:1William
ID: 8203361
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
 

Author Comment

by:cmcgregor
ID: 8203392
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
 

Author Comment

by:cmcgregor
ID: 8203396
Great thanks
0
 
LVL 2

Expert Comment

by:mrtgold
ID: 8203421
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
 

Author Comment

by:cmcgregor
ID: 8203452
I'll ask another question soon and I will give you extra points!
0
 
LVL 18

Expert Comment

by:1William
ID: 8203494
Hey CM, you promised me that too in the other questions we dealt with last week!
0
 

Author Comment

by:cmcgregor
ID: 8203506
shoot your right. I am sorry. I will give you more too. You have helped me so much. I am very sorry.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question