July 5, 2004 at 10:02 pm
Hello friends,
I have a problem. I want to update data from Access DB table to SQL DB table. I used OPENROWSET command but it wasn't worked properly and I don't want to use 'Link Server'. If you have any idea, please help me. Thanks a lot!
July 5, 2004 at 11:19 pm
July 6, 2004 at 1:21 am
Thanks for your interest. I have a table 'Table1' in Access Database and I also have a tabel 'Table2' in SQL Database. In Table2, there are two fields 'ID' and 'Status' . I want to update that Status field which Table2.ID matches with in Table1.ID. How could I write the sql script? Please help me again.
July 6, 2004 at 5:03 am
To do this, you need to JOIN Table1 and Table2 like this:
UPDATE a
SET a.Status = b.Status
FROM Table2 a
JOIN Table1 b
ON a.[ID] = B.[ID]
I don't know of a way of joining tables unless you use linked servers. You could DTS Table1 across to SQL Server, then do the update and then drop Table1
July 6, 2004 at 6:46 am
Openrowset should work, like this
UPDATE t2
SET t2.Status = t1.Status
FROM Table2 t2
INNER JOIN OPENROWSET('MSDASQL',
'Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\mydb.mdb;Uid=admin;Pwd=',
'select * from Table1') t1
ON t1.ID = t2.ID
Is the mdb on the server?
Is the mdb password protected?
Far away is close at hand in the images of elsewhere.
Anon.
July 6, 2004 at 9:36 pm
Hello David Burrows,
I'm sorry, my mdb and SQL are not in the same server. SQL is in Remote server and mdb is in local machine. Do you have any idea??? Thanks.
July 7, 2004 at 4:09 am
Create a share on the mdb server for the folder the mdb is in.
Grant 'Everyone' read access on the folder, or preferably give the login that SQL is using use access to the share. To see the login that SQL is using, go into EM, right click on the server name, properties, server settings. The login listed there needs read access on the share containing the mdb file.
Connect to the SQL remote server using QA and run David's query from there. First change 'Dbq=c:\mydb.mdb' to the share you have just created.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply