Sql-server – only see SYS and INFORMATION_SCHEMA tables? Access 2007 ODB to SQL Server 2008

ms accessodbcsql server

I was finally able to create my ODBC connection but now when I go to External Data > ODBC > LINK TABLES using my ODBC connection in Access 2007 I get a huge list of tables such as

INFORMATION_SCHEMA.CHECK_CONSTRAINTS

INFORMATION_SCHEMA.COLUMNS

INFORMATION_SCHEMA.DOMAINS

etc…..

and a whole bunch of

sys.all_columns

sys.all_objects

sys.all_parameters

etc…

the only tables that i really need are tables such as

dbo.users

dbo.stats

dbo.customers

etc…

but none of those tables are on the list of table that I am able to choose from to link.

Does anyone know why I can't see or link to the DBO tables I need?

(by the way i created a new user login in order to create my ODBC connection)

Best Answer

Thanks to Jimbo and Shark's comments I was able to fix my problem by adjusting the permission for the tables I wanted to see. I did not have SELECT permission on the tables so I was not able to see them. Once I added the SELECT permission to the tables I was then able to see my data perfectly.

Related Solutions

Sql-server – Remove nulls from TSQL connection string

SQL Server always returns NULLs in this case. I know of no way to globally (through server settings or connection strings) cause it to return empty strings or zeroes instead of NULLs. And I do not think it is a database library issue--using different drivers won't change a thing. This is just the way SQL Server behaves.

If you want to convert NULLs to something else, you'll need to explicitly use the IsNull(Expression, ValueIfNull) function or the similar Coalesce() function which takes multiple parameters and returns the first non-null value.

SELECT O.OrderID, IsNull(CustomerName, '') CustomerName
FROM
   dbo.OrderTable O
   LEFT JOIN dbo.CustomerTable C
      ON O.OrderCustomerID = C.CustomerID

Though one comment... why would there be an order with no customer? Wouldn't a customer with no order be more likely?

Update

Can you put in a wrapper that, client side, converts the NULLs to what you want? What process or program are you using to convert the result sets to text?

Ms-access – Access 2007 sql editor

There are many tools and ways better ways to do SQL editing for Access 2007 other then using msword , at least i would use Notepad++

below are tools i used and are good

here is a 2 post1 and post2 in Stackoverflow similar to your issue

but if you want my opinion to this problem, the best thing i suggest is to use SSMS and add a linked server to your Access file, i like SSMS

Best Answer

Related Solutions

Sql-server – Remove nulls from TSQL connection string

Ms-access – Access 2007 sql editor

Related Question