Linked table update

Hi,
i have linked a sql tablewith access.
Need to update a row in  the sql table with the access table.How do i do this?
New to access so need to know the basic steps as well.
Cheers
RIASAsked:
Who is Participating?
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.

RiteshShahCommented:
well suppose your Linked Server of Access name is "acc"

update tfs set field1=tfa.Field1 from
tableFromSQL tfs join acc.tableFromAccess tfA
on tfs.someCommonField=tfA.somecommonfield


0
RiteshShahCommented:
to know more about linked server to access, have a look at my article at

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

0
RIASAuthor Commented:
Cheers mate will try and get back
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

RIASAuthor Commented:
I did something like this and got an syntax error
update TESTINGDOCIMG  
set FileType = Castle_Imaging_Analysis.Filetype  from
TESTINGDOCIMG  join Castle_Imaging_Analysis
on TESTINGDOCIMG.IndexMember=Castle_Imaging_Analysis.DocID
0
RiteshShahCommented:

update t  
set t.FileType = cia.Filetype  from
TESTINGDOCIMG t join [YourAccessLinkedServerName]...Castle_Imaging_Analysis cia
on t.IndexMember=cia.DocID

--NOTE:
--TESTINGDOCIMG t join Castle_Imaging_Analysis cia

--I guess TESTINGDOCIMG is your SQL server table
--Castle_Imaging_Analysis is your access table, if that is true than you have to use
--[YourAccessLinkedServerName]...Castle_Imaging_Analysis


0
RIASAuthor Commented:
Can you guide me on how to find YourAccessLinkedServerName?
0
RIASAuthor Commented:
Sorry for asking basic question but new to access.Cheers mate
0
RiteshShahCommented:
If you have read my article given above, you got to know. anyway, here is the query to find it.

select * from sys.servers
0
RIASAuthor Commented:
Hi,
I am linking sql table with access.
Using access at the moment to update sql table and when i execute
select * from sys.servers
message I get is sys.mdb not found

Cheers
0
RiteshShahCommented:
have your made linked server of Access in SQL Server? if yes, than that error shouldn't come. I can make query for you if you post result of select * from sys.servers after running it in SQL Server
0
RIASAuthor Commented:
Cheers mate will do accordingly..and get back
0
RIASAuthor Commented:
Hi,
Tried this
exec sp_addlinkedserver

@server='LinkToAceess',

@srvproduct='AccessDatabase',

--if you have older version of access then kindly use old jet provider

--Microsoft.Jet.OLEDB.4.0

@provider='Microsoft.ACE.OLEDB.12.0',

@datasrc='J:Testing.mdb'

GO

but got an error:
OLE DB error trace [Non-interface error:  Provider not registered.].
Msg 7403, Level 16, State 1, Line 1
Could not locate registry entry for OLE DB provider 'Microsoft.ACE.OLEDB.4.0'.


0
RiteshShahCommented:
there is no provider like "Microsoft.ACE.OLEDB.4.0"

if you are using access version less than 2007 than use Microsoft.Jet.OLEDB.4.0
if you have access 2007 than use Microsoft.ACE.OLEDB.12.0
0
RIASAuthor Commented:
Hi,
Tried to execute the query again got this error:
Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 79
The server 'LinkToAceess' already exists.
Cheers
0
RiteshShahCommented:
because you have tried first time and created linked server but wasn't completed, even you can find one instance, before you run second time, you have to remove first instance.  see screen shot about how to delete previous linked server.
delete.JPG
0
RIASAuthor Commented:
Cheers for the solution but when i made the changes as you suggested to create another link i got this error:
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d:  Authentication failed.].
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
0
RIASAuthor Commented:
Hi,
Could execute the above query but when tried to view the table
I got this
[OLE/DB provider returned message: 'J:\testing.mdb' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:   ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
0
RiteshShahCommented:
either you access file is open somewhere or corrupt.
0
RIASAuthor Commented:
Hi,
Its on the network so possibility of it been corrupt is vey less and open not  sure what you mean by that ,my access is currently closed .I am getting it wrong somewhere?
0
RiteshShahCommented:
there are few possiblitity,

--your SQL Server service account doesn't have rights to read that access file
--your access file is corrupt
--since it is in network, somebody might have open it and that is why you are getting an error.
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
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.