Check to see if linked table has changed

[TKnight]

Hi, my question is fairly simple but i'm going to post the background because I feel like my solution is a bit long winded and hopefully someone can point me in a better direction...

We have an off-the-shelf (very inflexible) oracle system at work and my job (amongst other things) is to build databases which keep data which isn't catered for in the oracle system. If I need data in my DB that is stored in the Oracle DB I link to it to save double keying. The problem is that we're not allowed to connect to the Oracle Tables using ODBC (even read-only) and instead we get text file extracts which get updated overnight to keep them fresh.

The text files are usually pretty big and working with them linked to my DBs slowed things down too much so I wrote code which imported the relevant text files when the first user of the day logged in. This worked fine initially but because the text files come from another department they have a nasty habit of adding a field occasionally in the middle of the text file and then the import spec gets thrown out and really messes things up!

Now i'm working on a solution which will effectively check to see if a table (linked to the text file) has changed its format before appending any new records to the table in my DB.

To do this i'm using 3 tables for each table that comes from the Oracle Extract

eg
tblClientDetails- Table to import to
tLnkClientDetails- Table Linked to txt file
tLnkClientDetailsComp - Comparison table which has 1st line only of tLnkClientDetails last time it was succesfully imported.

I was thinking of using DAO to open the linked table and the comparison table and make sure the field names of each field match.
Eg

If rstLinkedTable.Fields(1).Name = rstComparisonTable.Fields(1).Name Then
Field Name hasn't changed
Else:
Field Name has changed
End If

So finally to my questions!
1) Will the above work for checking the names of the fields in the tables?
2) Is there a much simpler solution that i'm totally missing (I hope so!)

Thanks, Tom

 

[The Stoat]

Hi,

Just a thought, You could import the data into a fresh table and do an append query. An error should be generated if the field names are not the same.

docmd.runsql("INSERT INTO NEW_TBL_STAFF
SELECT TBL_STAFF.*
FROM TBL_STAFF;")

If there is an error you could then catch it and know that the tables are different.

TS

 

[TKnight]

Thanks for the help, that would work with the field names. I keep forgetting that sometimes errors are useful!

I've set it up now with a field name and data type check and it works quite well.

Thanks again, Tom