Solved

Refreshing Views in a Access ADP

Posted on 2011-09-05
4
609 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

734 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