locked
Access Linked table does not display all SQL Server table columns RRS feed

  • Question

  • Hi

    I have created a linked table in MS Access 2010.  The source is a view in SQL Server 2008 R2.  After linking the SQL Server table I found that certain columns is missing in the destination linked table in MS ACCESS 2010.  Tried several options but was not able to get the columns into MS Access.

    As a workaround I tried creating another view out of the source view in SQL Server.  When I tried to link the new View it's displaying all the columns as required.  Can anyone share their experience if they had faced similar issue?

    I don't want to create a new view but rather I want to use the source view as we don't have access to create these views in Production environment.

    Regards
    Ravi

    Saturday, September 20, 2014 6:20 AM

Answers

  • Thanks all for responding to my post. I worked around the problem by using a PassthroughQuery instead of a linked table. I did a Select * from <MyView> and I was able to retrieve all the columns into the access DB.

    I will try to post the column names and try to find out what the issue was with the linked table.  For now my issue is resolved and thanks again for all the suggestions and inputs.

    Regards
    Ravi

    Tuesday, October 7, 2014 4:22 PM

All replies

  • The columns are probably hidden by accident...

    Open the DatasheetView of the Linked Table and try the "Unhide Fields..." somewhere on the Ribbon and see if you can make the hidden columns visible.

     


    Van Dinh

    Saturday, September 20, 2014 7:55 AM
  • Does the view utilize SELECT * (bad practice) and changes were made to the source tables? Then you need to run sp_refreshview in order for the view to see the changes. Then re-link the view in Access.

    Were changes made to the view? It needs to be re-linked in Access.

    Unlikely, but are you using newer SQL Server datatypes (Date, Time, etc.), that Access may not recognize correctly? Only use data types that Access recognizes correctly.

    Saturday, September 20, 2014 12:59 PM
  • How to go to datasheet view in Access 2010?  Also when I go the design mode the columns are not appearing which means there is very little scope for the columns to be hidden.


    Regards
    Ravi

    Saturday, September 20, 2014 4:02 PM
  • >>How to go to datasheet view in Access 2010?<<

    Double-click on the icon for the Linked Table to open the DatasheetView.  You can also right-click on the icon and then select "Open".

     

    However, if the DesignView of the Linked Table doesn't show these Fields, then the problem is further up the chain.

    1. Have you opened the Datasheet of the View in SSMS and check whether these Fields are included in the SELECT clause of the View? *** EDIT: This is probably not applicable since the (new) Linked Table based on the second SQL Server View (which is, in turns, based on the first/original SQL Server View) does show the relevant Fields.

    2. If you have modified the View recently, have you refreshed the Linked Tables so that Access can modifty the Table Structure according to the modified View?

      


    Van Dinh



    • Edited by Van Dinh Wednesday, September 24, 2014 11:02 PM Second thought re SQL Server Datasheet
    Saturday, September 20, 2014 11:31 PM
  • The view is a default view given by Microsoft (for the product we are using).  Select * from viewname gives all the fields in SQLServer.  Also Select missingfieldname from viewname selects the column in SQL server.  This eliminates any permissioning issue.

    For point no 2. as I said we don't modify the views as these are provided by Microsoft.  I deleted the link table and relinked it still the columns do not appear.

    I will post some screenshots today.

    Sunday, September 21, 2014 12:42 PM
  • Screen shots coming?

    Please post the SQL of the View, the Field names whose columns don't show up in Access and the data-type of these columns.

     


    Van Dinh

    Wednesday, October 1, 2014 11:55 PM
  • Try this:  create a new form and set the recordsource of that form to the table in question.  Then, in design view of this form, look at the fields list of this form and see if the missing fields/columns  from the other form are contained in this list for the new form.  If this new form contains all the columns when you select just the table in question -- then maybe the recordsource of the old form is based on a sql query like

    Record Source  = Select fld1, fld2, fld3, fld4 from tblx


    Rich P

    Friday, October 3, 2014 8:36 PM
  • Thanks all for responding to my post. I worked around the problem by using a PassthroughQuery instead of a linked table. I did a Select * from <MyView> and I was able to retrieve all the columns into the access DB.

    I will try to post the column names and try to find out what the issue was with the linked table.  For now my issue is resolved and thanks again for all the suggestions and inputs.

    Regards
    Ravi

    Tuesday, October 7, 2014 4:22 PM
  • This is not an Access problem. It's how SQL Server works. Whenever you make a change to a view's underlying table you have to refresh the view. The SQL command is really simple. Run it in a new query in your database.

    sp_refreshview ourViewNameGoesHere
    Then relink the view in Access.



    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, October 7, 2014 8:03 PM
  • Hi,

    I found this thread while searching for a solution to the same issue. What worked for me was in access I clicked on the External Database tab, Selected "linked table manager" and then selected the table and clicked ok. This refreshed the table fields to show in access the same way they are in the SQL database.


    Sunday, October 25, 2015 12:04 PM
  • I just wanted to add to this string to point out that I too have linked to a SQL database via a DSN and the tables (as displayed with field lists) are  not showing all the fields. I have tried both MS query (from Excel) and MS Access. In both cases not all the fields are showing.

    However, I find that when I drag and drop all the fields into the Query, within MS Query - Excel, and load the data into Excel and then edit the SQL by right clicking on the data -  Table,External Data Properities, Definition, Command text - and I change the SQL by removing the list of field names with an asterisk, then all the fields show up.

    Equally if I use MS Access to write a query for all fields and the query SQL is Select * from db1 for instance it does not show all the fields but if I then change the same query to a Pass-Through Query and use the same DSN then all the fields show up.

    I have a colleague who used her PC and MS Query to do the same thing AS ME and she sees all the fields.

    I tried re-creating the DSN but this made no difference. All the missing fields have an entry in the DOMAIN_CATALOG field in the Information Schema

    It is annoying. I am still investigating.

    Friday, January 18, 2019 9:16 AM