INSERT INTO table from recordSet??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

juvi - 29 Mar 2008 17:28 GMT

Hello,

Is it possible to run an INSERT INTO table command and pass over a recordset?

Now I must write all field-names:
INSERT INTO tbl_test([Field1], [Field2]......) VALUES ( .. rs ! Field1 .... )

this works fine but it is hard work if columns are deleted or added.
Something like the following would be nice if possible:
INSERT INTO tbl_test FROM rs??????

thank you in advance
juvi

Reply to this Message

Ken Snell (MVP) - 29 Mar 2008 23:50 GMT

You cannot do it directly with rs in the SQL statement, but something like
this might work (this assumes that you have already created the rs object
and that its fields are in the same order as in the tbl_test table) -- NOTE
that this code assumes that the fields in rs recordset all are numeric -- if
some are text or date, then the code would need to be modified to test the
datatype of each rs field and then use the appropriate delimiters around the
value based on the datatype:

Dim strSQL As String
Dim lngFields As Long
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("tbl_test")
strSQL = "INSERT INTO tbl_test ("
For lngField = 0 to tdf.Fields.Count - 1
strSQL = strSQL & tdf.Fields(lngField).Name & ", "
Next lngField
strSQL = Left(strSQL, Len(strSQL) - 2) & ") VALUES ("
For lngField = 0 to rs.Fields.Count - 1
strSQL = strSQL & rs.Fields(lngField).Value & ", "
Next lngField
strSQL = Left(strSQL, Len(strSQL) - 2) & ")
dbs.Execute strSQL, dbFailOnError
Set tdf = Nothing
dbs.Close
Set dbs = Nothing

Signature

Ken Snell
<MS ACCESS MVP>

> Hello,
>
[quoted text clipped - 11 lines]
> thank you in advance
> juvi

Reply to this Message

Ken Snell (MVP) - 30 Mar 2008 02:03 GMT

Forgot a trailling " character:

Dim strSQL As String
Dim lngFields As Long
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("tbl_test")
strSQL = "INSERT INTO tbl_test ("
For lngField = 0 to tdf.Fields.Count - 1
strSQL = strSQL & tdf.Fields(lngField).Name & ", "
Next lngField
strSQL = Left(strSQL, Len(strSQL) - 2) & ") VALUES ("
For lngField = 0 to rs.Fields.Count - 1
strSQL = strSQL & rs.Fields(lngField).Value & ", "
Next lngField
strSQL = Left(strSQL, Len(strSQL) - 2) & ")"
dbs.Execute strSQL, dbFailOnError
Set tdf = Nothing
dbs.Close
Set dbs = Nothing

Signature

Ken Snell
<MS ACCESS MVP>

> You cannot do it directly with rs in the SQL statement, but something like
> this might work (this assumes that you have already created the rs object
[quoted text clipped - 39 lines]
>> thank you in advance
>> juvi

Reply to this Message