copying data from table one to table two

[Siddharth Rout]

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'????

[Br1an_g]

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:

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

[Siddharth Rout]

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

[Br1an_g]

yes it can be used in vb.

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

Dim dbs as Database

set dbs = "c:\location of database"

dbs.Execute "COPY SQL STATEMENT HERE"

[Siddharth Rout]

Sorry was out of town

will try this code today

appreciate your help