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)

ADVERTISING
MS Access Report Generator

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
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
Here’s how you would use it in your code:

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

Thanks!

Juan