I have several data queries in a workbook that extract certain details from a
SQL database. A variety of functions are then performed on the results and a
graphical view of the results is diplayed for the user. The intent behind the
workbook is to act as a viewer for our ticketing system that allows Incident
Management to make informed decisions about which tickets need to be worked
when to meet a variety of Service Level Agreements.

What I now need to do is to lock the queries so that the SQL code cannot be
viewed. I think if I remove the all entries for Import External Data from the
Data menu and also edit the Query commandbar, I should be able to prevent
viewing of the queries. What I don't know are the command codes associated
with these menu options. Also, will removing access to the query menu options
prevent someone from viewing the query definitions or do I need to lock these
out another way?
--
Thanks,
R Woltner

Hi Rudy,

Rudy Woltner wrote:
I have several data queries in a workbook that extract certain
details from a SQL database. A variety of functions are then
performed on the results and a graphical view of the results is
diplayed for the user. The intent behind the workbook is to act as a
viewer for our ticketing system that allows Incident Management to
make informed decisions about which tickets need to be worked when to
meet a variety of Service Level Agreements.

What I now need to do is to lock the queries so that the SQL code
cannot be viewed. I think if I remove the all entries for Import
External Data from the Data menu and also edit the Query commandbar,
I should be able to prevent viewing of the queries. What I don't know
are the command codes associated with these menu options. Also, will
removing access to the query menu options prevent someone from
viewing the query definitions or do I need to lock these out another
way?

It will be difficult to hide the SQL queries from the user if you're using
QueryTables. If all UI accessibility is locked out, a smart user could use
3 lines of code to get all of your SQL queries programmatically.

It will take some more work, but I would suggest using ADO directly, then
exporting the recordset to a range (using CopyFromRecordset), then locking
your VBA project for viewing. Another safeguard would be to use stored
procedures or queries on the RDBMS directly. That way, your code is simply
calling a procedure, and the SQL text is not viewable.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]