Show new imported data ?

[Lukael]

Hi,



I'm importing some data to tables from Excel.

Does anybody have an idea on how I could create a form that would open after Import and show Imported data - only new ones ?

Desirable is to show table records, and make text Bold in fields where Importing was done.

I don't know even where to start, but I'm guessing that It will involve VBA, so I posted here.

Any help much appreciated !!

[CJ_London]

your requirement is not clear

open after Import and show Imported data - only new ones

make text Bold in fields where Importing was done

if you are only showing the new ones, why have bold?

in either event, your table will need a field to indicate data has been imported - either a Boolean flag of some sort or better a timestamp so you can see when imported.

if you go for the latter, you don't necessarily need any vba code, just conditional formatting based on the timestamp field

[Lukael]

Hi ajax,

I just started researching this matter. I added a Date field in my tables, and set Validation rule to Now(). This successfully creates time of when records were created, but problem occurs when there are some fields just updated - then time in records do not change, so I don't know if field was changed.

Do you have any examples to show (sites) for what you're saying, never have done that before ?

your requirement is not clear

I am importing and Updating tables, via VBA. After that is done, I want to see what data was imported - so view of table and highlighting fields where changes are made or new records are created.

[CJ_London]

set Validation rule to Now().

hope you mean default value

after that is done, I want to see what data was imported - so view of table and highlighting fields where changes

a timestamp will only be useful for new records. To see which fields have been updated is not possible - you would need to have something to compare with.

The only way this can be done is to import all records and then compare each new record with the previous one (if exists otherwise this is a new record) on a field by field basis. You will then need to modify your other forms etc to either take the latest view or some sql to delete all 'old' records - taking care to not delete records which are not new and haven't changed.

to compare a record with the previous one, your sqlcode would be something like

SELECT *, isnull(prev.UniqueID) as NewRecord, cur.fld1<>nz(prev.fld1) as fl21Changed,cur.fld2<>nz(prev.fld2) as fld2Changed,...
FROM myTable Cur Left JOIN myTable Prev On Cur.UniquePK=Prev.UniquePK
WHERE Cur.timestamp>Date() and Prev.timestamp=(SELECT Max(TimeStamp) FROM myTable WHERE TimeStamp<Date())

note this assumes you are importing no more than once a day - if you do, you will need to trap the time of the import started and use that instead of Date()

NewRecord will be true if there isn't a previous record but not really required, since all the other fields will be true anyway, just shown for illustration purposes and perhaps indicated in your form
fld1changed etc will be true either for a new record or if changed

so your control conditional formatting would be set on the state of fld1changed etc.

Note if importing a lot of data this could be quite slow

also recommend you index the timestamp field

[Lukael]

hope you mean default value

Yes, my typing mistake.

Thanks for guidelines, I assumed there would be issues with updated data. I'll study a little bit on this matter, but If I won't resolve It, I'll just open a form where all data from table will be shown.

[Paul H]

Or you could set a field "New" to true for all new records, then before your next import reset everything to False. This could be in addition to your timestamp. Just set the RecordSource on your form to be "Select * from myTable where New = True".

[Lukael]

Or you could set a field "New" to true for all new records, then before your next import reset everything to False. This could be in addition to your timestamp. Just set the RecordSource on your form to be "Select * from myTable where New = True".

thanks Paul, will this also work for Updated fields in records ?

So, If I understand you.... I create a field called "New", data type YES/NO. Before Import I run SQL like "UPDATE MyTable SET New=False". And then I run Import. And when creating a form where I want to see all changed or added records in this table, I add Recordsource as you said ?

[CJ_London]

think you will find that will identify new records, but not of any use for identifying changed fields

[Lukael]

thanks Ajax,

I think that your solution is only solution. Problem is only that I don't quite follow your instructions.

What about this, please check - In Update SQL set WHERE NOT EXISTS clause and change Timestamp field only there :

SQL = " UPDATE DestinationTable" & _
      " INNER JOIN SourceTable" & _
      " ON DestinationTable.Field1=SourceTable.Field1 OR DestinationTable.Field2=SourceTable.Field2" & _
      " WHERE NOT EXISTS(SELECT * FROM DestinationTable WHERE(DestinationTable.Field1=SourceTable.Field1 OR DestinationTable.Field2=SourceTable.Field2))" & _
      " SET DestinationTable.Field1=SourceTable.Field1, DestinationTable.Field2=SourceTable.Field2, DestinationTable.DateField=Now()"

I've tried this, but nothing happens, no update executed. But If I remove "WHERE NOT EXISTS" line, then update is done, but all "DateField" are changed to Now(), even old records which are not changed.

[CJ_London]

not sure what you are trying to achieve or why, but looks like your not exists function negates the join - i.e. excludes all records that are found by the join.

[Lukael]

Yes, I figured that out by now.

I'm considering another solution, let me explain this one....

So, I'm doing Import & Update from Excel file into Access table:

1. I import all Excel data into SourceTable;

2. I create a "unmatched" query with Destination Table - this successfully shows me what I need - desired output of records that are not same.

3. Then in VBa I run SQL "INSERT INTO DestinationTable" and "UPDATE DestinationTable".


After that "unmatched" query ofcourse doesn't show anymore differences. So, basically what I need is to store these "unmatched" before SQL ? Can this be done, and how ? Put that "unmatched" data into new table with another "INSERT INTO" ? Am I right this time ?

[CJ_London]

I would put in the same table rather than a new one

Just be aware that by updating, even if you change the timestamp, you will have no way of identifying which fields have changes, only those records that have changed

[Lukael]

I finally managed It

I created a new table, where selects of unmatched records from DestinationTable and SourceTable are. This is how I've done It :

1. I created a "unmatched" Query via Access Query Wizard - from this query I pasted SQL clause and used this in VBA, and then deleted Query. Now VBA begins only.

2. I do Transfersheet method into Source table - all Excel data.

3. immediately after Transfersheet method I run 3 SQL's - first is "INSERT INTO NEW_UnmatchedTable" (this is used with "unmatched" Query that I deleted), then " UPDATE DestinationTable from SourceTable" and finally "INSERT INTO DestinationTable FROM SourceTable".

4. I've created a form from NEW_UnmatchedTable and voila - I can see now which records have been added or updated


Ofcourse, in the beginning of my code I also delete everything from NEW_UnmatchedTable, otherwise form would show previous records too.

I would put in the same table rather than a new one

I have explained how I'm doing Import - I do that because IMPORTING has to be done without duplicates, simple Transfersheet method doesn't exclude duplicates (or at least I think so). So I have to create a table for Imports, and table for "unmatching records". How would you do that all in same table ?

Just be aware that by updating, even if you change the timestamp, you will have no way of identifying which fields have changes, only those records that have changed

Yes, I have noticed that, but It's efficient enough for me. It would be better to highlight exact fields, but this is also good.

[CJ_London]

You have to do what you are happy with.

[Lukael]

Ajax,

I'm happy with this also. Just tell me one more thing please - I run all SQL using DAO.Database (db.Execute SQL_1, db.Execute SQL_2 etc..). Is this approach good, or I should be using .BeginTrans method instead, since I have multiple Queries to execute ?

I'm just trying to make code as decent as It should be.