Update and append data in one table from data in another table

[Sandra Walsh]

Hello -

I have a local table in my database called t_CompanyData that holds a subset of the fields in another table called dbo_INT_AUX_LISTING.

dbo_INT_AUX_LISTING is a linked table via an ODBC connection. This is a table in our CRM system and the data in that table is updated constantly.

I need to run a query to:

1 - update the data in t_CompanyData with any updated data for related records in dbo_INT_AUX_LISTING

2 - append any NEW records in dbo_INT_AUX_LISTING to t_CompanyData

I wrote an query that updates data and adds any new records using the strategy at
http://support.microsoft.com/kb/127977

New records are being added to t_CompanyData. However, the value in dbo_INT_AUX_LISTING.LISTING_ID field is not being added to t_CompanyData.IA_CompanyLISTING_ID.

I have verified that the LISTING_ID field in the dbo_INT_AUX_LISTING table is a NUMBER datatype (not autonumber). It is a primary key.

UPDATE (dbo_INT_AUX_LISTING LEFT JOIN t_CompanyData 
ON dbo_INT_AUX_LISTING.LISTING_ID = t_CompanyData.IA_CompanyLISTING_ID) 
LEFT JOIN dbo_INT_AUX_DIRECTORY 
ON dbo_INT_AUX_LISTING.OWN_DIR_ID = dbo_INT_AUX_DIRECTORY.DIRECTORY_ID 
 
SET t_CompanyData.IA_CompanyLISTING_ID = [dbo_INT_AUX_LISTING].[Listing_ID], 
t_CompanyData.COMPANY_NM = [dbo_INT_AUX_LISTING].[Company_NM],
t_CompanyData.DIRECTORY_NM = [dbo_INT_AUX_Directory].[directory_NM];
 

Any suggestions greatly appreciated.
Sandra

[NeoPa]

The settings of t_CompanyData.IA_CompanyLISTING_ID are of far more interest Sandra ;-) If this is an AutoNumber field for instance, it would fail.

PS Congrats on a well prepared question. Such quality from newbie posters is as rare as the proverbial R-H S.

[Sandra Walsh]

Thanks, NP ;)

t_CompanyData.IA_CompanyLISTING_ID is a number - long integer.

Any other clues I can send along?

[NeoPa]

I'm grasping at straws here Sandra, but is it involved as a foreign index to another table maybe? Does it feature in the Relationships diagram?

Frankly I'm surprised at the described behaviour.

[Sandra Walsh]

dbo_INT_AUX_LISTING is not part of the Relationships diagram.

Here are a few images - hope this helps.

[Rabbit]

Why store the company name and directory name if they're already stored in the other tables? When you need them, you can just join the tables together.

[Sandra Walsh]

When I first set up this database it was taking a very long time to load the data from the ODBC tables. They have done some upgrades to our servers and now things are nice and fast.

I think it may be best for me to re-configure the back end to get data directly from the ODBC tables instead of going through this update/append process. Hopefully this wont be too much of a hassle :-)

Thanks all!

[NeoPa]

I see the relationship, but I cannot think of anything that would explain the bahaviour. Even if all the restrictions are set up the value, by definition, must be a valid one as it's the same as the one it's linked to (having been copied from it). I don't know the problem I'm afraid.

[Sandra Walsh]

Thanks NP - maybe some issues with the install. I have been having other strange problems so I might re-install Access.