Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to programmatically switch query to design mode

Posted on 2008-10-01
12
Medium Priority
?
850 Views
Last Modified: 2013-11-29
I have an Access databas that does not allow objects like forms and tables to be modified in design mode.  I just added the default Access buttons to allow users to create custom data queries.  If a new query is created and run, but not yet saved, Access recognizes it as "query1" and that is fine.  

The problem is once the query is run and you are looking at the data, one might decide to modify the query further, but I can find no way to get back to design mode without adding the default Access design mode button.  The problem with that is that it would then allow users to switch forms, reports, tables, etc in the database to design mode!

I need a way to only switch the query to design mode!
0
Comment
Question by:bc4942
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
12 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22619222
docmd.OpenQuery "YourQuery",acViewDesign
0
 

Author Comment

by:bc4942
ID: 22634856
The problem is just are when you are designing a query in Access, the query may not be saved to the database.  Therefore, how would I use:
docmd.OpenQuery "YourQuery",acViewDesign
if the query has not been saved and therefore technically has no name.
0
 
LVL 85
ID: 22635065
You couldn't ... you'd have to save the query, then open it in Design view.
0
Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!

 

Author Comment

by:bc4942
ID: 22636829
So then how does Access do it using the default "design mode" button.  With it, you can switch back and forth from design to actually running the query even though the new query has not been saved yet.
0
 
LVL 85
ID: 22637496
I would suppose it builds a temporary query and allows you to work with that. That's typically the way object-oriented design works.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22643541
bc4942,

If I am reading your post correctly:
  "I have an Access databas that does not allow objects like forms and tables to be modified in design mode."
And:
  "but I can find no way to get back to design mode without adding the default Access design mode button."

From this I can acertain that you have merely turned off/hidden the "View" button.
And/Or disabled the View menu commands

If so, then this is not a good way to protect your Objects because users can still use Ctrl+< and Ctrl+> to switch to design view.

The better way is to create an MDE file.
(Tools-->Database Utilities-->Make MDE file)
In an MDE file you don't have to worry about deleting the view button or menu commands because users will not be able to modify Forms or Reports by default.
But they can still create run, and yes, switch back and forth between datasheet view and design view before saving a query.

JeffCoachman
0
 

Author Comment

by:bc4942
ID: 22650373
I understand.  Unfortunately, I have not been able to get my database to make an MDE file in a few years.  Always get the following error:
"Microsoft Office was unable to create an MDE database."
This error is usually associated with compiling a large database into an MDE file.  Due to the method used to compile the database, a considerable number of TableID references are created for each table.  The Microsoft Jet database engine version 4.0 can only create a maximum of 2048 open TableIDs at one time.  Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Jet database engine uses during the process of compiling a database as an MDE.  However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.
More information about this error message online.  
0
 
LVL 85
ID: 22650793
Are you able to COMPILE your database? Unless your database is HUGE (and I don't mean a couple dozen tables and forms) then you should be able to convert to MDE format. I've successfully compiled apps with 75 forms, 35+ reports, 75+ queries and somewhere around 35 tables. The most common issue when trying to convert to MDE is (a) trying to convert the wrong format (i.e. trying to convert an Access 2000 db while you're working in Access 2003) or (b) failing to compile the database before attempting to convert it.
0
 

Author Comment

by:bc4942
ID: 22669084
Yes, it is quites large.  There are no tables as it uses MySQL to store data, but there are lots of forms, reports, and queries with thousands of controls that will easily exceed the 2048 "table ID" limit of Access.
I can however compile the database and am running Access 2003 and the databas is in 2003 format.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22674790
bc4942,

The same thing happened to me a while ago.
Here is what I had to do:
Delete all unused Variables, Procedures, and Functions
Delete all excessive "White space"
Decopile the databse: (http://www.mvps.org/access/bugs/bugs0008.htm)

JeffCoachman
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question