Using DoCmd.OpenQuery on a query in a remote database

[teddysnips]

My clients have asked me to maintain a database that was developed in-
house. It's pretty good, considering the developer isn't a
"programmer".

The first thing they want me to do is to split it into a Front End/
Back End, which is very sensible. However, there are a number of ad
hoc queries that are required to be available to all users, which can
be edited, deleted or created "on the fly". The most sensible
solution would seem to be to store these in the Back End database, and
to get access to them thus:

Dim dbNew As DAO.Database
Dim qdfExisting As DAO.QueryDef

‘ Open the back end database
Set dbNew = DAO.Workspaces(0).OpenDatabase("C:\Projects\MyProject
\TestBackEnd.mdb")

‘ Open the query on the back end database
Set qdfExisting = dbNew.QueryDefs("qryGetCustomer")


In the existing code there is a function to edit these ad hoc queries
using the DoCmd.OpenQuery function.

DoCmd.OpenQuery "qryGetCustomer", acViewDesign

This works fine in the unsplit version. However, I can't seem to work
out how to pass a query on the remote database into this function.
Not unsurprisingly this doesn't work:

DoCmd.OpenQuery dbNew.QueryDefs("qryGetCustomer"), acViewDesign

Has anyone any ideas on how to solve this?

Thanks

Edward

 

[rkc]

My clients have asked me to maintain a database that was developed in-
house. It's pretty good, considering the developer isn't a
"programmer".

The first thing they want me to do is to split it into a Front End/
Back End, which is very sensible. However, there are a number of ad
hoc queries that are required to be available to all users, which can
be edited, deleted or created "on the fly". The most sensible
solution would seem to be to store these in the Back End database, and
to get access to them thus:

Dim dbNew As DAO.Database
Dim qdfExisting As DAO.QueryDef

‘ Open the back end database
Set dbNew = DAO.Workspaces(0).OpenDatabase("C:\Projects\MyProject
\TestBackEnd.mdb")

‘ Open the query on the back end database
Set qdfExisting = dbNew.QueryDefs("qryGetCustomer")


In the existing code there is a function to edit these ad hoc queries
using the DoCmd.OpenQuery function.

DoCmd.OpenQuery "qryGetCustomer", acViewDesign

This works fine in the unsplit version. However, I can't seem to work
out how to pass a query on the remote database into this function.
Not unsurprisingly this doesn't work:

DoCmd.OpenQuery dbNew.QueryDefs("qryGetCustomer"), acViewDesign

Has anyone any ideas on how to solve this?

Think about this for a minute:

If you keep the editable queries in the backend .mdb file when they
are edited the edits will affect everyone using the application.

 

[teddysnips]

Think about this for a minute:

If you keep the editable queries in the backend .mdb file when they
are edited the edits will affect everyone using the application

Which is exactly what's required.

Edward

 

[paii, Ron]

Think about this for a minute:

If you keep the editable queries in the backend .mdb file when they
are edited the edits will affect everyone using the application.

You can only execute a query stored in the front-end so your code will need
to copy it from backend before it is executed.

 

[Larry Linson]

Which is exactly what's required.

Seems unlikely to me... that is, if rkc modified a query for his own "ad
hoc" needs, then I'd be expected to have the same "ad hoc" needs next time I
used the application? In any case, you have to jump through some hoops to
store queries in the back end and execute them from the front end.

If these are queries that are part of the developed application, then they
should be created by the developers, distributed with each release of the
front end, and not changed by the users. The users, then, can use the
developed queries as the basis for their own "personal" queres, which they
store only in their own copy of the front end (or a separate front-end, as
the next release of the front-end will wipe out local modifications).

Few well-designed and well-implemented developed applications allow users to
make changes of the type you describe (or, in fact, use queries directly, at
all).

Larry Linson
Microsoft Office Access MVP

 

[teddysnips]

You can only execute a query stored in the front-end so your code will need
to copy it from backend before it is executed.- Hide quoted text -

I'm thinking that this might be a non-starter, for the reason above.
Maybe what should be stored on the server is the SQL. Can anyone see
any problems with this?

Thanks

Edward

 

[rkc]

I'm thinking that this might be a non-starter, for the reason above.
Maybe what should be stored on the server is the SQL. Can anyone see
any problems with this?

Now we're getting some where. Not only could you store the original SQL,
but you could store information about what it is used for.

And if it's really necessary, the latest user modification(s).

 

[teddysnips]

 >> Think about this for a minute:
 >>
 >> If you keep the editable queries in the backend .mdb file when they
 >> are edited the edits will affect everyone using the application
 >
 > Which is exactly what's required.

Seems unlikely to me... that is, if rkc modified a query for his own "ad
hoc" needs, then I'd be expected to have the same "ad hoc" needs next timeI
used the application?  In any case, you have to jump through some hoops to
store queries in the back end and execute them from the front end.

Unlikely as it may seem, that's the way it is. I'm sure this model
causes conniptions amongst design purists, but I'm doing this in the
real world, as a result of someone paying me money. They (the client,
which is incidentally the HR department of a multi-national company)
are perfectly happy with their database except they want me to split
it FE/BE and, in order to mimic the current way of working, this means
that the availability of the new or amended queries should be
distributed to ALL users. So, if there are any hoops that I can jump
through in order to open queries on a remote server using
DoCmd.OpenQuery or similar, I'd be insanely grateful if you could
share your information.

Thanks

Edward

 

[Stuart McCall]

if there are any hoops that I can jump

through in order to open queries on a remote server using
DoCmd.OpenQuery or similar, I'd be insanely grateful if you could
share your information.

Here's one way:

With dapenDatabase("c:\temp\test.mdb")
strSQL = .QueryDefs("RemoteQueryName").sql
End With
With CurrentDb.CreateQueryDef("LocalQueryName")
.sql = strSQL
End With

Now that the query exists in the front end, you can do whatever you normally
do with it.

Should you wish to later get rid of it:

CurrentDb.QueryDefs.Delete "LocalQueryName"

 

[paii, Ron]

I'm thinking that this might be a non-starter, for the reason above.
Maybe what should be stored on the server is the SQL. Can anyone see
any problems with this?

Thanks

Edward

Look at Stuart McCall's post, it shows how to copy the stored query from the
backend to the front-end. You will need to add code to do this each time a
user wants to run an ad-hoc query. You will also need to do the reverse each
time a user edits a ad-hoc query. This will be easy if you are using a form
to execute the query, otherwise supply a function to update at start-up
and/or shut-down. By the way, are you distributing a front-end for each user
or are they sharing?

 

[Larry Daugherty]

While lots of the lads and lasses who help other developers here are
wiser than I, few are also older. Larry Linson is one such.

To casually dismiss the suggestion that protecting the customer's data
is merely the behavior of a "purist" is to attempt thereby to
rationalize and justify irresponsible behavior. I know that Larry
Linson and just about all of us who respond in these threads to help
other developers have years in the trenches. We have all dealt with
customers large and small. We have always had to do the best and most
responsible job we can. Sometimes that's over client objections. In
almost all cases there is some education of the client involved. It's
not all one way, we learn from our clients too. But, where the
protection of the client's data is concerned, we are the responsible
ones and what we have to say carries the day.

There are so many ways to achieve the same informational results for
the end users without allowing them design rights. Queries are good
things. Just about all of our forms and reports and many controls
depend on them. But live queries do not belong in the hands of end
users. That's only half a step removed from inviting them to muck
around directly in the tables (oh, that would be tougher to do since
your application is at least split.... ). There are many ways to
use queries with list boxes and other controls to achieve the same
informational results without exposing the data to the users with no
protection at all. The proper design would eliminate the need for
users to be involved at the level of SQL and would be significantly
more intuitive for the users.

As to kowtowing to "Intimidating, Impressive, Authority", *don't*!
They need you or you or they wouldn't be paying you to be there. In
the data domain of their application, *you* are the authority. That's
one of the things you are being paid to provide. You owe them your
best level thinking and performance. By the way, as a "purist" my
customers have included the largest wireless telecom provider in the
US and the largest bank in the US. There were many other customers,
large and small. Usually there were several applications per customer
Similar issues arose in dealing with them. The issues were discussed
and resolved. In no instance of any application was the user invited
nor allowed into design mode. Delivered application front ends were
MDE files.

The mode of operation in the current application of your customer is
similar to the bliss and arrogance of NASA before the Challenger
disaster. There were going to do what they wanted in a way that would
bring them the greatest praise and satisfaction. To Hell with those
bothersome "purist" engineers who told them that attempting to launch
during extremely cold weather was to invite failure. Hopefully, the
consequences would not be so drastic in this case.

Notice that neither Larry Linson nor I suggested that "in a perfect
world I would do it this way ...." In fact, the message is that if he
or I had the responsibility for the exact application that you do we
would provide the informational solution in a way that did not involve
the user getting at the design in any way. Not with arrogance but
with information and by persuading and selling the better methods to
the customer.

HTH
--
-Larry-
--

Seems unlikely to me... that is, if rkc modified a query for his own "ad
hoc" needs, then I'd be expected to have the same "ad hoc" needs next time I
used the application? In any case, you have to jump through some hoops to
store queries in the back end and execute them from the front end.

Unlikely as it may seem, that's the way it is. I'm sure this model
causes conniptions amongst design purists, but I'm doing this in the
real world, as a result of someone paying me money. They (the client,
which is incidentally the HR department of a multi-national company)
are perfectly happy with their database except they want me to split
it FE/BE and, in order to mimic the current way of working, this means
that the availability of the new or amended queries should be
distributed to ALL users. So, if there are any hoops that I can jump
through in order to open queries on a remote server using
DoCmd.OpenQuery or similar, I'd be insanely grateful if you could
share your information.

Thanks

Edward

 

[lyle fairfield]

Just in case, someone should think that silence means consent, or
assent, I consider this to be a very bad idea.

 

[teddysnips]

While lots of the lads and lasses who help other developers here are
wiser than I, few are also older.  Larry Linson is one such.  

How old do you think I am? (I'm 51) What basis do you have for your
belief? And what has age got to do with anything?

To casually dismiss the suggestion that protecting the customer's data
is merely the behavior of a "purist" is to attempt thereby to
rationalize and justify irresponsible behavior.  I know that Larry
Linson and just about all of us who respond in these threads to help
other developers have years in the trenches.  We have all dealt with
customers large and small.  We have always had to do the best and most
responsible job we can.  Sometimes that's over client objections.  In
almost all cases there is some education of the client involved.  It's
not all one way, we learn from our clients too.  But, where the
protection of the client's data is concerned, we are the responsible
ones and what we have to say carries the day.

I completely disagree. We are there to do what our clients instruct
us to do. Sure, we should warn them if they plan to do something
really dumb, but in the end the decision is theirs, not ours. If you
ride roughshod over your client's stated requirements, then good luck
getting repeat business.

There are so many ways to achieve the same informational results for
the end users without allowing them design rights.  Queries are good
things.  Just about all of our forms and reports and many controls
depend on them.  But live queries do not belong in the hands of end
users.  

They do if that's what the client wants. I don't wish to offend, but
your attitude is highly patriarchal - in fact, you remind me of the
medical profession in the old days, who wouldn't trouble their clients
(patients) with the details of their diseases, because the poor dears
simply didn't have the mental capacity to understand.

That's only half a step removed from inviting them to muck
around directly in the tables (oh, that would be tougher to do since
your application is at least split....  ).  There are many ways to
use queries with list boxes and other controls to achieve the same
informational results without exposing the data to the users with no
protection at all.  The proper design would eliminate the need for
users to be involved at the level of SQL and would be significantly
more intuitive for the users.

I don't disagree with you. In an ideal world. But if you trouble to
read the thread properly, you'll find that I'm simply doing some small
amendments to allow an established application to run in a client/
server environment. There is no budget to give the client the ability
to create safe reports, ad hoc, as and when needed, via a super-duper
report builder. What they have now is, I admit, dumb and dirty, but
they're happy with it, and I want to get paid.

As to kowtowing to "Intimidating, Impressive, Authority", *don't*!
They need you or you or they wouldn't be paying you to be there.  In
the data domain of their application, *you* are the authority.  That's
one of the things you are being paid to provide.  You owe them your
best level thinking and performance.  

Which I've already given them, by alerting them to the potential risks
of allowing users to create queries ad hoc. My reservations are
documented. They're happy. Therefore, so am I.

By the way, as a "purist" my
customers have included the largest wireless telecom provider in the
US and the largest bank in the US.  There were many other customers,
large and small.  Usually there were several applications per customer
Similar issues arose in dealing with them.  The issues were discussed
and resolved.  In no instance of any application was the user invited
nor allowed into design mode.  Delivered application front ends were
MDE files.

But I'm not delivering something in that sense - this is a mature
application that, for reasons unrelated to current performance
(reasons of IT management), the clients require to be split into FE/
BE.

The mode of operation in the current application of your customer is
similar to the bliss and arrogance of NASA before the Challenger
disaster.  There were going to do what they wanted in a way that would
bring them the greatest praise and satisfaction.  To Hell with those
bothersome "purist" engineers who told them that attempting to launch
during extremely cold weather was to invite failure.  Hopefully, the
consequences would not be so drastic in this case.

That's rather over-egging the pudding, I think.

Notice that neither Larry Linson nor I suggested that "in a perfect
world I would do it this way ...."  In fact, the message is that if he
or I had the responsibility for the exact application that you do we
would provide the informational solution in a way that did not involve
the user getting at the design in any way.  Not with arrogance but
with information and by persuading and selling the better methods to
the customer.

Well, I'm a developer, not a salesman. If I had any talent in that
area I'd be selling for all I was worth, since I'd then stand a chance
of early retirement! If I were to stand my ground and refuse to do
the work on the grounds of a poor design, they would simply take their
business elsewhere. So I'm stuck with the current design.

And since you all are such experienced developers, care to tell me HOW
I can do what my clients have asked me to do, rather then WHY I should
NOT do it? That is, if you know. If it can't be done (opening a
remote query using DoCmd.OpenQuery) then I'll have to look at other
options, such as storing the SQL and creating local querydefs. That
method works in theory, but the problem is that the QBE window is
asynchronous, so capturing it being saved and then writing the revised
SQL to a table is a little more complex.

Edward

 

[lyle fairfield]

Just to assure myself that it was possible I just changed a Northwind
query from a new database, db1, using DoCmd etc.

 

[teddysnips]

Just to assure myself that it was possible I just changed a Northwind
query from a new database, db1, using DoCmd etc.

I've entirely failed to be able to do that. Would you be so very kind
as to post the code? In other words, how do you reference the
Northwind query in your db1 database code?

Thanks

Edward