?
Solved

Refreshing Views in a Access ADP

Posted on 2011-09-05
4
Medium Priority
?
614 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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

719 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