Go to Top

Using SQL Server Views with Access: Index needed for editing data.

SQL Server views are one of the best tools a Microsoft Access programmer can use to limit exposure to data and improve Access performance.

Unfortunately, you can’t modify data in a view unless it has a unique index, read on if that is the case for you. (You can create indexed views in SQL Server, this article applies to non-indexed views)

After you have linked your view to your Access application, create a new query or execute the following SQL statement in your code to create the index:

Create Index ix_name On view_name(fields) With Primary

ix_name: The name you assign to your primary index.
view_name: The name of your view in Access.
fields: One or more fields seperated by commas that will compose your primary key.

That’s all there is to it. We hope you can “Discover the Power of Your Data!” with this tip.

UPDATE: Code Example

Here’s how you would use it in your code:

CreateIndexonView “IDX_OrderID”, “vw_CustomerExpiredOrders”, “OrderID”

Thanks!

Juan

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

7 Responses to "Using SQL Server Views with Access: Index needed for editing data."

  • 10 things every Access developer who works with SQL Server should know | accessexperts.com
    May 14, 2013 - 9:38 pm Reply

    […] is to make sure the view has an index. There are two ways about it: You can add the index using Access or create the index in SQL Server using the CREATE VIEW SQL syntax. You can’t create an index […]

  • Ralf
    January 16, 2012 - 5:52 pm Reply

    Is it possible to make the result from a pass-through query editable (edit, new, delete) from within Access i.e. in the same way as I can edit linked tables?
    Best regards
    Ralf

    • Ralf
      January 30, 2012 - 3:05 pm Reply

      No answers but I now understand that pass-through queries are read-only.
      Another question: How do I “populate” a @variable in a pass-through query from an Access Form or a VBA variable?

      Best regards
      Ralf

  • Ralf
    January 8, 2012 - 6:23 pm Reply

    Where should I place the code?

    “Create Index ix_name On view_name(fields) With Primary”

    In the actual pass-through query or a separate query or in VBA?

    • Juan Soto
      January 9, 2012 - 3:30 am Reply

      Ralf,

      The line would go into your code.

      Thanks for visiting our blog!
      Juan

      • Ralf
        January 9, 2012 - 4:17 pm Reply

        Thanks a lot! It works fine. It seems that querying a SQL server view is faster than querying the table? I am new to SQL Server, is there an advantage to index the view in SQL Server?

        /Ralf

        • Juan Soto
          January 11, 2012 - 8:20 pm Reply

          Ralf,

          Usually there is no benefit, only if you intend to edit the data in Access.

          Juan

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.