Updating a Table in Access 2010

I have been tring to update a table in Access 2010 from another table. Table A has a list of all our PCs on the network. TableA is missing information that TableB has, specifically  LastLogonID, LastLogonDomain, and IPAddresses.

So here is the Code I came up with:
UPDATE TableA[All] AS A
INNER JOIN TableB AS B ON A.SystemName = B.SystemName
SET A.LastLogonID = B.LastLogonID, A.LastLogonDomain = B.LastLogonDomain, A.IPAddresses = B.IPAddresses;

Open in new window


It runs but nothing happens, can any help me out here. Am I trying to change to many fields?
EverwulfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this


UPDATE TableA AS A
INNER JOIN TableB AS B ON A.SystemName = B.SystemName
SET A.LastLogonID = B.LastLogonID, A.LastLogonDomain = B.LastLogonDomain, A.IPAddresses = B.IPAddresses;
0
EverwulfAuthor Commented:
Sorry What you have is what i am using not sure where the [All] came from.
0
Rey Obrero (Capricorn1)Commented:
your update query will only update records where A.SystemName = B.SystemName

if you want to add records from to table A from table B
use an append query

insert into TableA
select TableB.* from TableB
where TableB.SystemsName not in (select SystemsName From tableA)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Eric FlammOwnerCommented:
Try
Update TableA
inner join TableB B on TableA.SystemName=B.SystemName
Set LastLoginID=B.LastLoginID, LastLogonDomain=B.LastLogonDomain,IPAddresses=B.IPAddresses

Open in new window

I think the Alias (A) is valid in the join (although I removed it for clarity), but not in the Update command.
0
EverwulfAuthor Commented:
I think I found my issue. Thanks to Capricorn1. For some reason out of the 185 records in TableB it only finds the first record as a match to TableA. Not exactly sure why it started doing that.

Now I have to figure out why it doesn't see the rest.
0
HndlWCareCommented:
Make sure both tables have all of the SystemName, it won't update if it can't match a similiar entry.

You should try to avoid using JOINs with Access also, a workaround would be :

UPDATE TableA AS A, TableB AS B
SET A.LastLogonID = B.LastLogonID, A.LastLogonDomain = B.LastLogonDomain, A.IPAddresses = B.IPAddresses
WHERE A.SystemName = B.SystemName;

Look closely at the WHERE clause, both tables NEEDS all of those SystemName
0
Rey Obrero (Capricorn1)Commented:
<You should try to avoid using JOINs with Access also, a workaround would be :>

and why is that?
0
EverwulfAuthor Commented:
Ok I got it figured out. Of the list in TableB only one machine is actually listed. Which means two things our inventory is out of date and I need to do more investigating before waisting everyones time.
0
EverwulfAuthor Commented:
Not all the same data was in the column I was comparing. Would not have stopped to think about that had Cap not stated that:

"your update query will only update records where A.SystemName = B.SystemName"

Thanks again Cap
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.