How to refresh (programmatically) an ADP project's knowledge about views and stored procedures?

[Yarik]

Hello,

I hope somebody could help me with this seemingly simple problem:

I have an ADP project (MS Access 2000) referring to SQL Server
database. I guess everyone knows that if I create a new view or stored
procedure in the SQL Server database while MS Access ADP application is
running, the application does not "know" about that new view or SP
until its "knowledge" about the underlying SQL Server database is
"refreshed". The simplest possible way that I know to refresh such
knowledge MANUALLY is to open the "Database" window, switch to the
"Views" or "Stored Procedures" view, and use the "View >> Refresh"
command (or F5 hotkey). Now, the general question is: how to do such
refresh programmatically?

FWIW: Here is the more specific variant of the problem. When a user
presses certain button on MS Access form, the ADP application is
supposed to make a call to DoCmd.OpenView("Someviewname"). So the
application must "know" about this view in the underlying SQL Server
database. So I want to find a way to programmatically refresh this
knowledge before calling DoCmd.OpenView. It is important that this
programmatic refresh does not produce any UI effects visible to the
user (like opening/closing and activating/deactivating any windows).

Please advise. Any help would be greatly appreciated.

Thank you,
Yarik.

 

[Sylvain Lafontaine]

You can try the RefreshDatabaseWindow method; however, I don't know if this
will works with views. Another possibility would be to close/reopen the
connection.

 

[Robert Morley]

Yes, RefreshDatabaseWindow does work with Views, just be sure to display the
database window (if it's not already) and select the View tab first. You
can do all of this programmatically, though I don't remember the exact
command off the top of my head. One of the DoCmd.RunCommand options, IIRC.
Let me know if you need specifics and I can look up how I'm doing it at work
tomorrow.

To my knowledge you can't refresh the view info without displaying the
database window, at least temporarily.



Rob

 

[Yarik]

Thank you guys for the ideas, but the problem is still open...

RefreshDatabaseWindow does not seem to help. At least not in all
situations (for example, it definitely does not help when the
underlying view gets renamed). I did not try it while the Database
Window is open and active, but even if it worked that way it would
definitely be my latest resort (because users are not supposed to see
that window).

As for closing/reopening the project's connection... that's an
interesting idea, but how exactly do I do that? It looks like
CurrentProject.Connection returns something like a clone of the actual
connection used by the project, so closing it does not make sense, does
it?

 

[Sylvain Lafontaine]

Use Application.CurrentProject.Connection.Close and
Application.CurrentProject.Connection.Open "... Connection string ..." .

Don't know if this will work. If it works, then it's also possible that
simply calling Open without first calling Close will also work.

 

[Sylvain Lafontaine]

BTW, what you do you want to achieve with these dynamic views? Maybe
another solution exists without the need of creating a new view each time.

 

[Yarik]

BTW, what you do you want to achieve with these dynamic views? Maybe
another solution exists without the need of creating a new view each time.

Well, maybe... I am not sure I understand what do you mean by "dynamic
views", but the bigger problem context is the following:

We have a dozen or two of views and stored procedures that provide some
interesting results (interesting to the end-users). They usually fall
into one of the following two categories:

(a) An ad-hoc, quick-and-dirty report that is not supposed to be used
frequently and therefore does not have to have fancy UI. For example, a
report showing some inconsistencies in data. In fact, some of those
reports may have very short life-time...

(b) A prototype of some long-term report or form, whose requirements
are still under development. Eventually, it's going to be a full-blown
form with complex UI or a fancy printable report. But for some time a
simple datasheet view is sufficient.

The problem is: we want users to be able to see the results of all
these views and stored procedures, but it's not practical (from the
cost/benefit point of view) to create forms or reports as front-ends
for these views and SPs. Needless to say, it is not practical to
maintain those front-ends as we change the underlying views and stored
procedures. So we came up with the following idea:

-- There is a table listing all these views and stored procedures (we
call them "report prototypes") along with their human-friendly
descriptions and other attributes.

-- There is a form that allows users to browse all available report
prototypes and launch them (without knowing about such "weird",
programmerish things as views and stored procedures).

Behind the scene, this "report prototype launchpad" form uses
DoCmd.OpenView() and DoCmd.OpenStoredProcedure() to show users what
they want. Basically, it seems to be a very cheap and simple way to let
users play with temporary or prototypical reports.

The general idea seems to be nice and easy to implement, but... as
usually, the Devil turns out to hide in technical details... )

 

[Sylvain Lafontaine]

The problem with this method is that the probabilities are very high that
some of these "on the fly" reports will be bugged: you cannot change the
schema of a database and keep the Views and SP updated at the pace while
your users are playing with these Views and SP at the same time.

 

[Robert Morley]

Here's the method I use. As you point out, it has the disadvantage of
briefly showing the database window, but it's the best I've been able to
come up with.

SendKeys "{F11}", True
DoCmd.RunCommand acCmdViewViews
Application.RefreshDatabaseWindow
DoCmd.RunCommand acCmdWindowHide

For the first line, you can use API calls to find and unhide the database
window as well if you don't like using SendKeys.



Rob

 

[Malcolm Cook]

Yarik,

In my hands (Ac2003/XP) it DOES word when the the database window is hidden and either the underlying view gets renamed or a new
view gets created.

In other words, the commented lines below are NOT needed to make this function work as your requirements dictate.

I created new views and modified existing views using another program (Enterprise Manager) and my ADP picked up the changes. The
RefreshDatabaseWindow was required.

Public Sub FreshOpenView(strViewName As String)
' Application.echo False
Application.RefreshDatabaseWindow
' DoCmd.SelectObject acFunction, strViewName, True
DoCmd.OpenView strViewName
' Application.echo True
End Sub


Try it again.

 

[aaron.kempf]

so.. when you create these 'dynamic views' for people.. why don't you
just query sysobjects instead?

keep people out of the db window in most circumstances

-Aaron
ADP Nationalist

 

[Yarik]

If it was a question for me, then I am afraid I do not understand it.
Why would I want to query "sysobjects"?

As for showing DB Window to users: that's exactly what I want to avoid
by all means...

 

[aaron.kempf]

so instead of worrying about why the db window isn't showing the
objects that you're looking for-- just have a list box that contains
the query-- select name from sysobjects where xtype = 'P' and name like
'Analyst%' for example

then you just requery the single list box when you need to; treat it
like a normal object.

or of course
Docmd.SelectObject "MyView", True (in databaseWindow)
then
Docmd.RefreshDbWindow- or whatever macro / argument you were using
earlire

-Aaron

 

[Yarik]

so instead of worrying about why the db window isn't showing the
objects that you're looking for-- just have a list box that contains
the query-- select name from sysobjects where xtype = 'P' and name like
'Analyst%' for example

then you just requery the single list box when you need to; treat it
like a normal object.

or of course
Docmd.SelectObject "MyView", True (in databaseWindow)
then
Docmd.RefreshDbWindow- or whatever macro / argument you were using
earlire

Hmm... I still do not understand, sorry.

The problem is not in how to obtain the name of the view (now I get it
from a table along with its human-friendly name, description, and other
attributes). The problem is to make DoCmd.OpenView work with that
name...

 

[Yarik]

Yarik,

In my hands (Ac2003/XP) it DOES word when the the database window is hidden and either the underlying view gets renamed or a new
view gets created.

In other words, the commented lines below are NOT needed to make this function work as your requirements dictate.

I created new views and modified existing views using another program (Enterprise Manager) and my ADP picked up the changes. The
RefreshDatabaseWindow was required.

Public Sub FreshOpenView(strViewName As String)
' Application.echo False
Application.RefreshDatabaseWindow
' DoCmd.SelectObject acFunction, strViewName, True
DoCmd.OpenView strViewName
' Application.echo True
End Sub

Try it again.

I only have Access 2000 + Windows 2000 (the configuration that most of
the users have) and Access 2003 + Windows 2003...

I tried. Alas, does not work in neither of two configurations...

 

[aaron.kempf]

a-ha--- so sorry now i understand


i'll post an example soon

 

[dbahooker]

the underlying view got renamed?

right-click rename or sp_renameobject

yeah there is a bug on this; sometimes it gives the wrong info

tell us more; give us specifics