Linked server with MS access mdb database - Error 7416?

  • Hi

    MS ACCESS mdb database located to another server.

    SQL 2K8R2 - database another server.

    I have created & working fine linked at SQL 2K8R2 with MS ACCESS by using windows administator user in SQL box.

    when try to connect and access SQL user does not working linked server and throwing error 7416.

    It seems like remote server login access denied in SQL box, so how can assign remote sever login and password at SQL box. because mdb file don't have user and login.

    Thanks

    ananda

  • 1. User must have connection rights (login) to the SQL Server or be member of an A.D. group that has connection rights to the SQL Server.

    2. User must have the necessary access rights to the concerned database or be member of an A.D. group that has access rights to the concerned database.

    3. As the Access database is a Front-End (i.e. it does not contain the data tables), it should be copied on each client machine (never share a F.E.).

    4. If a connection string is used (i.e. no attached tables or dynamic attached tables), include "Trusted_Connection=Yes" in the connection string (e.g.: "ODBC;DRIVER={SQL Server};SERVER=Iskender;DATABASE=PictureManagerSQL;TRUSTED_CONNECTION;Yes;").

    5. If the F.E. uses attached tables through a DNS (ODBC defined connection), use an account that fullfill 1 and 2 here above when defining this ODBC connection (it can be a system-defined DNS connection or a user-defined DNS connection).

    Have a nice day!

  • Hi

    Never used access, but had problem with Excel file on another machine. I had to set the user in the linked server properties to connect to the XLS as "admin".

  • This is because Excel uses MSQuery which needs to access to several system objects.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply