copying data from table one to table two

There are two tables with the same fields say 'firstname' and 'secondname'.

How do I copy all the records from the first table(tableone.mdb) to the second table(tabletwo.mdb) which satisfy the condition that the second name should be 'smith'????

use an append query.

if you are using access this is relatively simple using the query builder. have a quick look in the help section

if you want to use SQL then:

Code:

---

INSERT INTO [Table2] ( [Field 1], Field2)
SELECT Table1.[FIeld 1], Table1.Field2
FROM Table1
WHERE Table1.Surname = "Smith";

---

thankx a ton, i'll try that today by the way i am using VB as frontend and access as backend....

yes it can be used in vb.

you need to declare and set the database object to use it.

Code:

---


Dim dbs as Database

set dbs = "c:\location of database"

dbs.Execute "COPY SQL STATEMENT HERE"

---

Sorry was out of town

will try this code today

appreciate your help