Transfer data from one table to another

[Dave G]

Using vba it is easy to transfer a single record from one table to
another, where the two tables have the same structure. But its long
winded when there are, say, 100 fields. So is there a clever bit of
code that can achieve this without rs2![Field38] = rs1![Field38] and so
on.......?

Thanks
Dave

[Wayne Gillespie]

On 23 Jan 2006 01:18:11 -0800, "Dave G @ K2" <da**@k2computers.co.uk> wrote:

Using vba it is easy to transfer a single record from one table to
another, where the two tables have the same structure. But its long
winded when there are, say, 100 fields. So is there a clever bit of
code that can achieve this without rs2![Field38] = rs1![Field38] and so
on.......?

Thanks
Dave

INSERT INTO tblTable1
SELECT *
FROM tblTable2;

Wayne Gillespie
Gosford NSW Australia

[Dave G]

Sorry if I'm being thick here - but how does that transfer just one
record? Ah but wait, what if the second line was something like SELECT
* WHERE tblTable2.ID = 86 or whatever. Would that do it?

[Wayne Gillespie]

On 23 Jan 2006 02:09:30 -0800, "Dave G @ K2" <da**@k2computers.co.uk> wrote:

Sorry if I'm being thick here - but how does that transfer just one
record? Ah but wait, what if the second line was something like SELECT
* WHERE tblTable2.ID = 86 or whatever. Would that do it?

Sorry I forgot the where statement -

INSERT INTO tblTable1
SELECT *
FROM tblTable2
WHERE ID=86;
Wayne Gillespie
Gosford NSW Australia

[Dave G]

Thanks Wayne. Simple really - I'm embarassed I didn't know this - I
must do a bit more SQL

Cheers Dave