INSERT INTO table from recordSet??

[juvi]

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

 

[Ken Snell \(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
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

 

[Ken Snell \(MVP\)]

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

--

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
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

--

Ken Snell
<MS ACCESS MVP>

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