Solved

Refreshing Views in a Access ADP

Posted on 2011-09-05
4
601 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
Comment Utility
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
Comment Utility
Thanx Boag2000, but no luck on this option.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
Comment Utility

 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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now