MS Access missing fields on a linked table

[David Koeblitz]

I don't see that anyone is having this issue however I can recreate it across multiple databases and multiple computers.  I'm using O365 with Sql 2019.  I have a linked table (customer orders) using a sql account.  The sql account is using the role db_dataread. When I open the table in access the decimal fields are missing (price for example).  Using the same sql account in SSMS the fields show.  Changing to a windows account with db_datareader does not fix the issue.  

[#2]

@David Koeblitz I am having the same issue. I have been looking everywhere and have found no solution.

[#3]

@David Koeblitz 

One of the difficulties of trouble-shooting problems like this is that there is no way for us to see the problem first-hand. That said, we can ask some clarifying questions that might help lead to insights.

Did you recently install any updates to your Windows or Office? If so, when?

Did this problem appear suddenly, i.e did it "work" in the past but no longer?

Did you change any settings in your Access 365 application? 

When you say the fields are missing, does that mean there are no decimal fields at all, or that values in decimal fields are missing?

Have you relinked the tables (I assume yes, because you changed the UserID, but to be thorough....)

Is this an on-premises instance of SQL Server, or a hosted instance? 

Have you tried repairing your installation of Office 365, or reinstalling it?

Can you create a new accdb and link to this table from it? And does that also leave out the same fields?

Trouble-shooting is frequently a process of eliminating, step-by-step, one at a time, possible factors that impact, or could impact a relational database application. Unfortunately, errors of this category tend to be the hardest to isolate because they don't come from commonly experienced problems and tend to be related to the environment where they occur only.

[#4]

@David Koeblitz That is not my experience, using A365-32 v2102 Current Channel, Microsoft SQL Server 2019 (RTM-CU9), ODBC Driver 17 for SQL Server 2017.175.02.01

The Decimal field is still there. What is different about your situation?

[#5]

 

here is a side by side.  The only difference is on the left the sql account is a db_owner and on the right the sql account is a db_datareader.  From a security standpoint, I would like them to be read only.  You will also see 2 numeric fields do show.

[#6]

I tried to recreate your scenario. Created SQL user with db_readonly. Dropped and recreated the linked table. The decimal field is still there. I confirmed it is indeed readonly: could not edit an existing row.
Not sure where to go next. Maybe you can drop the linked table. Restart SQL Server (so the caches are empty), and recreate the linked table while running SQL Server Profiler. Anything interesting being recorded?

Also: please use precise language. There are not two "numeric" fields, but two Number fields of subtype ???. IOW, are there any decimals showing on the left?

[#7]

When I run Profiler on my machine when linking to the table, one of the calls is:
exec [sys].sp_columns_100 N'TomTest',N'dbo',NULL,NULL,@fUsePattern=1
Running this in SSMS, I get this, plus several more columns:
WideWorldImporters dbo TomTest ID 4 int identity
WideWorldImporters dbo TomTest myInt 4 int
WideWorldImporters dbo TomTest myDateTime2 11 datetime2
WideWorldImporters dbo TomTest mySingle 7 real
WideWorldImporters dbo TomTest myVarchar_255 12 varchar
WideWorldImporters dbo TomTest myNVarchar_255 -9 nvarchar
WideWorldImporters dbo TomTest myDecimal_18_3 3 decimal
WideWorldImporters dbo TomTest myBit -7 bit
WideWorldImporters dbo TomTest ts -2 timestamp

On your system, does such call omit the decimal columns? That would make me fall off my chair.

[#8]

I am having the same issue. Able to replicate on multiple versions if access and using different ODBC drivers. This issue doesn't affect all decimals the same. Datetime fields aren't affected and import down to the second. Decimals (1-2 decimal places) come in as whole numbers, not sure if this is the same for higher decimal places but have seen truncating in other posts. Money fields don't even show up as a field in the linked table or when selecting a primary key. SQL that I am accessing is a view that I normally access and can view all fields in SSMS 18. This is across all tables with a money field. Is there any solution yet?