I am a self-taught new user so bear with me. I am performing data analysis in VBA and using SQL commands to extract and combine data from multiple tables, using SELECT . . . INTO and then DROP TABLE to eventually delete the interim tables (4 of them). I don't want to use queries since they are visible in the database to "novice" Access users and subject to modification. Will this eventually cause fragmentation problems, and is this not generally a good practice?

clwk,

In terms of "good practice" I don't think it makes a major difference, in my opinion, there are a number of factors that should be taken into consideration when working on a project such as development time available, target audience, support available etc.

I personally dont like using queries as in a medium-large system, there can be loads of them and it can get messy.

I prefer to build and object model and have each object handle its own data access, or hand over to a data acess tier. This seems more logical to me and appears much cleaner.

Each "query" is also encapsulated so any changes to that query will be seperate from any others (theres nothing worse than updating a query in one place then making a form somewhere else fall over because it is bound to the said query that you have inserted another column into).

I hope this gives you some "food for thought".

To be honest, for me, best practice is not always a "buy the book" example.
"Best Practice" is making sure you meet your specification on time and with as robust yet flexible a system as possible/required.

Best Regards,
Rob

Right click on the query, select properties, then check Hidden. Most users do not have View Hidden Objects enabled in their Tools menu, so they just disappear to them.

Another option is to use code and put your query string in code and run it from modules. Then convert the mdb to an mde so users can't see your code or SQL strings. So the queries are totally invisible to users.

You can also make your tables invisible to users rather than creating and dropping them programatically.

There are also other things you can do to prevent novices from poking around in the database window.

HTH

mmcdonal

Also, if you are modifying components, don't forget to View Dependent Objects so you can cascade your changes down through other queries, forms and reports.


mmcdonal

I am working in Access 2007. If I hide a query by checking "hidden", how would I then "View Hidden Objects"? The 2007 version no longer has a Tools drop down menu. It also has an option to "Hide in this group" when you right click the query, which may work also. I am just afraid to hide the query without knowing how to "unhide" it.