How to add append records from table 1 to table 2

[angel06]

Hello! I'm currently working with my monitoring system now, is there a code to append records from table 1 to table 2? Please help me guys! Thanks.

[AndreRet]

We will need much more information...

which database, what tables, append which fields etc.

[angel06]

I used MS Access 2000 and I am also using an ADODC connection. For example I have two tables Table5 and Table4, once I added records in Table5 using VB, the added records should also be appended in Table4. The field names to be filled are Employee_ID and Employee_Name it can be found in both of the tables. I tried the query INSERT INTO Table4(Employee_ID, Employee_Name) Select Employee_ID, Employee_Name FROM Table5 but it gives me the whole Names and ID number of Table5. What I just want to happen is that the newly added records are the only records that should be inserted in Table4.

[AndreRet]

The reason it returns the incorrect recordset from table 5 is because it is reading from the first record in table 5. You need to use the "WHERE" function in your sql statement to select the correct record, then add the new record to table 4 -

"INSERT INTO Table4(Employee_ID, Employee_Name) Select Employee_ID, Employee_Name FROM Table5 WHERE Employee_ID ='" & YourIdValueHere & "'"

[debasisdas]

What about running parallel insert into both the underlying tables. So each time there is a new entry into table5 the same is also inserted into table4 for desired fields.

[angel06]

Adodc1.RecordSource = "INSERT INTO Table4(Employee_ID, Employee_Name) Select Employee_ID, Employee_Name FROM Table5 WHERE ((Employee_ID <> 2674930) or (Employee_ID <> 2267421))"
Adodc1.Refresh

it gives me an error syntax error in FROM clause and Method of'Refresh' of object ' IAdodc' failed

[angel06]

What about running parallel insert into both the underlying tables. So each time there is a new entry into table5 the same is also inserted into table4 for desired fields

How would i do parallel insert into sir? I have no idea on how to do that. Can you give me the code for that?

[AndreRet]

angel, did you even had a proper look at my code? That is why you got an error. Why use all the brackets etc. (()) Use the code I gave you above, just change the "YourIdValueHere" with a proper value...

If you are going to return a smaller and bigger than search, do the following -

"INSERT INTO Table4(Employee_ID, Employee_Name) Select Employee_ID, Employee_Name FROM Table5 WHERE Employee_ID <'" & YourSmallIdValueHere & "' AND Employee_ID >'" & YourBiggerIdValueHere & "'"

[angel06]

sir andre i still have errors, the same errors i have a while ago. What i want to happen really is for example, I entered new record in employee_ID, 2012345 and David in employee_name in table5, it should appear in the other table(table4). I have other records stored in table5 where i will input the new records, the newly input records should be appended to table4 without appending the other records in table5. I just want to add the new records only the new ones sir

[debasisdas]

when i say parallel insert , what i mean to say is you need to execute 2 insert statments
1. into the table5
2. into the table4
by using the same input values.

and if you try to use sub queries for insert into table4 and execute the code for each new record, a lot of duplicate records will be inserted into table4 because of repeated select of old data from table5.

[BitBlt]

Okay, so there are a couple of things going on here.

1. Your SQL statement as written will include ALL records. (If the Employee_ID = 2674930 then it doesn't equal 2267421, therefore is included...and vice versa.) You should have something like this:

   WHERE Employee_ID <> 2674930 AND Employee_ID <> 2267421

That will exclude both of those records and include all others. Assuming, of course, that's what you're really after.
2. You are trying to use the statement as the RECORDSOURCE for an ADO Data Control on your form, correct? Well, guess what...you can't do that. You'll have to create a new connection object to execute it. Here's some sample code:

Dim myconn As ADODB.Connection
Set myconn = New ADODB.Connection
' Assumes a form, two ADO Data Controls pointing to the same database,
' and a DataGrid bound to the second ADO Data Control.
' Recordsources are set to:
' ADODC1.Recordsource = "select * from Table5"
' ADODC2.Recordsource = "select * from Table4"
' also assumes that this code is embedded in some event procedure that is triggered when you
' want it, either a Command Button Click event or some Change event.
myconn.ConnectionString = Adodc1.ConnectionString

myconn.Open

myconn.Execute "INSERT INTO Table4(Employee_ID, Employee_Name) Select Employee_ID, Employee_Name FROM Table5 WHERE Employee_ID <> 2674930 AND Employee_ID <> 2267421"

myconn.Close

Me.Adodc2.Refresh
Me.DataGrid2.Refresh

Hope this gets you straightened out. Good luck!