Loop using append query

[Martin]

Hello,

I have some code that I cant get to work. I want to run an append query for
each record in a table. Here is the code:

For Each rs In Recordset
DoCmd.RunSQL "INSERT INTO [Import FN] ( Postcode, Brand, [Branch Name],
Sortcode, DistanceCalc, [Distance(km)], [Distance(miles)] )SELECT TOP 1
[Nearest 01].Postcode, [Nearest 01].Brand, [Nearest 01].[Branch Name],
[Nearest 01].Sortcode, [Nearest 01].DistanceCalc, [Nearest
01].[Distance(km)], [Nearest 01].[Distance(miles)] FROM [Nearest 01]"
Next

The append query works but I think I am getting stuck with running that for
each rs.

Can anyone point me in the right direction?

Martin

 

[vbasean]

I don't think you're going to be able to do this with the runSQL command:

dim rsFROM as recordset
dim rsTO as recordset
dim db as database
set db = currentdb
set rsFROM = db.openrecordset("[Nearest 01]")
set rsTO = db.openrecordset("[Import FN]")
rsFROM.movefirst
do while not rsFROM.eof
With rsTO
..Add
rsTO!Postcode = rsFROM!Postcode
rsTO!Brand = rsFROM!Brand
rs... etcetera (not willing to type all the fields LOL)
..Update
End With
rsFROM.movenext
Loop

 

[Martin]

Thanks for the response.

This has worked perfectly.

Many thanks,

Martin

I don't think you're going to be able to do this with the runSQL command:

dim rsFROM as recordset
dim rsTO as recordset
dim db as database
set db = currentdb
set rsFROM = db.openrecordset("[Nearest 01]")
set rsTO = db.openrecordset("[Import FN]")
rsFROM.movefirst
do while not rsFROM.eof
With rsTO
.Add
rsTO!Postcode = rsFROM!Postcode
rsTO!Brand = rsFROM!Brand
rs... etcetera (not willing to type all the fields LOL)
.Update
End With
rsFROM.movenext
Loop

Hello,

I have some code that I cant get to work. I want to run an append query for
each record in a table. Here is the code:

For Each rs In Recordset
DoCmd.RunSQL "INSERT INTO [Import FN] ( Postcode, Brand, [Branch Name],
Sortcode, DistanceCalc, [Distance(km)], [Distance(miles)] )SELECT TOP 1
[Nearest 01].Postcode, [Nearest 01].Brand, [Nearest 01].[Branch Name],
[Nearest 01].Sortcode, [Nearest 01].DistanceCalc, [Nearest
01].[Distance(km)], [Nearest 01].[Distance(miles)] FROM [Nearest 01]"
Next

The append query works but I think I am getting stuck with running that for
each rs.

Can anyone point me in the right direction?

Martin

 

[John W. Vinson]

I have some code that I cant get to work. I want to run an append query for
each record in a table. Here is the code:

Why not do it in a single query?

strSQL = "INSERT INTO [Import FN] ( Postcode, Brand, [Branch Name],
Sortcode, DistanceCalc, [Distance(km)], [Distance(miles)] )SELECT
First([Nearest 01].Postcode), First([Nearest 01].Brand), First([Nearest
01].[Branch Name]),First([Nearest 01].Sortcode), First([Nearest
01].DistanceCalc),First( [Nearest 01].[Distance(km)]), First([Nearest
01].[Distance(miles)]) FROM [Nearest 01] GROUP BY [Nearest01].[<whatever field
makes your recordset unique>]"

<then create a Querydef and use the querydef's Execute method>