Option Link Server in Link Table Wizard in Ms Access 2007 is greyed out when connecting to SQL 2005 database

Question

Dear all,

Recently I have adp file in Access 2007 that links to several tables in a SQL server 2005 database. I tested on my desktop (Windows XP pro)  & it works OK, can manipulate any tables in that database. But the problem occurs when I copy this adp file to another user in my company. She cannot see any linked table. Going to that users desktop, opening the adp file, I realized I can see other objects except linked tables.

Going to Server > Link Tables to open the Link Table Wizard, I see the option Linked Server is greyed out & the only option I can choose is Transact SQL.

Are ther any things related to user rights on the SQL server 2005 or something wrong in ODBC?

Please help!

Thanks so much,

Eward

Answer 1

Hi Eward,

For this problem, there are several things should be noticed:

1. You have moved the adp file to a new machine, the current user may be changed. It will cause the ODBC connection failed.
2. On the new machine, can you connect to SQL Server by using SQL Server management studio? Please check the net traffic between the new server and SQL server.

According to your description, I also recommend you to post this question to Access forum, some details of moving ADP files could be achieved in that forum. Here is the address:

http://social.msdn.microsoft.com/Forums/en-US/accessdev/threads

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Iric,

Thanks for your replay. I'd appreciate about that. I just read your reply after I did a small test on the user's desktop that I copied the adp file into. I logged into her desktop with my credentials & opened the same adp file, every thing  works fine. I can see all linked tables (from SQL server-based database) & manipulate it without any issue. I also tried to log into that desktop with another user with the same settings on that database & it works fine. That proves that something wrong in the user's setting in the SQL server side(security or permission or something like that), but I couldn't figure out what they are & where. I guess maybe  something related to the permission or security in the server where the SQL server was installed; because I did compare the security & permission between the other user (who works fine with his credentials) and her: they are the same permission. I'm really confused now & I hope I can find something I miss or do incorrectly.

I can connect to the SQL Server without any problem (by remote) & open SQL Server management studio with my credentials, but I haven't tried with the user's credential. I'll try & post the result.

Hi Iric, is it OK if I post my issue in another group reccommended by you?

Answer 3

Finally, my issue was solved by adding one more database role: db owner for the database that the adp file links to.

Thanks