I need a query builder activex control

[Tore]

I have an access project based on access 2000 and sql server 2000. I need a
query builder on the client so that users can compose their own queries and
run them. Where can I find a suitable control for this? I need a control much
like the one used to design a view in enterprise manager, or the control used
in Excel query. Can this be done?

The end users will have access to one table or view only, so they should not
need to make any table joins.

Regards

Tore

 

[Tom van Stiphout]

It's not a control, but there is a program called Microsoft Access
that can do just that. Given that you say you already are deploying an
ADP, it's likely users will have that tool on their machines.

No seriously, that;'s what I do when users ask for this.

-Tom.

 

[Tore]

Using the standard tools on the Microsoft Access client would likely cause a
view, function or stored procedure to be built or changed on the SQl Server
2000 (or am I wrong?). It is hard to imagine a number of users trying to
change the same view at the same time. I cannot see how this could work for
me.

Therefore, what I think I am looking for is a query builder that can run
locally on the client. I would like this tool to translate a graphical
representation of a query to valid SQL. Then I would like to transmit this
SQL-code from the client to the server (using ado?) and get a recordset back
to display.

I f anyone can point me in the right direction to find such a query builder
I will be happy. Or is there another solution to let concurrent users use
graphical tools to build a query?

Regards

Tore

 

[Mark Layne]

Tore,

We wrote an ADP interface form that allows the users to utilze the Access
Query builder tool to develop Ad Hoc Queries and then store that SQL String
into a Database table under their name for later retrival and reveiw. The
views can then be retrieved from the table and be re-run at points in time
and modified and resaved, etc...

This way they don't have to store separate query objects for each query they
build or worry about their loss when a new front-end version is releases.
(not an issue with ADP but was a big issue with MDBs). Also it allows the
users of an application the abilty to share the queries/views created so
that expertise can be shared.

Personally I would go that route to try and provide your users with
functionality.

Mark

 

[Tom van Stiphout]

In ADP, permanent query objects are ALWAYS stored in the SQL Server.

If you really need to have local objects, perhaps an MDB with
ODBC-attached tables to the SQL Server may be a better choice. It
stores these objects locally.

How about a hybrid? Keep your app in ADP, and give users an empty MDB
with attached tables so they can create their own local queries?

-Tom.

 

[Mark Layne]

There are better ways to do this yet more expensive though. Mainly see the
other post I put up. It takes some development time and a little effort but
then you don't have databases scattered around your network and you can
centrally manage the data. It also provides the users with a way to share
queries without actually transferring a physical file/object.

Allowing others the ability to create local object is a nightmare waiting to
happen in any multi-user implementation. (My opinion and as everyone has
one take it as you wish.) Unfortunately there will always be users that
know enough about Access and linking to tables that what you propose will
probably always happen. You just hope to provide them with enough tools
that you don't see it become detrimental to the management of the overall
system.