Append Access Table, delete transferred records, and keep "duplica

[Wissam]

Hi,
Every few months, I recieve a table that includes patients who have high
blood Cholesterol. I want to import from that table the patients who are
"new" to our clinic and have not been seen before by us, and keep the ones
that have been seen by us before.
The code that I have below appends records from “SourceTable†to
“TargetTableâ€. Both tables are set to have the same field names and structure
(such as ID, Name,… ). [ID] in the TargetTable is set as a primary key and
its format is text (something like ab1234, dd2345,..). I want to amend the
code so that it deletes records in SourceTable that get copied to TargetTable
and keep the ones that were not transferred (because they already exist in
the TargetTable); therefore, my goal is:

For each recordset in SourceTable, check if SourceTable.[ID] exists already
among the recordsets in TargetTable.[ID] fields. If not, then transfer the
record to TargetTable and delete it from the SourceTable. If it does exist,
then keep it in SourceTable.

Thanks a lot for any suggestions:

My current Code is as follows:
-------------------------------------------
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Set db = CurrentDb()
Set rsSource = db.OpenRecordset("SourceTable")
Set rsTarget = db.OpenRecordset("TargetTable")
rsSource.MoveFirst
Do Until rsSource.EOF
rsTarget.AddNew
rsTarget![ID] = rsSource![ID]
rsTarget![Name] = rsSource![Name]
….
rsTarget.Update
rsSource.MoveNext
Loop
------------------------------------------------------------------

 

[Steve Sanford]

Try this on a *copy* of your database:

'-----------------------------------------------
Public Sub updatetable()

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()
Set rsSource = db.OpenRecordset("SourceTable")
Set rsTarget = db.OpenRecordset("TargetTable")

rsSource.MoveFirst

Do Until rsSource.EOF
'search for matching record
rsTarget.Index = "PrimaryKey"

rsTarget.Seek "=", rsSource![ID]
' .NoMatch is FALSE if rec found
If rsTarget.NoMatch Then
rsTarget.AddNew
rsTarget![ID] = rsSource![ID]
rsTarget![Name] = rsSource![Name]
rsTarget.Update

'delete the source table record
rsSource.Delete

End If
rsSource.MoveNext
Loop

'clean up
rsSource.Close
rsTarget.Close
Set rsSource = Nothing
Set rsTarget = Nothing
Set db = Nothing

MsgBox "Done"

End Sub
'-------------------------------------


Note: *"NAME"* is a reserved word and shouldn't be used as object names.


HTH

 

[Wissam]

Worked Like a magic. THANKS A LOT!
I also changed the field name from Name to PtName based on your suggestion.
Thanks.
Have a Happy New Year.

Try this on a *copy* of your database:

'-----------------------------------------------
Public Sub updatetable()

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()
Set rsSource = db.OpenRecordset("SourceTable")
Set rsTarget = db.OpenRecordset("TargetTable")

rsSource.MoveFirst

Do Until rsSource.EOF
'search for matching record
rsTarget.Index = "PrimaryKey"

rsTarget.Seek "=", rsSource![ID]
' .NoMatch is FALSE if rec found
If rsTarget.NoMatch Then
rsTarget.AddNew
rsTarget![ID] = rsSource![ID]
rsTarget![Name] = rsSource![Name]
rsTarget.Update

'delete the source table record
rsSource.Delete

End If
rsSource.MoveNext
Loop

'clean up
rsSource.Close
rsTarget.Close
Set rsSource = Nothing
Set rsTarget = Nothing
Set db = Nothing

MsgBox "Done"

End Sub
'-------------------------------------


Note: *"NAME"* is a reserved word and shouldn't be used as object names.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi,
Every few months, I recieve a table that includes patients who have high
blood Cholesterol. I want to import from that table the patients who are
"new" to our clinic and have not been seen before by us, and keep the ones
that have been seen by us before.
The code that I have below appends records from “SourceTable†to
“TargetTableâ€. Both tables are set to have the same field names and structure
(such as ID, Name,… ). [ID] in the TargetTable is set as a primary key and
its format is text (something like ab1234, dd2345,..). I want to amend the
code so that it deletes records in SourceTable that get copied to TargetTable
and keep the ones that were not transferred (because they already exist in
the TargetTable); therefore, my goal is:

For each recordset in SourceTable, check if SourceTable.[ID] exists already
among the recordsets in TargetTable.[ID] fields. If not, then transfer the
record to TargetTable and delete it from the SourceTable. If it does exist,
then keep it in SourceTable.

Thanks a lot for any suggestions:

My current Code is as follows:
-------------------------------------------
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Set db = CurrentDb()
Set rsSource = db.OpenRecordset("SourceTable")
Set rsTarget = db.OpenRecordset("TargetTable")
rsSource.MoveFirst
Do Until rsSource.EOF
rsTarget.AddNew
rsTarget![ID] = rsSource![ID]
rsTarget![Name] = rsSource![Name]
….
rsTarget.Update
rsSource.MoveNext
Loop
------------------------------------------------------------------

 

[Steve Sanford]

Excellent!

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Worked Like a magic. THANKS A LOT!
I also changed the field name from Name to PtName based on your suggestion.
Thanks.
Have a Happy New Year.

Try this on a *copy* of your database:

'-----------------------------------------------
Public Sub updatetable()

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()
Set rsSource = db.OpenRecordset("SourceTable")
Set rsTarget = db.OpenRecordset("TargetTable")

rsSource.MoveFirst

Do Until rsSource.EOF
'search for matching record
rsTarget.Index = "PrimaryKey"

rsTarget.Seek "=", rsSource![ID]
' .NoMatch is FALSE if rec found
If rsTarget.NoMatch Then
rsTarget.AddNew
rsTarget![ID] = rsSource![ID]
rsTarget![Name] = rsSource![Name]
rsTarget.Update

'delete the source table record
rsSource.Delete

End If
rsSource.MoveNext
Loop

'clean up
rsSource.Close
rsTarget.Close
Set rsSource = Nothing
Set rsTarget = Nothing
Set db = Nothing

MsgBox "Done"

End Sub
'-------------------------------------


Note: *"NAME"* is a reserved word and shouldn't be used as object names.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi,
Every few months, I recieve a table that includes patients who have high
blood Cholesterol. I want to import from that table the patients who are
"new" to our clinic and have not been seen before by us, and keep the ones
that have been seen by us before.
The code that I have below appends records from “SourceTable†to
“TargetTableâ€. Both tables are set to have the same field names and structure
(such as ID, Name,… ). [ID] in the TargetTable is set as a primary key and
its format is text (something like ab1234, dd2345,..). I want to amend the
code so that it deletes records in SourceTable that get copied to TargetTable
and keep the ones that were not transferred (because they already exist in
the TargetTable); therefore, my goal is:

For each recordset in SourceTable, check if SourceTable.[ID] exists already
among the recordsets in TargetTable.[ID] fields. If not, then transfer the
record to TargetTable and delete it from the SourceTable. If it does exist,
then keep it in SourceTable.

Thanks a lot for any suggestions:

My current Code is as follows:
-------------------------------------------
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Set db = CurrentDb()
Set rsSource = db.OpenRecordset("SourceTable")
Set rsTarget = db.OpenRecordset("TargetTable")
rsSource.MoveFirst
Do Until rsSource.EOF
rsTarget.AddNew
rsTarget![ID] = rsSource![ID]
rsTarget![Name] = rsSource![Name]
….
rsTarget.Update
rsSource.MoveNext
Loop
------------------------------------------------------------------

 

[Dale Fye]

Not sure why you would want to delete records from your SourceTable? I can
understand just wanting to insert new ones in your TargetTable, but not
deleting data from the original table. What happens if you want to go back
later and look at the original data from the SourceTable? I make it a habit
to NEVER manipulate data in the original file I got it in. I will link to
it, and use it in queries, but I NEVER change the original data. If the data
is corrupt, I'll generally create a backup and work with it.

A simple Append query with a should do what you want.

INSERT INTO TargetTable([Field1], [Field2], ...)
SELECT SourceTable.Field1, SourceTable.Field2, ...
FROM SourceTable LEFT JOIN TargetTable
ON SourceTable.[ID] = TargetTable.[ID]
WHERE TargetTable.ID IS NULL

The key to this is to create the query as a SELECT query first, joining the
ID field from SourceTable to the ID field in TargetTable. Grab all the
fields from SourceTable and the ID field from TargetTable. In the Criteria
table for TargetTable.ID, enter: IS NULL

Then, you need to right click on the join line and select that option that
says something like "Select all from SourceTable and only those that match
from TargetTable".

Now, run your query. You should have all the records not already in
TargetTable.

Now, modify the query to an Append Query, select the TargetTable in the
dialog box that pops up, and, assuming all the fields have the same name
between the tables, the names will fill in properly.

Once you have it working, save the query. Then, all you have to do in code
is a single line.

Currentdb.Execute "qry_Append_New_Clients"

HTH
Dale

Then,
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi,
Every few months, I recieve a table that includes patients who have high
blood Cholesterol. I want to import from that table the patients who are
"new" to our clinic and have not been seen before by us, and keep the ones
that have been seen by us before.
The code that I have below appends records from “SourceTable†to
“TargetTableâ€. Both tables are set to have the same field names and structure
(such as ID, Name,… ). [ID] in the TargetTable is set as a primary key and
its format is text (something like ab1234, dd2345,..). I want to amend the
code so that it deletes records in SourceTable that get copied to TargetTable
and keep the ones that were not transferred (because they already exist in
the TargetTable); therefore, my goal is:

For each recordset in SourceTable, check if SourceTable.[ID] exists already
among the recordsets in TargetTable.[ID] fields. If not, then transfer the
record to TargetTable and delete it from the SourceTable. If it does exist,
then keep it in SourceTable.

Thanks a lot for any suggestions:

My current Code is as follows:
-------------------------------------------
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Set db = CurrentDb()
Set rsSource = db.OpenRecordset("SourceTable")
Set rsTarget = db.OpenRecordset("TargetTable")
rsSource.MoveFirst
Do Until rsSource.EOF
rsTarget.AddNew
rsTarget![ID] = rsSource![ID]
rsTarget![Name] = rsSource![Name]
….
rsTarget.Update
rsSource.MoveNext
Loop
------------------------------------------------------------------