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 an 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
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
8 years later and this post just saved someone’s life. Thanks for posting this!
[…] 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 […]
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
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
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?
Ralf,
The line would go into your code.
Thanks for visiting our blog!
Juan
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
Ralf,
Usually there is no benefit, only if you intend to edit the data in Access.
Juan