Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

 

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

 

End Sub

Code Samples for Businesses, Schools & Personal Use

Updated 25/11/2017  

 

The information below is based on an article by Juan Soto at https://accessexperts.com/blog/2010/03/03/using-sql-server-views-with-access-index-needed-for-editing-data-2/

   

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, run the following procedure in your code to create the index:

 

Code:

Create Index on SQL View

Example usage:

 

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

Return to Code Samples Page