|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
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.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Yarik" <yarik[ at ]garlic.com> wrote in message
news:1156461126.957748.88050[ at ]i42g2000cwa.googlegroups.com...
[Quoted Text] > 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.
>
|
|
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
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uPmDMe9xGHA.2396[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > 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. > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF> > > "Yarik" <yarik[ at ]garlic.com> wrote in message > news:1156461126.957748.88050[ at ]i42g2000cwa.googlegroups.com... >> 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. >> > >
|
|
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?
|
|
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, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Yarik" <yarik[ at ]garlic.com> wrote in message
news:1156472117.773262.145600[ at ]74g2000cwt.googlegroups.com...
[Quoted Text] > 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?
>
|
|
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.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Yarik" <yarik[ at ]garlic.com> wrote in message
news:1156472117.773262.145600[ at ]74g2000cwt.googlegroups.com...
[Quoted Text] > 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 (fill the blanks, no spam please) wrote:
[Quoted Text] > 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... :-))
|
|
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.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Yarik" <yarik[ at ]garlic.com> wrote in message
news:1156506291.880925.20420[ at ]74g2000cwt.googlegroups.com...
[Quoted Text] >
> Sylvain Lafontaine (fill the blanks, no spam please) wrote:
>> 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... :-))
>
|
|
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
"Yarik" <yarik[ at ]garlic.com> wrote in message
news:1156506291.880925.20420[ at ]74g2000cwt.googlegroups.com...
[Quoted Text] >
> Sylvain Lafontaine (fill the blanks, no spam please) wrote:
>> 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... :-))
>
|
|
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.
--
Malcolm Cook - mec[ at ]stowers-institute.org
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA
"Yarik" <yarik[ at ]garlic.com> wrote in message news:1156472117.773262.145600[ at ]74g2000cwt.googlegroups.com...
[Quoted Text] > 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?
>
|
|
Malcolm Cook wrote:
[Quoted Text] > 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...
|
|
|