Compare records more efficiently 1

[JunglesMcBeef]

G'day,

I was just wondering if there is an efficient way to compare a record from one table in a db with another record in a table in a completely different db, and see if they are identical. I have a table in access that was originally an import from SQL Server, and now I want to update the access table with any updates to the SQL table each time the access database is opened, but I still want to keep any additions to the current access table (the user can add new items to the table in access too). I know, it would be much easier if the items are only added to the one table and then the table is just linked to access, but there are special items that only apply to the access database. If there is no better way, I am just going to compare each field in each record to see if they are new entries or updated records, and then update the access table as necessary, all with ADO. If anyone can advise me on a better way to do this or can give me some tips to assist with my procedure, I would appreciate that very much.

 

[KenReay]

Hi

assuming you always want to update from the SQl table to the Access table, why bother with a compare?, just do an update query which updates all columns from the SQL table to the Access table, if they haev not changed, so what ?

this is of course assuming you have a unique key to match on..

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JunglesMcBeef]

Thanks for your help. I have another problem now though (this is in no way related to the last problem, well it makes it easier if I say that anyway :-/ ). I don't know if it is just because I am using access 2000 or not, but I can't find the additem method for combobox. This method is available in access 2002, so is it just an issue of versions? I would like to populate the combobox with values from 2 tables, but am not capable of doing it through code because there is no additem method, and I am not aware of any other ways of doing this in VBA. The fields I want to use are from two different tables but have exactly the same properties. They are pretty much identical in all respects except their values. I am guessing there would be a way to overcome my problem but the answer is eluding me. Can you please tell me if I am wasting my time trying to do this? If I am it just means my program will be less efficient (I will have to do more table manipulation than necessary).

 

[PHV]

Have you tried to play with the RowSource and RowSourceType properties of your ComboBox object ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JunglesMcBeef]

Yes I had both tables in the rowsource and the correct rowsourcetype. This did not work at all.

 

[PHV]

What I wanted to say is that you can have a string list or even a function as RowSource.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JunglesMcBeef]

Im not too sure what you are trying to tell me. A string list is completely useless to me, but I've never used a function as a rowsourcetype. Can you please elaborate on this a bit more?

 

[PHV]

From my help file, the RowSourceType can be set to the name of a function, which function must be defined as:
Function nom de fonction(chp As Control, id As Variant, lgn As Variant, col As Variant, code As Variant) As Variant
Don't know if available in access 2000, take a look at your VBA help file.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JunglesMcBeef]

Thank you very much, I tried everything and in the end the RowSourceType function worked perfectly. Much appreciated.