Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-14-2014, 02:34 PM   #1
AlSallam
Newly Registered User
 
Join Date: Nov 2013
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
AlSallam is on a distinguished road
Append data from several tables to one table

I have the following code, the purpose of the code is that to take all rows from each table to append them into one table. However, I am testing this code with 2 tables (Table2 and Table3) each table has 2 records, when I run the code, it keeps adding records to table 1 that exceeds one million. what is wrong with my code?
Dim tblString, I As Integer
Dim rstFrom As Recordset, rst2 As Recordset
Dim db As Database

Set db = CurrentDb
Set rst2 = db.OpenRecordset("Table1", dbOpenDynaset)
tblString = Array("Table2", "Table3")
For I = 0 To 1

Set rstFrom = db.OpenRecordset(tblString(I), dbOpenDynaset)

If Not rstFrom.BOF Then
rstFrom.MoveFirst
Do Until rstFrom.EOF
With rst2
.AddNew
rst2![Field1] = rstFrom![Field1]
rst2![Field2] = rstFrom![Field2]
rst2![Field3] = rstFrom![Field3]
.Update
End With
Loop
End If
Next I
Set rst2 = Nothing
Set rstFrom = Nothing
Set db = Nothing

AlSallam is offline   Reply With Quote
Old 02-14-2014, 02:43 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,166
Thanks: 10
Thanked 3,887 Times in 3,830 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Append data from several tables to one table

You forgot the MoveNext on the from recordset and created an endless loop.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
AlSallam (02-14-2014)
Old 02-14-2014, 02:47 PM   #3
AlSallam
Newly Registered User
 
Join Date: Nov 2013
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
AlSallam is on a distinguished road
Re: Append data from several tables to one table

You are right thank you

AlSallam is offline   Reply With Quote
Old 02-14-2014, 02:51 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,166
Thanks: 10
Thanked 3,887 Times in 3,830 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Append data from several tables to one table

Happy to help.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Tags
addnew , append , movefirst

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Append Data from 2 Tables (w/ matching fields) into a 3rd Table - with DAO Recordset Cosmonaut_99 Modules & VBA 5 12-11-2012 11:28 PM
How to use append query to update data in three tables? Garindan Queries 8 02-04-2011 04:56 PM
I cant append data in tables after txt import!!! mihalisp General 20 10-29-2009 05:01 AM
Trying to append data of two tables TIbbs Queries 7 06-04-2008 07:24 AM
[SOLVED] common data in two tables of different size, how do i append from one to the other..? GeorgeP General 2 07-23-2006 09:09 PM




All times are GMT -8. The time now is 09:23 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World