Append entire recordset

[Guest]

Is there an easy way to append an entire recordset to a table? I want all
the fields but I don't want to use this statement because it can cause
duplicate id's. I want different autonumber ID's in the destination table.

INSERT INTO TblSab900_history SELECT * FROM TblSab900 Where ID=" & Rs!ID

The only other way I know is to define to recordsets and move each
individual field and then to an update to the destination table.

Any advice would be appreciated.

Thanks,

Kerry

 

[Van T. Dinh]

INSERT INTO ... SQL is more efficient than traversing a Recordset,
especially with large number of rows to be inserted.

All you need to do is to list the Fields except for the ID Field, something
like:

INSERT INTO TblSab900_history
( {List of Fields except ID Field} )
SELECT {List of corresponding Fields except ID Field}
FROM TblSab900
WHERE {criteria if required}

 

[Guest]

Thanks but I was trying to avoid that also as to not list all the field names
in case new ones are added. But unless I hear of something else that is what
I will do.

Thanks,

Kerry

 

[John Vinson]

Thanks but I was trying to avoid that also as to not list all the field names
in case new ones are added. But unless I hear of something else that is what
I will do.

That is what's needed, if you want the ID to autoincrement. * means
append all fields, and if you append all fields you're appending the
ID - and getting ID errors as a result.

You don't need to type them all out though: just shift-select them in
the list of fields, and drag to the Field row of the query grid. Three
seconds tops.

John W. Vinson[MVP]

 

[Dirk Goldgar]

Thanks but I was trying to avoid that also as to not list all the
field names in case new ones are added. But unless I hear of
something else that is what I will do.

Thanks,

Kerry

If you're building the SQL statement in code, you could build the field
list on the fly by inspecting the table definition. The following rough
routine gives the idea, though it doesn't contain any error-handling or
cover all possible bases:

'----- start of code -----
Function fncFieldList( _
pstrTableName As String, _
Optional pblnExcludeAutonumber As Boolean) _
As String

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strFieldList As String

Set db = Application.DBEngine.Workspaces(0).Databases(0)

Set tdf = db.TableDefs(pstrTableName)
For Each fld In tdf.Fields
If (fld.Attributes And dbAutoIncrField) Then
If Not pblnExcludeAutonumber Then
strFieldList = strFieldList & ", [" & fld.Name & "]"
End If
Else
strFieldList = strFieldList & ", [" & fld.Name & "]"
End If
Next fld

Set tdf = Nothing
Set db = Nothing

If Len(strFieldList) > 0 Then
fncFieldList = Mid$(strFieldList, 3)
End If

End Function
'----- end of code -----

In your case, you'd want to call the function with the option argument
pblnExcludeAutonumber set to True.

 

[Van T. Dinh]

While there ways to handle this by code (e.g. Dirk's posted code), IMHO,
this is generally not necessary since in a properly structured / developed
and implemented database, new Fields are rarely added since adding new
Fields means that all other Access objects that are based on the relevant
Tables will need to be modified to work with the new Table Fields. You
already found that out about Queries but there are Forms / Reports / VBA
codes ... that are more likely to be more complex to modify.

Remember that in a properly designed / developed and implemented database
application, we add data by adding Records, not Fields unless there is a
change in the requirements that necessitate the modification of the Table
Structure. If you see that you will need to regularly add Fields, you may
have an incorrect Table Structure. If this is the case, you should really
look at the Table Structure and re-design it so that data can be added by
adding Records and not Fields.