I would like to insert the results of in an ODBC recordset into an Access Table.

Currently I cycle through each value and use "Insert Into.. Value...".

However I feel it would be much cleaner if I could just use "Insert Into.. Select..."

- My query is how do I specify the query that builds my recordset into the Insert Statement? Is there a way of passing the connection details within the sql query?

Hi Ben,

I think the easiest way to write the SQL statement is to build an Append Query in Access (Design View is simplest), then edit the SQL statment, copy-paste it into the code and eventually make the necessary changes.
I asume you use ADODB, so you can pass the SQL statement to the connection.Execute method

Mihai

Hi Mihai,

Thanks for your reply.

I've already written the Select statement I need, however I'm unsure if it will work in an insert query as it is.

For example my query currently reads

sConnect = "PROVIDER=MSDASQL;DSN=xDb"
Set db = New Connection
db.Open sConnect

Set rec = New ADODB.Recordset
sSQL = "Select Col1, Col2.... From tblx"

rec.Open sSQL, db

Do While Not rec.EOF
sSQL = "Insert Into tbl (col1, col2, ...) Values (" & rec(0) & ", "
& rec(1) ... & ")"

CurrentDb.Execute sSQL

rec.MoveNext
Loop

rec.Close

I would like to change this so it reads:

sSQL = "Insert Into tbl (col1, col2, ...) Select Col1, Col2.... From tblx"

CurrentDb.Execute sSQL

rec.Close

The trouble is how do I define the Select query in the second example is via ODBC?
Due to a bug in the driver I can't link the table into the database and I'm adverse to creating a pass-through query in resides in the database.

Anyone any ideas :(:)