Linked SQL Server Table Missing Columns (1 Viewer)

[Dopeyjh]

Hi,
Hoping someone can help me out.
Within my access database I have a linked table to a SQL Server. I have setup the ODBC connection and the table link works no problem, except is missing random columns. I've also tried other table from the SQL server and they are also missing columns. They are not hidden and they are not restricted data type fields (hyperlink, /etc) The linked tables are well under the 255 max, some only have 10 columns.

Now, If I create a pass through query within access to the same server and table I can then see all the columns.

I can see all of the columns using the same connection string is SSMS and PowerBI and even in Excel.

Does anyone have any idea / suggestions why some of the columns are not visible ?

Appreciate any help
Thanks

 

[Pat Hartman]

Have you checked the data types of the missing columns? I have never seen this behavior but then I get to control the data types of the Server-side tables.

 

[Dopeyjh]

Hi Pat,
Thanks for responding. When I look at the missing columns in SSMS, they all appear to not have a set data type, but a system type of varchar

 

[GPGeorge]

Hi Pat,
Thanks for responding. When I look at the missing columns in SSMS, they all appear to not have a set data type, but a system type of varchar

varchar is the generic datatype for "characters"; what would be Short Text or Long Text in Access. However, they'll also include a second designation indicating how many characters are allowed. You should see something like "VarChar(100)" for example, indicating a text field for up to 100 characters.

What is the designation in the fields you see?

 

[Dopeyjh]

In my view on the SQL Server the datatype for the missing columns is blank...
I think i figured out the problem, it boils down to a permission issue. Ive since learned that the data type on the SQL Server is a user defined datatype that i currently do not have access too. All the fields that are missing are setup with this user-defined datatype. I am assuming and hoping that once I have access to the datatype, then this will no longer be an issue.

 

[Pat Hartman]

I am assuming and hoping that once I have access to the datatype, then this will no longer be an issue.

Not likely. How will Access know what the data type is if it is user defined? Access will either cast the column as text or leave it invisible. Including the column in your select clause may render the query not updeatable since parts of the row will not be updateable.

 

[gemma-the-husky]

I've never had a problem with varchar fields. I don't know what the technical difference is. I assume it's something like SQL server not reserving initial space for the full potential size of a varchar string, which is the way access manage strings, as we discussed recently. Maybe it's something else.

Can SQL server deny users access to certain datatypes? Can't that setting just be changed, if that's the issue?

 

[Pat Hartman]

The point of a varchar is that the field is variable in length and therefore the db engine does NOT reserve the full space. It uses only as much space as is needed for the current value

 

[GPGeorge]

In my view on the SQL Server the datatype for the missing columns is blank...
I think i figured out the problem, it boils down to a permission issue. Ive since learned that the data type on the SQL Server is a user defined datatype that i currently do not have access too. All the fields that are missing are setup with this user-defined datatype. I am assuming and hoping that once I have access to the datatype, then this will no longer be an issue.

Is it perhaps a computed (calculated) field in the SQL Server table? Or is it really a user-defined datatype, which seems to me to be exceedingly unusual, albeit possible. My money is on a computed column.