VBA code to loop through access table

[luzz]

Hello everyone, how to have VBA code to read the data in access table row by row and compare it to another table? After reading and comparing the rows among the two table , it will identify data that have been modify and delete the whole rows of data that have modify and add in the new modified data.

 

[MarkK]

I would not read thru table data row by row and compare each row to a second table. Rather, I would join the two tables using SQL to identify rows that meet certain conditions. Consider SQL like...

SELECT table1.*, table2.*
FROM table1 INNER JOIN table2 
   ON table1.ID = table2.ID
   AND table1.Field1 <> table2.Field1

See how that joins on the ID field being equal, but field1 having changed? You should be able to apply logic like this directly in the SQL, and in how you join the two tables, to only return the rows that you are concerned with.
hth
Mark

 

[arnelgp]

it would be good if you just update the old data.

 

[luzz]

I would not read thru table data row by row and compare each row to a second table. Rather, I would join the two tables using SQL to identify rows that meet certain conditions. Consider SQL like...

SELECT table1.*, table2.*
FROM table1 INNER JOIN table2 
   ON table1.ID = table2.ID
   AND table1.Field1 <> table2.Field1

See how that joins on the ID field being equal, but field1 having changed? You should be able to apply logic like this directly in the SQL, and in how you join the two tables, to only return the rows that you are concerned with.
hth
Mark

The problem i am having now is that i do not have a primary key. Do you have any idea how can i do that without primary key?

 

[luzz]

it would be good if you just update the old data.

how to update?

 

[Pat Hartman]

If you don't have a unique identifier (can be a group of up to 10 columns) that allows you to uniquely identify each row, how do you propose to match the records?

 

[MarkK]

I would not worry about updating until you can identify the rows that need it, and the rows that will supply the correct data.

What criteria did you intend to use when you were thinking about looping thru one table, and comparing to the other? Use that criteria in your join, it doesn't need to be on an ID field. The ID field in my example is just an example.

Mark

 

[luzz]

I would not worry about updating until you can identify the rows that need it, and the rows that will supply the correct data.

What criteria did you intend to use when you were thinking about looping thru one table, and comparing to the other? Use that criteria in your join, it doesn't need to be on an ID field. The ID field in my example is just an example.

Mark

The row that i need from the excel file that is imported into my access and the same row that i need for my main table are:
PO, StyleNo, GL Lot, Name, date, Description2, Colour,Fabric Weight,Fabric Cuttable Width,Our Qty,Supplier Qty,GSMBeforeWash,GMS Per SqYD,ModifyDate

 

[MarkK]

And those are the fields that need to match?

 

[luzz]

And those are the fields that need to match?

Yes, and all this field contains duplicate data

 

[MarkK]

So what are the fields that might have changed?

 

[luzz]

So what are the fields that might have changed?

All the field might have the possibility that it will be change

 

[MarkK]

Can you show the logic for this using some sample data? Please show how a change can be detected. What are the exact rules for this?
Mark

 

[luzz]

Can you show the logic for this using some sample data? Please show how a change can be detected. What are the exact rules for this?
Mark

Hi,I have attached the sample data above.
I have two table. One is sample(import from excel) and the other one is the main table.
So basically take the sample table as data that i will be importing from excel and with the insert statement i will put the data from this table into my main table.

I have alter the color, supplier qty and modify date for the first row of PO 12346 in the sample table and you can see that the data for the first row of PO 12346 is different. So if there is modification to the data in the sample table, how can the new data be added into the main table?

I am thinking as long as there is changes made to the PO number, the whole data under the same PO will be deleted and then insert the new data that belong to the same PO in again. What do you think? Or do you have better suggestion?

 

[MarkK]

So which one is correct? Is the data in the Excel import correct? If so, delete everything from main for the PO numbers in the import, and then insert everything from the import.
Delete from Main the POs that exist in sample, like...

DELETE FROM MainTable 
WHERE PO IN ( SELECT DISTINCT PO FROM SampleData )

...and then insert all sample data into Main, like...

INSERT INTO MainTable
SELECT * FROM SampleData

...if the import data is correct.
Mark

 

[arnelgp]

without PK its very hard to identify.
of course i see the changes but to which record in main we
can match, it's very difficult.


are you importing just the one with latest date or all from excel.


i think the only way around this is to delete all records from main,
and reimport all from excel.


then you'll need to worry which one has changed, since on your first
post you want to delete from main those has changed and insert the
updated one.

 

[luzz]

So which one is correct? Is the data in the Excel import correct? If so, delete everything from main for the PO numbers in the import, and then insert everything from the import.
Delete from Main the POs that exist in sample, like...

DELETE FROM MainTable 
WHERE PO IN ( SELECT DISTINCT PO FROM SampleData )

...and then insert all sample data into Main, like...

INSERT INTO MainTable
SELECT * FROM SampleData

...if the import data is correct.
Mark

Yes, the import data is the correct one

 

[luzz]

without PK its very hard to identify.
of course i see the changes but to which record in main we
can match, it's very difficult.


are you importing just the one with latest date or all from excel.


i think the only way around this is to delete all records from main,
and reimport all from excel.


then you'll need to worry which one has changed, since on your first
post you want to delete from main those has changed and insert the
updated one.

I am importing all from excel, as the data is updated every morning thus i will need to import into my access.

If the only way is ti delete all records then reimport again, then i will have difficulties on which PO i have done and which PO i have not done.

 

[luzz]

I tried using this method after my own research, whenever i import data in it will show duplication. How to not have duplication and only import the new data?

 

[isladogs]

Import to a temp buffer table first which includes a pk field.
Then use an unmatched query to append non duplicate records.

If there are dupes in the new excel data, the pk in the buffer table should prevent more than one of each being imported.

If you haven't yet done so, delete all existing dupes then add a PK field to your main table to solve the issue.
Its madness to have a database without using PK fields.