Access ODBC can't pull from SQL table with more than 255 columns

Question

I have a database that I'm trying to query from Access with ODBC. The table I need to read from has 304 columns, and I can only see the first 255 in the Query Builder. I've read elsewhere that the solution is to write an SQL query by hand rather than relying on the Builder, so I tried this query:

SELECT [Field1], [Field304] 
FROM [ODBC;DRIVER=SQL Server;UID=USERNAME;SERVER=ServerAddress].[TabelName];

This query returns Field1 just as I'd expect, but still won't get Field304. What am I doing wrong?

Answer 1

You have encountered a limitation of ODBC linked tables in Access, and a query like

SELECT ... FROM [ODBC;...].[tableName];

is really just a way of creating a temporary ODBC linked table "on the fly".

When Access goes to create an ODBC linked table it queries the remote database to get the column information. The structures for holding table information in Access are limited to 255 columns, so only the first 255 columns of the remote table are available. For example, for the SQL Server table

CREATE TABLE manyColumns (
id int identity(1,1) primary key,
intCol002 int,
intCol003 int,
intCol004 int,
...
intCol255 int,
intCol256 int,
intCol257 int)

an Access query like

SELECT [id], [intCol002], [intCol255] 
FROM [ODBC;DRIVER={SQL Server};SERVER=.\SQLEXPRESS;DATABASE=myDb].[manyColumns];

will work, but this query

SELECT [id], [intCol002], [intCol256] 
FROM [ODBC;DRIVER={SQL Server};SERVER=.\SQLEXPRESS;DATABASE=myDb].[manyColumns];

will prompt for the "parameter" [intCol256] because Access does not know that such a column exists in the SQL Server table.

There are two ways to work around this issue:

(1) If you only need to read the information in Access you can create an Access pass-through query

SELECT [id], [intCol002], [intCol256] 
FROM [manyColumns];

That will return the desired columns, but pass-through queries always produce recordsets that are not updateable.

(2) If you need an updateable recordset then you'll need to create a View on the SQL Server

CREATE VIEW selectedColumns AS
SELECT [id], [intCol002], [intCol256] 
FROM [manyColumns];

and then create an ODBC linked table in Access that points to the View. When creating the ODBC linked table remember to tell Access what the primary key column(s) are, otherwise the linked table will not be updateable.