CompTech810
asked on
INSERT INTO + Update additional fields in the to file.
I'm inserting data from one database to another but I also want it to update/populate additional fields that aren't in the original database. How would I add that to the following code?
CurrentProject.Connection. Execute "INSERT INTO SupplierRFQSLT SELECT * FROM SUPPLIERRFQ"
CurrentProject.Connection.
If you want to update some fields (or populate in case of empty) than you run an UPDATE query.
The syntax is something like this:
UPDATE table_name
SET column1=value, column2=value2
WHERE column0=value0
The values in the SET statement can also be references to or formulas using other columns instead of just hard-coded values.
The syntax is something like this:
UPDATE table_name
SET column1=value, column2=value2
WHERE column0=value0
The values in the SET statement can also be references to or formulas using other columns instead of just hard-coded values.
You supply those values as expressions.
Rearrange your query like this:
/gustav
Rearrange your query like this:
INSERT INTO
SupplierRFQSLT
(FieldA, FieldB, FieldC, FieldD, FieldE)
SELECT
SourceFieldForFieldA,
SourceFieldForFieldB,
'SomeStringExpression' AS FieldC,
1234 As FieldD,
SourceFieldForFieldE
FROM
SUPPLIERRFQ
/gustav
ASKER
Maybe I'm thinking this the wrong way.
What I want to do I is loop through a query and create a new record(for each loop from query) in the SupplierRfqSlt file by writing all fields from the SupplierRFQ file, which is only one record, and while writing all the fields from SupplierRFQ populate a few fields that are in the SupplierRFQSlt file but not in the SupplierRFQ at the same time. I want to be able to use '*' instead of typing each field and only type the few fields that do not come from SupplierRFQ. I already have the loop working.
What I want to do I is loop through a query and create a new record(for each loop from query) in the SupplierRfqSlt file by writing all fields from the SupplierRFQ file, which is only one record, and while writing all the fields from SupplierRFQ populate a few fields that are in the SupplierRFQSlt file but not in the SupplierRFQ at the same time. I want to be able to use '*' instead of typing each field and only type the few fields that do not come from SupplierRFQ. I already have the loop working.
Here is how to do a fast copy of records using DAO avoiding a loop with SQL insert:
/gustav
Public Sub CopyRecords()
Dim rstSource As DAO.Recordset
Dim rstInsert As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim lngLoop As Long
Dim lngCount As Long
strSQL = "SELECT * FROM tblStatus WHERE Location = '" & _
"DEFx" & "' Order by Total"
Set rstInsert = CurrentDb.OpenRecordset(strSQL)
Set rstSource = rstInsert.Clone
With rstSource
lngCount = .RecordCount
For lngLoop = 1 To lngCount
With rstInsert
.AddNew
For Each fld In rstSource.Fields
With fld
If .Attributes And dbAutoIncrField Then
' Skip Autonumber or GUID field.
ElseIf .Name = "Total" Then
' Insert default job code.
datNow = Now
rstInsert.Fields(.Name).Value = 0
ElseIf .Name = "PROCESSED_IND" Then
rstInsert.Fields(.Name).Value = vbNullString
Else
' Copy field content.
rstInsert.Fields(.Name).Value = .Value
End If
End With
Next
.Update
End With
.MoveNext
Next
rstInsert.Close
.Close
End With
Set rstInsert = Nothing
Set rstSource = Nothing
End Sub
Of course, you will have to modify this to match the fields of your source query/table and to the field names of this./gustav
ASKER
I'm not a VBA programmer so I'm trying to understand the code you provided.
Is tblstatus the tabel that is being cloned to? Please give a little explaination of what the code is doing, I'm kind of lost when I read the code..... I'm a RPG programmer...
Is tblstatus the tabel that is being cloned to? Please give a little explaination of what the code is doing, I'm kind of lost when I read the code..... I'm a RPG programmer...
rstSource and rstInsert are the two recordsets. You will have to adjust the SQL for both as the code here is for copying records from a table to the same table.
It loops through the records, and for each record it loops through the fields, copying/inserting the value. The If .. Else If .. section is for exceptions to the straight copy done after the Else line.
/gustav
It loops through the records, and for each record it loops through the fields, copying/inserting the value. The If .. Else If .. section is for exceptions to the straight copy done after the Else line.
/gustav
ASKER
Ok, I am pretty lost here...... I'm going to bump up the points because this is clear as mud.
Could you change your code to reflect the tables and two fields that don't exist in the to table.
The table that I'm reading from is SupplierRFQ, it has one record. I want to copy that one record to SupplierRFQSlt which is identical to the SupplierRFQ with the exception of a few fields. I am using a query that loops through a contact database that has contacts. I need to add the record from SupplierRFQ to SupplierRFQSlt for each contact in the query and populate the additional fields in SupplierRFQSlt during that add.
The query loop I have in place is working fine with the exception of adding the single record to the table SUpplierRFQSlt for each contact the query finds..
Could you change your code to reflect the tables and two fields that don't exist in the to table.
The table that I'm reading from is SupplierRFQ, it has one record. I want to copy that one record to SupplierRFQSlt which is identical to the SupplierRFQ with the exception of a few fields. I am using a query that loops through a contact database that has contacts. I need to add the record from SupplierRFQ to SupplierRFQSlt for each contact in the query and populate the additional fields in SupplierRFQSlt during that add.
The query loop I have in place is working fine with the exception of adding the single record to the table SUpplierRFQSlt for each contact the query finds..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I actually used an apend query, check this link. I still want you to have the points for all your help!
https://www.experts-exchange.com/questions/27665148/Copy-record-from-on-table-to-another-and-change-a-few-fields-before-add.html
https://www.experts-exchange.com/questions/27665148/Copy-record-from-on-table-to-another-and-change-a-few-fields-before-add.html
That could be used as well for one record. If many records, the VBA method is way faster.
/gustav
/gustav
ALTER TABLE table_name
ADD column_name column-definition;