1

I have a SQL Server view set up, which performs correctly when opened inside SQL Server. It's got groupings and pivoting in the view. It's essentially a matrix of 1's and 0's with some unique identifiers.

However, when I try to open the view from Access using the below steps:

Access > External Data > Link to the data source... > Machine Data Source > Selecting the DSN > Selecting the View to be linked > Select Unique Record Identifier.

...I get the message: ODBC--call failed.

If I then try and open it, I get the expected amount of rows produced, but each field is populated with #Name?

Any clues to what might be causing this? The ODBC should be fine, as I can open up all other tables and views from the same database - just not this particular one.

Also, if I push the results of the view into a table, and try and open the table via Access, I still get call failed.

glass_kites
  • 401
  • 4
  • 18
  • If you have Bit / Boolean columns in the SQL Server tables, check out https://stackoverflow.com/questions/4764508/nullable-bool-fields-in-ms-access-linked-tables – Andre Jun 22 '17 at 13:20

1 Answers1

2

It looks as if there's a character limit on the column title. 3x of my columns had quite long titles - after reducing these I was able to resolve.

glass_kites
  • 401
  • 4
  • 18
  • Interesting. How long were these column names (how many characters)? – Andre Jun 22 '17 at 14:37
  • 65 characters for the column name. But perhaps because it's using a linked table, that everything is fully qualified 'behind the scenes' and exceeding a limit? i.e. servername.dbo.dbname.tablename.65charactercolumnname – glass_kites Jun 22 '17 at 14:44