Solved

Refreshing Views in a Access ADP

Posted on 2011-09-05
4
607 Views
Last Modified: 2012-05-12
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
now...
   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.
0
Comment
Question by:Milkus1
  • 2
4 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36489025
Not sure but:

CurrentDB.execute
...has an argument of: dbSeeChanges

Perhaps CurrentProject.Connection.Execute
...has a similar argument?

See the Access help files for more info...
0
 

Author Comment

by:Milkus1
ID: 36492965
Thanx Boag2000, but no luck on this option.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 36495153

 I've been hanging back watching this seeing if anyone would kick in.  I don't work with ADP's, but use Access with SQL server quite a bit (all ODBC however).

 I think you got what you got; if you change the view, Access has to read the meta-data and I know in general that anytime you refresh a collection in Access, it is an expensive operation in terms of performance.

<<   DoCmd.RunSQL "Create View " & tempS & " as " & sql
now...
   CurrentProject.Connection.Execute "Create View " & tempS & " as " & sql>>

<<The issue is that the View on the SQL Server is fine, but Access seems to be retaining the old metadata for the link. >>

  That's because you need to understand what's going on; "Access" is actually multiple componets: Access, VBA, and JET.

  The first operation you used is through Access, so the collection in Access gets refreshed.  The second is a JET based one, so your cutting Access out of the picture.   It's the same thing with CurrentDB() vs. dbEngine.Workspaces(0).Databases(0) in DAO.   CurrentDB() is an Access call, dbEngine a JET object.   Calling CurrentDB() is about 5000 times slower.

  You might want to check out the article I wrote:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2072-CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

  Which goes into a little detail on that.

  Suffice to say I think your stuck with what your stuck with.

Jim.
0
 

Author Closing Comment

by:Milkus1
ID: 36546714
Thanks Jim, I am of similar conclusion.
I understand the difference between the two approaches...just HOPING for a magic solution.
I have been able to significantly speed up most of the application using JET methods where view creations arent required...Its only a few places I am now stuck, my users seem happy enough with the trade off for now.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question