Member Avatar for 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.

  • 4 Contributors
  • 10 Replies
  • 253 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

Summary and practical fixes for appending only the newly added rows from Table5 into Table4.

The clean choices are (A) write to both tables when you create the new employee row (so only the new row goes to Table4), or (B) run an append query that selects only rows that are not already in Table4. The errors seen in the thread come from two things: (1) trying to use ADODC.RecordSource for an action query (ADODC expects a SELECT recordset), and (2) a logical mistake using OR with <> which effectively matched everything. Those points were already raised by and ; below are concrete, safe ways to implement each approach.

Append only missing rows (recommended for ad‑hoc sync)

INSERT INTO Table4 (Employee_ID, Employee_Name)
SELECT t5.Employee_ID, t5.Employee_Name
FROM Table5 AS t5
LEFT JOIN Table4 AS t4
  ON t5.Employee_ID = t4.Employee_ID
WHERE t4.Employee_ID IS NULL;

This inserts only Table5 rows whose Employee_ID is not present in Table4 (no duplicates).

Parallel insert at creation (recommended if you control the VB insert)

Sub SaveEmployee(empId As Long, empName As String)
  Dim cn As New ADODB.Connection
  Dim cmd As New ADODB.Command

  cn.ConnectionString = Adodc1.ConnectionString
  cn.Open
  On Error GoTo ErrHandler
  cn.BeginTrans

  With cmd
    .ActiveConnection = cn
    .CommandType = adCmdText

    .CommandText = "INSERT INTO Table5 (Employee_ID, Employee_Name) VALUES (?, ?)"
    .Parameters.Append .CreateParameter("id", adInteger, adParamInput, , empId)
    .Parameters.Append .CreateParameter("nm", adVarChar, adParamInput, 255, empName)
    .Execute

    .CommandText = "INSERT INTO Table4 (Employee_ID, Employee_Name) VALUES (?, ?)"
    .Execute
  End With

  cn.CommitTrans
Cleanup:
  If cn.State = adStateOpen Then cn.Close
  Set cmd = Nothing: Set cn = Nothing
  Exit Sub
ErrHandler:
  cn.RollbackTrans
  Resume Cleanup
End Sub

Extra tips

  • If Employee_ID is numeric, do not wrap it in quotes.
  • Add a unique index / primary key on Table4.Employee_ID to prevent duplicates.
  • Execute action SQL via ADODB.Connection/Command (not ADODC.RecordSource), then refresh the UI-bound recordset.
  • Wrap multi-statement saves in a transaction to avoid partial writes (as shown).

Those steps combine the ideas from (parallel insert) and / (filtering and correct execution path) into robust, production-safe patterns.

Recommended Answers

All 10 Replies

Member Avatar for AndreRet

We will need much more information...

which database, what tables, append which fields etc.

Member Avatar for 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.

Member Avatar for 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 & "'"
Member Avatar for 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.

commented: True, much faster with less errors, thanx debasis +12
Member Avatar for 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

Member Avatar for 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?

Member Avatar for 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 & "'"
Member Avatar for 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

Member Avatar for 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.

Member Avatar for 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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.