Transfer data from one table to another

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

re: Transfer data from one table to another

On 23 Jan 2006 01:18:11 -0800, "Dave G @ K2" <dave@k2computers.co.uk> wrote:
[color=blue]
>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[/color]

INSERT INTO tblTable1
SELECT *
FROM tblTable2;

Wayne Gillespie
Gosford NSW Australia

re: Transfer data from one table to another

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?

re: Transfer data from one table to another

On 23 Jan 2006 02:09:30 -0800, "Dave G @ K2" <dave@k2computers.co.uk> wrote:
[color=blue]
>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?[/color]

Sorry I forgot the where statement -

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

re: Transfer data from one table to another

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

Cheers Dave