how to write a subroutine to compare two tables?

[longmatch]

Dear Friends:
I need to copy a table from another database based upon the number of records. How can I write the function to do that?


Haijun

 

[BNPMike]

longmatch

Can you expand on your "based on the number of records"? It is difficult to guess what you are trying to do.

If you want to add records from another database but want to ensure that they do not duplicate existing records or alternatively only match existing records or you want to update existing records only if certain field values meet criteria (eg Table.Some_date >= OtherTable.Some_date) then you can do this without writing functions. Tell us more.

 

[longmatch]

Dear Mike:
Your guess is absolutely right. I am sorry I did not state my question right. What I want to do is to synchronize (or update) my table in current database with (from) one table of another database. The data collected in "another database" is to use palm handheld, the data is automatically synchronized to an Access database. I am trying to update information based upon handheld data. Could you tell me the strategy to complete this? Could I use linked table, import or something else?

Thank you

Haijun

 

[BNPMike]

I would import the updating table. Then do a query which joins from your resident table to this import table. You will now have all the 'old' fields and all the 'new' fields. You probably only are interested in a few fields so eliminate the others from the query. You can add selection criteria as I mentioned. Finally you do an update to the 'resident' table just in the normal way (ie select Update Query option from the QBF menu) and eg Set StockCount = ImpStockCount. You can do this in stages or all at once.

As always, read the SQL carefully before you press the button.

 

[BNPMike]

Just to complete the story, if your import table has 'new' records you wish to add to your resident table. Then just generate a select * query from the import table and append it to the resident table. Jet will reject all the existing matches as duplicates. You can of course do this more elegantly with outer joins but you probably don't need to.