A Couple of Access 2010 Questions

[NoSmoke]

Tried a couple of questions on the MrExcel forum with no response so far so though I might post them here also.

First is:

The only way I can figure out how to launch the Query Wizard by a button click is to use a SendKeys macro. I have looked at other macro commands but there does not seem to be any other way of doing it.

I would also like to use the .accdr extension to keep users out of stuff that might cause problems but that suppresses the button with the above mentioned SendKeys macro.

Could anyone kindly suggest a way of using a button to launch the Query Wizard without using SendKeys or, better still, do it while keeping the .accdr extension?

Second question is:

I have downloaded the Northwind database to examine how it is designed but I can't figure out how to open any of the tables or forms in design view (eg right-clicking on a table or report name doesn't give "Design View" in the pull-down list)). It seems to be the same situation with the Acccess templates one can download.

Can anyone please help?

 

[John Big Booty]

Are you suggesting that you want to leave construction of Queries up to the user

This, in my mind at least, is a recipe for disaster, the user could create a delete query and "accidentally" (or otherwise) delete all your data, the range of things that could go wrong boggles the mind.

You would be far better off, creating a generic query, that allows the user to select various criteria they may need to use.

Without knowing exactly what you are trying to do I can't really be more specific.

 

[CJ_London]

Have you made sure you have set the db as trusted?

There are other posts to explain how, but I attach a document I use which you may find helpful.

With regards opening the queries from an accdr - I agree with John BB.

 

[NoSmoke]

Are you suggesting that you want to leave construction of Queries up to the user

This, in my mind at least, is a recipe for disaster, the user could create a delete query and "accidentally" (or otherwise) delete all your data, the range of things that could go wrong boggles the mind.

You would be far better off, creating a generic query, that allows the user to select various criteria they may need to use.

Without knowing exactly what you are trying to do I can't really be more specific.

Thank you for responding to my post.

All the users would be doing is using the wizard (only) to create a tabulation of data in "table" form (and maybe print it) containing only the fields wanted and also sorted or filtered to certain field values as required. They would be instructed to restrict themselves to the base wizard capability and stay out of the query design stuff. The database in any case is backed up at least once per day so if the users stuff it up, they will have to re-enter the data that followed the last intact back-up.

Anyhow, if the users are kept out of the ribbon by use of the .accdr extension, that would add to the security (I think) but, getting back to my original question, the problem I then have is how to launch the query wizard (via a command button).

I dunno, maybe that's not a good policy and a "generic" query would be better. I'm not sure what you mean by a generic query however and would appreciate any elaboration you could provide.

 

[NoSmoke]

Have you made sure you have set the db as trusted?

There are other posts to explain how, but I attach a document I use which you may find helpful.

With regards opening the queries from an accdr - I agree with John BB.

CJ, thanks for responding. I'm not sure where to find the attachment you mentioned but both the programs I have tried (the "Northwind sales web database" and the "Desktop services template") are stored in a trusted folder, along with database files of my own creation (as per "Trust Center Settings/Trusted Locations"). Or is something else required??

 

[John Big Booty]

Thank you for responding to my post.

All the users would be doing is using the wizard (only) to create a tabulation of data in "table" form (and maybe print it) containing only the fields wanted and also sorted or filtered to certain field values as required. They would be instructed to restrict themselves to the base wizard capability and stay out of the query design stuff. The database in any case is backed up at least once per day so if the users stuff it up, they will have to re-enter the data that followed the last intact back-up.

Anyhow, if the users are kept out of the ribbon by use of the .accdr extension, that would add to the security (I think) but, getting back to my original question, the problem I then have is how to launch the query wizard (via a command button).

I dunno, maybe that's not a good policy and a "generic" query would be better. I'm not sure what you mean by a generic query however and would appreciate any elaboration you could provide.

You can give all the instructions you like, but there will always be the user too silly to realise what they are doing, or who thinks they know more than what they do. I would not allow users direct access to the query building wizards. I'd be building an interface that allowed the user to collect his information in a very controlled manner, that did not risk the integrity of my DB.

 

[CJ_London]

sorry, thought it was attached, but just realised it exceeded file size for a pdf - here it is again, zipped

 

[NoSmoke]

Thanks CJ but I've already checked that the Trusted Locations contains the folder in which the Access files in question (and including my own databases) are stored.

I've noticed the icons to the left of the table etc. names in the navigation(?) pane for the problem databases have a blue dot in the middle and, the File/Options/Current Database pane has the "Enable Layout View" and "Enable design changes for tables in Datasheet view" boxes are checked but grayed out.

I wonder if these MS example databases are not meant to be modified??

 

[Pat Hartman]

Ditto what John said.
Allowing the users to make queries and work directly with the recordsets they create circumvents ALL validation code you have put into your forms to ensure they only enter valid data. You have no control over what they do with a query. As long as it doesn't violate RI, ACE/Jet will allow it to happen.

If your users need to analyze data, create some generic queries and allow them to export the data to Excel where they can work however they want. You can add criteria to allow the users to limit their selections.

As to the .accdr, I also prefer to use it but as you have found out, you CANNOT get to design view on any object in an .accdr. Don't make the mistake of thinking this makes the db secure since a savvy user can simply rename to .accde/.accdb and have access to everything.

Since I would never allow a user to open the QBE, I don't really know how to do it with VBA but I would guess either the DoCmd. or Application. objects will provide the method you need. Try each in a VBA module and scoll through the options presented by intellisense.

 

[jdraw]

Nosmoke

You have an answer on your cross posted item at
http://www.mrexcel.com/forum/micros...y-launch-query-wizard-button.html#post3402935

 

[NoSmoke]

Ditto what John said.
Allowing the users to make queries and work directly with the recordsets they create circumvents ALL validation code you have put into your forms to ensure they only enter valid data. You have no control over what they do with a query. As long as it doesn't violate RI, ACE/Jet will allow it to happen

.

Pat, I hear what you folks are saying but my users are responsible and intelligent. They know if they screw up the data, they will have to correct it. I think I also mentioned that we keep daily backups which would be useful in that regard. Trouble with generic queries is that we have about 100 data fields (mainly for wildlife rehab medical information) and the users have a wide number of possibilities for queries which are hard to predict in advance. I have instructed them carefully on how to use the wizard and what not to do - I know it's not fool-proof by any means but I think it's workable. Maybe it's not however, I guess time will tell.

I also realize the user(s) could easily change the extension but they are responsible individuals (and only two of them) so I don't think that would happen. if it does, and they screw up, tough luck for them....

BTW, what does ACE/Jet mean?

If your users need to analyze data, create some generic queries and allow them to export the data to Excel where they can work however they want. You can add criteria to allow the users to limit their selections.

As to the .accdr, I also prefer to use it but as you have found out, you CANNOT get to design view on any object in an .accdr. Don't make the mistake of thinking this makes the db secure since a savvy user can simply rename to .accde/.accdb and have access to everything.

Since I would never allow a user to open the QBE, I don't really know how to do it with VBA but I would guess either the DoCmd. or Application. objects will provide the method you need. Try each in a VBA module and scoll through the options presented by intellisense.

 

[Pat Hartman]

Jet is the name of the database engine that Access relies on for versions A2003 and older. For A2007 and newer, the database engine is now called ACE and is managed by the Access team. Jet was managed by the SQL server team.

Letting the users create objects in the production FE is a bad idea no matter how responsible they are. What do you plan on doing if you need to ship out an update to the FE. Are you going to personally merge each user created object into the new FE?

If you want to give them query access, a better solution would be to create a separate FE to use as a workplace. I would create a dummy table with a single record. I would then create a query for each table and add this dummy table to the query with no join lines. The result would be a Cartesian Product which would not be updateable and so fat fingered users couldn't accidentally change data. I would then hide all the tables. The users would simply build their queries based on the non-updateable queries rather than with the tables.

 

[AccessJunkie]

Hi,

I have downloaded the Northwind database to examine how it is designed but I can't figure out how to open any of the tables or forms in design view (eg right-clicking on a table or report name doesn't give "Design View" in the pull-down list)). It seems to be the same situation with the Acccess templates one can download.

This is by design actually. All Access 2010 web databases do not allow you to open web objects in Design view. If you are using the Northwind 2010 web database, those objects are all web objects and therefore cannot be open in Design view. You have to use Layout view for web forms and web reports and you have to use Datasheet view to design web tables.

You can tell if an object is a web object by looking at the object's icon in the Navigation pane. If the icon has a globe on it, it is a web object.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------