First Published 20 Oct 2021                    Last Updated 30 Jan 2022


The information below is based on an article by Juan Soto : Using SQL Server Views with Access: Index needed for editing data

SQL Server views are one of the best tools a Microsoft Access developer 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 . . .

NOTE:
You can create indexed views in SQL Server, this article applies to non-indexed views.

After you have linked your SQL view to your Access application, run the following procedure in your code to create the index:

Code:

Public Sub CreateIndexonView(strIndexName As String, strViewName As String, strFields As String)

Dim strSQL As String
strSQL = "Create Index " & strIndexName & " On " & strViewName & "(" & strFields & ")"
CurrentDb.Execute strSQL, dbFailOnError

End Sub

Example usage:

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



Feedback

Please use the E-Mail button in the contact form below to let me know whether you found this article useful or if you have any questions.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 30 Jan 2022

Return to Code Samples Page

Return to Top