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