SQL Server 2008: why do I see only system tables when I login ODBC from ACCESS

Question

I am using Access to try to get some links to tables in SQL Server 2008.

I've created a user name under security in SSMS for SQL Server and I've mapped it to a user that is under the database that I need. I've also given that user a default database that I need.

When I try to connect through ODBC I am not seeing the correct tables. What I am seeing is a bunch of Sys tables. Does anyone know what I am doing wrong?

Here is the create for the main user:

/* For security reasons the login is created disabled and with a random password. */
/****** Object:  Login [lomuser]    Script Date: 10/22/2010 08:14:03 ******/
CREATE LOGIN [lomuser] WITH PASSWORD=N'µ''ØÑëOº\¾dõMÐàæfÄ%[RríÜ2 y', DEFAULT_DATABASE=[LOMDATABASE], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

ALTER LOGIN [lomuser] DISABLE
GO

Here is the create for the specific user to the specific database (on which the top user is mapped to) also FYI they have the same name:

USE [LOMDATABASE]
GO

CREATE USER [lomuser] FOR LOGIN [lomuser] WITH DEFAULT_SCHEMA=[dbo]
GO

Answer 1

When you setup the DSN connection in the odbc panel or let access create this connection for you, the default database is master. So, when you link a table, the only tables you will see are the system tables. Delete your linked table (if you have any). Now, when you choose to link to a table, choose NEW in the data source to create a new DSN connection. During that process, you see the option to change the default database from system to whatever database you want.

Once you change the above default, then when you link a table, you see tables from the above selected database you choose. If you did not set above, as noted, you just going to see the system tables.