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
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
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
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
Pingback: 10 things every Access developer who works with SQL Server should know | accessexperts.com