Import CSV and keep track of deleted records

[DevAccess]

I would like to import CSV file into MS access using VBA, this import would import new records from CSV ( with unique ID ) and delete the records in the destination access table which is not there in incoming CSV. ( by Unique ID )

For example if table in access has records unique ID = A232122 and if this records is not there in CSV, it should delete that record and place that record in another table to know which has been deleted.

I need to have separate table which will tell me deleted records from the access table while it did import process.

I think this needs to be bidirectional process.

How should I accomplish this

 

[arnelgp]

If it is in the csv what should it do update the record?
Add another field to your table, yes/no. This will be the flag to tell u if the unique id is found in the csv.
Before the update flag the field as false. On update flag this as true.
You will need an Update query right join to cvs file:

First update the flag to false:

Update table1 set flag=false;



Then update from csv ( actually add and update):

Update table1 right join csv on table1.id=csv.id set table1.field=csv.field, table1.flag=true;


After the two queries were run inspect the table those with flag=false are not in cvs

 

[DevAccess]

If it is in the csv what should it do update the record?
Add another field to your table, yes/no. This will be the flag to tell u if the unique id is found in the csv.
Before the update flag the field as false. On update flag this as true.
You will need an Update query right join to cvs file:

First update the flag to false:

Update table1 set flag=false;



Then update from csv ( actually add and update):

Update table1 right join csv on table1.id=csv.id set table1.field=csv.field, table1.flag=true;


After the two queries were run inspect the table those with flag=false are not in cvs

What I was thinking is that first I will write function in VBA to check from existing table [taking unique id as reference ] and then go to CSV file, check if that unique ID does not exists copy that recrod from table and paste it to deletedtracker table. if exists dont do anything.

Will write second procedure which will import to access table what contents are in there. basically replace kind of stuff.

Can you please help me with this.

 

[DevAccess]

If it is in the csv what should it do update the record?
Add another field to your table, yes/no. This will be the flag to tell u if the unique id is found in the csv.
Before the update flag the field as false. On update flag this as true.
You will need an Update query right join to cvs file:

First update the flag to false:

Update table1 set flag=false;



Then update from csv ( actually add and update):

Update table1 right join csv on table1.id=csv.id set table1.field=csv.field, table1.flag=true;


After the two queries were run inspect the table those with flag=false are not in cvs

Can you simplify for me above steps. ?

 

[arnelgp]

i don't have the structure of your table.
if possible upload the sample table and the csv
and i will write the code for you.

the steps:
1. link the csv
2. add the flag field.
3. update the table's flag to false.
4. update the the table from csv.
ignoring those already in csv and adding
new from csv.

Update table1 right join csv on table1.id=csv.id
set table1.field1=iif(table1.id=csv.id, table1.field1, csv.field1),
set table1.field2=iif(table1.id=csv.id, table1.field2, csv.field21),
....
....
set flag=true;

 

[DevAccess]

i don't have the structure of your table.
if possible upload the sample table and the csv
and i will write the code for you.

the steps:
1. link the csv
2. add the flag field.
3. update the table's flag to false.
4. update the the table from csv.
ignoring those already in csv and adding
new from csv.

Update table1 right join csv on table1.id=csv.id
set table1.field1=iif(table1.id=csv.id, table1.field1, csv.field1),
set table1.field2=iif(table1.id=csv.id, table1.field2, csv.field21),
....
....
set flag=true;

Here is the attached database and Csv file but please remember the import process has to be done using VBA.

 

[arnelgp]

Here it is made 3 action queries. See you vbs module last part.

 

[DevAccess]

Here it is made 3 action queries. See you vbs module last part.

Thanks a lot for this.

But still I need small help

Can you please list out steps like what steps has to be performed first and all

My query is that import has to happen from CSV to table in Access table and then mechanism to delete the records which are there in existing table but not there in CSV file.

So will this work in that case.

 

[arnelgp]

That is your purpose and that is what i delivered.

 

[DevAccess]

That is your purpose and that is what i delivered.

Thanks, this seems to be good, but it does not work for me.

It does not add record to tbl_deletedBasics.

Consider I have table called tbl_PilotBasic with all import from CSV file.

 

[DevAccess]

Thanks ! this works I was running wrong way, thanks a lot arnelgp

 

[arnelgp]

Ur welcome.