Appending a recordset to a table

  • Thread starter Thread starter PO
  • Start date Start date
P

PO

Hi,

I use a recordset (rs) to extract records from a table. I then append the
records to an other table by looping through the recordset (se code below):

rs.Open "SELECT RECNUM, DESCRIPTION FROM TEST WHERE RECNUM
in('103668','101687','108009')", cn '(TEST is a table in an external DB)

Do While Not rs.EOF
DoCmd.RunSQL "INSERT INTO MAXDATA VALUES(" & _
'(MAXDATA is a local Access DB)
Chr(34) & rs!RECNUM & Chr(34) & ", " & Chr(34) & rs!Description
& Chr(34) & ");"
rs.MoveNext
Loop


This works fine until the data in the Description field contains a "-sign.

Is there an other way to append the rs recordset to the MAXDATA-table?
Is it possible to append the whole recordset to the MAXDATA-table in one
transaction, for instance MAXDATA.Append rs

Kind regards
PO
 
You could do the whole thing with an Append Query

DoCmd.RunSQL
"INSERT INTO MAXDATA
SELECT RECNUM, DESCRIPTION
FROM TEST WHERE RECNUM In ('103668','101687','108009') "

(all on one line)

Personally, I prefer to use:

CurrentDb.Execute
"INSERT INTO MAXDATA
SELECT RECNUM, DESCRIPTION
FROM TEST WHERE RECNUM In ('103668','101687','108009') ",
dbFailOnError

(all on one line)
 
The problem is that TEST is a table in an external Oracle DB. Therefor I

have to use the recordset as an intermediary (or do I?). My question was if

I can append the entire recordset in one command, i.e. MAXDATA.append rs.

Kind regards

PO
 
Use File / Get External Data / Link ... to link the TEST Table so that it
appears as a Linked Table in Access.

Alternatively, you can use the "In" clause in the Query to specify that TEST
is in an external database.

Check Microsoft JET SQL Reference section of Access Help for the "In"
clause.
 
Back
Top