Refreshing Views in a Access ADP
Posted on 2011-09-05
Can some one tell me how I can 'refresh' a view in an Access ADP via VBA.
The issue is I create a view via VBA on the SQL Server for running a reports module.
The View is never the same, except the name.
Its deleted then recreated everytime the report is run.
Initially I was using the doCmd.RunSQL method to send the Create View statement, but I have found it creates lots of overhead for performance as it queries the server for metadata everytime.
DoCmd.RunSQL "Create View " & tempS & " as " & sql
CurrentProject.Connection.Execute "Create View " & tempS & " as " & sql
I moved to the Connection.execute method, which is quicker and cleaner but the view's structure is never refreshed after it is deleted and recreated. So if it runs the first time with 4 fields, the second time round the query may only require 3 fields ...but the fourth still exists.
The issue is that the View on the SQL Server is fine, but Access seems to be retaining the old metadata for the link.
I have tried the following to no avail:
CurrentProject.Application.RefreshDatabaseWindow (bigger overhead than runSQL)
I have also tried all the querydef/tabledef relink suggestions...but given it is an ADP most of this functionality is not available.
I need to find a low overhead solution as our interstate users are having terrible performance issues with SQL server, and the RunSQL statement appears to be a huge issue for creating excessing SQL server calls for metadata.