Update Linked File?

[Bill Lentz]

Can I update a linked file with an update query? Here's what I'm
trying to do:

CUSTOMER is a linked dbaseIV table. tblSingleLargeSale (SLS for
short) is an Access Table.

CUSTOMER has many fields, the following are relevant:

ACCOUNTNO
SALESMAN1


SLS has the following fields:

CUSTNO (same as CUSTOMER.ACCOUNTNO)
MaxOfPrice
EMPLOYEE


There are many more records in the CUSTOMER linked db than are in the
SLS table, however, there are no records in the SLS table that don't
have a matching CUSTOMER.ACCOUNTNO record.

I'm trying to update the CUSTOMER.SALESMAN1 field with the relevant
data in the SLS.EMPLOYEE field. Relevant meaning CUSTOMER. ACCOUNTNO
and SLS.CUSTNO are equal.

When I build an update query, the result is that the CUSTOMER db is
updated with the first record in the SLS file. If the linked db is
updateable, I can't figure out how to write criteria that will
correctly update it.

Thanks
Bill

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Did you try this (the JET [Access] way):

UPDATE CUSTOMER AS C INNER JOIN tblSingleLargeSale AS SLS
ON C.AccountNo = SLS.CustNo
SET C.SALESMAN1 = SLS.EMPLOYEE

Or this (the SQL Standard way):

UPDATE CUSTOMER
SET SALESMAN1 = (SELECT EMPLOYEE FROM tblSingleLargeSale
WHERE CustNo = Customer.AccountNo)


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDWIbIechKqOuFEgEQLi1wCg9DtGnfVdnAlyQL9oU2GONvl3UmYAoKDg
aXtd2pyJVh6E13DbPF6zRsTZ
=6NwK
-----END PGP SIGNATURE-----

 

[Bill Lentz]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Did you try this (the JET [Access] way):

UPDATE CUSTOMER AS C INNER JOIN tblSingleLargeSale AS SLS
ON C.AccountNo = SLS.CustNo
SET C.SALESMAN1 = SLS.EMPLOYEE

Or this (the SQL Standard way):

UPDATE CUSTOMER
SET SALESMAN1 = (SELECT EMPLOYEE FROM tblSingleLargeSale
WHERE CustNo = Customer.AccountNo)

I started from scratch, using the JET in for above and got it to work.

Thanks for the help

Bill