Link to home
Avatar of seacoastmedical
seacoastmedical

asked on

Access 2007, cannot see link tables in SQL Server 2008

I have several databases setup in SQLServer 2008. When I use Access 2007 to link to these tables from ODBC (External Data, >More > ODBC Database >Link to the data source by creating a linked table) and choose my ODBC connection (named SQL2) I see tables from 1 active database available to pick from in the Link table windows but do not see the tables I need from another Database on the SQLServer.
I have checked rights on both databases and they are the same.
What am I missing that these tables in a different database do not show up?
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Can you see the tables in that same database when using SSMS?
Hi
 Maybe user that you used for connect in ODBC can not access to tables that you want.
Avatar of seacoastmedical
seacoastmedical

ASKER

I can use SSMS Tools to query data in this database from SQL Management studio. Is that what you are asking?
I was asking if you can connect to that other database with the same user using SSMS?
I can see other databases with ssms in SQL Management studio.
If I change the ODBC settings and set the the default database to the database I need I can then view these tables from Access (by going to External Data >More > ODBC Database > Link to the data source by creating a linked table.)

Any thought why I can't view these when the default database isn't set?
I am new to SQL server, is this normal to only be able to view tables from one database?
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
I can connect with SSMS (assuming you mean Sql Server management studio) and see these databases.
When I set the ODBC Sql connection to use the default database of the database I want tables from, I can then see the tables from Access.
The problem I have is that from Access, I can't see tables from other databases at the same time. Only 1 Database on my SQL server.
Possibly this is a limitation of the ODBC connection.
I found that I had to create a seperate ODBC System DSN for each database on my SQL server and THEN set that ODBC connection to the default database I wanted to connect to. Otherwise it was just pulling in tables from a random database.