Duplicate Record Query

[wizard989]

Does any one know how i can write a sql statement in VB 5
to be able to filter two tables so that it will only return
all records that are different in both tables.
i.e
If you had 2 tables that were exactly the same except for
1 record then only that record would be returned.

Sounds easy?

Well the catch is that the field name(s) and number of fields are unknown each time the query is run.

Many thanks if you can help

Joe

[paulw]

Need a few more details Joe. Are the tables guaranteed identical formats - if not how do you compare?

All fields have to be identical? What about duplicates...

Give me more and I will see if I can help - you could create a query that JOINS all fields and then discard all records that are IN the returned dataset or you could do it in code by a brute force search - I have actually done this and it is reasonably quick.

If this appeals I can formulate some code that will do it - but not 'til Monday!

Cheers,

Paul.

[Anakim]

I think the only way to do it would be to interrogate the sys tables to get the table definitions and build the query that way.

On not quite as complex lines as those you've outlined I did this to get an app which would loop through an everchanging list of tables on a db and rename a user specified column to another given column (or delete, insert or change values).

Thing is though if the columns are unknown how would you effect the join?

[paulw]

Joe says the field names are unknown - not the Table names so you use the TableDefs collection to retrieve field info.

You can loop through in code (and that is the approach I used when I had a similar problem) but the request is for some SQL - I think it is easier in code but the result may be less efficient.

I prefer the control you get with code but ther you go...

[wizard989]

Yes paulw,
You are correct with the tabledefs that is what i have done.

As to the rest, well basically what i am trying to do is write code that will allow a field user to update tables that they have changed from the main database.

My code attempts to detect which tables have been changed and using an original backup copy of each table i would compare records for both tables to filter for the changed records.So the fields are 100% identical.

(I have just realised that the method i was using for detecting whether the tables had been changed was actually for the tables themselves - not the records inside them DOH!!!!!)


Perhaps maybe there is a better approach than the way i am thinking. Any suggestions etc gratefully received.

I dont want to use ms synchronised tables for various reasons.

Hope you can follow my ramble.

Many Thanks again.

Joe