Update table from a different directory and database

[Arvin Villodres]

Good day to everyone!

I have a table (tblInfo) with fields namely Name, Salary.
I need to update a table from a different directory and
different database (N:\MMS\mms.mdb) with table (tblNewInfo)
to be updated. Its fields are NewName, NewRate. Can I do
this by using the update query? How? If not, can you help
me how to do this in another way?

Thanks.

Arvin

 

[Van T. Dinh]

Yes, you can by using the In Clause. For example:

INSERT INTO tblNewInfo (NewName, NewRate)
In 'N:\MMS\mms.mdb'
SELECT [Name], Salary
FROM tblInfo

BTW, 'Name' is a bad choice for Field name. Vitually all
objects in Access have the Property 'Name' and Field
name 'Name' will get confused with Property 'Name'. If
you still want to use 'Name', make sure you use the square
brackets every time you refer to the Field 'Name'.

HTH
Van T. Dinh
MVP (Access)

 

[Arvin Villodres]

I need to update a table from a different database in a
different directory
having "totals" as its tablename and ItemIDGroup,
ItemDescGroup, TotDel for
its field names. The table in the current direcetory that
would update
"totals" is "Trivia" with ItemID, ItemDesc, and
SumOfQtyDelivered for its
fieldnames.

I made this SQL statement:

UPDATE totals
IN 'N:\MMS CENTRAL\IC\ictot.mdb'
INNER JOIN Trivia
ON totals.ItemIDGroup = Trivia.ItemID
SET totals.ItemIDGroup = [ItemID], totals.ItemDescGroup =
[ItemDesc], totals.TotDel = [SumOfQtyDelivered];

But if I try to run it a message "Syntax Error on Update
Statement" would appear.
What did I do wrong?

Thanks.

-----Original Message-----
Yes, you can by using the In Clause. For example:

INSERT INTO tblNewInfo (NewName, NewRate)
In 'N:\MMS\mms.mdb'
SELECT [Name], Salary
FROM tblInfo

BTW, 'Name' is a bad choice for Field name. Vitually all
objects in Access have the Property 'Name' and Field
name 'Name' will get confused with Property 'Name'. If
you still want to use 'Name', make sure you use the square
brackets every time you refer to the Field 'Name'.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Good day to everyone!

I have a table (tblInfo) with fields namely Name, Salary.
I need to update a table from a different directory and
different database (N:\MMS\mms.mdb) with table (tblNewInfo)
to be updated. Its fields are NewName, NewRate. Can I do
this by using the update query? How? If not, can you help
me how to do this in another way?

Thanks.

Arvin
.

.

 

[Van T. Dinh]

Try without the Table qualifier since you can only update ONE Table:

UPDATE totals IN 'N:\MMS CENTRAL\IC\ictot.mdb'
INNER JOIN Trivia
ON totals.ItemIDGroup = Trivia.ItemID
SET ItemIDGroup = [ItemID],
ItemDescGroup = [ItemDesc],
TotDel = [SumOfQtyDelivered];