Forum Discussion

David Koeblitz's avatar
David Koeblitz
Copper Contributor
Mar 29, 2021

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 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.  

access
  • Tom_van_Stiphout's avatar
    Tom_van_Stiphout
    Steel Contributor
    Apr 02, 2021

    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?

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Apr 01, 2021

    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.

    • David Koeblitz's avatar
      David Koeblitz
      Copper Contributor
      Apr 02, 2021

       

      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.

       

       

      • Tom_van_Stiphout's avatar
        Tom_van_Stiphout
        Steel Contributor
        Apr 02, 2021
        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?

Resources