Update a SQL table from an Access table?

1 Dec 2005 5:50 PM

Jason

I've got data that was imported from an Access database into a SQL database.
I've found the import didn't populate one field it should have, so would
like to now update that field by linking the two DB's and running an update
statement. Can someone help point me in the right direction? Thanks!

Andy

1 Dec 2005 6:09 PM

ML

Look up sp_addlinkedserver in Books Online to access the MDB from the SQL
Server.

Or better yet, investigate why the values were not imported as expected.

ML

---
http://milambda.blogspot.com/

1 Dec 2005 6:31 PM

John Bell

Hi

Check out http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_6a44.asp
for creating a linked server. You should then be able to update the column
with a query such as:

UPDATE t
SET col1 = s.col1
FROM uploadedtable t
JOIN linkedsvr...[Accesstable] s ON t.pkcol = s.pkcol

If not you can re-import the data into a staging table and then use that to
create a similar query to the above.

John

Show quote

"Jason" wrote:

> I've got data that was imported from an Access database into a SQL database.
> I've found the import didn't populate one field it should have, so would
> like to now update that field by linking the two DB's and running an update
> statement. Can someone help point me in the right direction? Thanks!
>
> Andy
>
>
>