Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Simple Append Query using a command button

Posted on 2003-03-25
19
Medium Priority
?
219 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
  • 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
Industry Leaders: 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

580 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