Solved

Refreshing Views in a Access ADP

Posted on 2011-09-05
4
605 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This collection of functions covers all the normal rounding methods of just about any numeric value.
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

863 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

26 Experts available now in Live!

Get 1:1 Help Now