Update table with values from another table

[Jan Nielsen]

I have a table in MS Access and I need to copy it's rows to a SQL server
table and following regularly run a query that updates changed information.
Changes are still performed in Access. I must update from Access to SQL.

First of all I have added a linked SQL table. This is ok.
To initially copy rows from Access to SQL I used an INSERT - SELECT
statement. This is ok too.
SQL:
INSERT INTO dbo_ServerCopy ( ForeignID, ServerName, Status, TypeID,
CreatorID, HwBrand, HwModel, HwSerial )
SELECT ID, ServerName, Status, TypeID, 3, HwBrand, HwModel, HwSerial
FROM ServerListExport
WHERE ID not in (SELECT ForeignID FROM dbo_ServerCopy WHERE CreatorID = 3);


Now I'm trying to make an UPDATE statement that can copy changed values to
SQL, but this is causing me problems.
The SQL I'm trying to use, seems not to work on Access.

First I tried an UPDATE - FROM statement, but it seems Access does not
recognize it.
Next I tried using subqueries, like this:
UPDATE dbo_ServerCopy AS A
SET A.ServerName = (SELECT ServerListExport.ServerName FROM ServerListExport
WHERE ServerListExport.ID = A.ForeignID)
WHERE ForeignID in (SELECT B.ID FROM ServerListExport B, dbo_ServerCopy C
WHERE C.ForeignID = B.ID and C.CreatorID = 3 and (C.ServerName <>
B.ServerName));

When trying to execute this Access just replies: Operation must use an
updateable query.
The problem seems to be the first subquery that returns a value to SET
ServerName. If I replace this with a static string it runs, but the result is
useless.


Googling to find a solution I noticed some examples like this:
UPDATE dbo_ServerCopy AS A INNER JOIN ServerListExport AS B ON
A.ForeignID=B.ID
SET A.ServerName = B.ServerName;

However it results in the same error message. Operation must use an
updateable query.
If I replace B.ServerName with a static string it runs. But again useless.


I have no more ideas of how to solve this.
Anyone knows if this kind of copying is possible with Access at all?
Obviously I can create some code (non-SQL) to iterate through both tables and
copy the information, but it's really not desirable as it should be possible
with simple SQL.

ServerListExport is a query (view) in Access that gathers columns from
multiple tables, and excludes columns as well. I can not just move
ServerListExport to SQL and use it there.

Any Access experts have an idea of how to do this?


TIA,
Jan

 

[vanderghast]

I suspect ServerListExport is not updateable, even if you don't try to
update it, using a not-updateable (even if only reading from it) marks the
query not updateable.


Instead of

UPDATE dbo_ServerCopy AS A
SET A.ServerName = (SELECT ServerListExport.ServerName
FROM ServerListExport
WHERE ServerListExport.ID = A.ForeignID)
WHERE ForeignID in (SELECT B.ID
FROM ServerListExport B, dbo_ServerCopy C
WHERE C.ForeignID = B.ID
and C.CreatorID = 3
and (C.ServerName <> B.ServerName));

try

UPDATE dbo_ServerCopy AS A
SET A.ServerName = DLOOKUP("ServerName" ,
"ServerListExport",
"ID =" & A.ForeignID)
WHERE ForeignID in (SELECT B.ID
FROM ServerListExport B, dbo_ServerCopy C
WHERE C.ForeignID = B.ID
and C.CreatorID = 3
and (C.ServerName <> B.ServerName));



since using a not updateable expression in the where clause is generally not
important (for updateability), you are NOT obliged, I think, to replace the
subquery there too.



Vanderghast, Access MVP

 

[Jan Nielsen]

It's working. Thanks!


Jan