Forum Discussion
MS Access missing fields on a linked table
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 ...
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.
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?
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?
- 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.- 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?
