Queries :: Crosstab Design Is Slow When Opening And Saving In Design Mode?
Oct 6, 2014
I have had to use my first crosstab queries.
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes.
I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
I have put much time in designing a form. After exiting and saving it, I cannot now open it. Only the Page Header shows, and I cannot get into 'Design' mode. I have tried repairing and compacting to no avail. Any ideas?
I just imported all of my objects (tables, forms, etc.) into a brand new database file and all of my forms are now full screen instead of the size I set them to in design. What caused this? I can't get them back to their normal size.
I am using Access 2007 but imported files into 2003 .mdb file.
When I click on the design mode of a query object, generally MS Access defaults to the GUI design view... but in this one application, the design view is pulling up queries in SQL mode first...
If I want to set it to the GUI design mode, I have to either hit design again or run the query then hit design to have it pull up in the regular design mode. Is there a setting or something I am missing? Seems that it only does this when the SQL code is reltively involved (i.e. the simpler queries pull up in the normal design view.
I have several subform tabs and one that I have updated no longer shows up when I run the form on Form mode. The tab is there but no content or details of the subform
I have an old db. I would like to see the forms and tables so I can select them and make changes. I know there is a way to access those items on startup but I have forgotten how. When I bring up the db I see a form and I can get into VB for that form but I cannot see any embedded table, forms reports, etc.
I have a form which was locked down (ie Allow additions, allow edit and allow deletions were all set to no) on the Form property. I want to change this to allow edits so in design mode I changed the Allow Edits property to Yes.
I then went to Form view and was able to update fields on the form. When I exited the form it asked me if I wanted to save the changes so I clicked yes.
On going back into the form I can no longer update the fields I could before. Going into design mode I can see that Allow edits on the form property is set to no again.
What is happening here. There is nothing in the on load or other events to set this to no, besides this shouldn't change the form property should it?
I know I can get around it my setting the property in the form itself but why is the property getting reset in the first place.
I have an application that when in design master shuts down when I attempt to open a certain form. I have traced the problem to a linked field from another table, by creating a new form and ommitting the said field, problem is its a required field!
If I copy the database (Backup) and run it as a replica it does not happen, if I then convert the replica to design master it happens.
I have a form/subform that requires some fields from the Switchboard for its criteria. Last week due to no action on my part, it started asking for those values as Parameters instead (Forms!Data!txtInspDate), etc! The new form loads, but of course it is blank with no data. This happens if I load it from a command button or from the navigation sidebar... UNLESS I first load the switchboard in Design Mode, go to VBA window, then reload the Switchboard in Form Mode and press the button. From then on until I close the database, everything works fine!
On occasion my access database all of a sudden begins to operate very slowly, I notice the queries take a much longer time to run.
Last week it suddenly began to operate very slowly after idling in excess of 30 mins
Today when I change the screen to design view and then back to form view once again it begins to operate very slowly. (yesterday was working fine on my desktop at home, I have not made any changes to the database with exception to saving and using the file this morning on the shared drive at work) If I shut down and re open it operates well but once again changing to design view causes the same issue. Sometimes compact and repair rectifies sometimes not.
I have a front end that is connected to three back end files. The front end is on my local computer while the back end files are on a network drive.
There are a lot of calculations that go into the queries and intermediate queries. For a report, I have based it on a UNION query.
But when trying to design the report it takes about 45 seconds just to do any one thing, e.g.; - Add Groupings - Add Grouping Headers//Footer, sorting option - Add bound textbox :eek:
Needless to say this is very annoying. :mad:
The union query itself runs fine (takes about 15 seconds to run) and returns about 12,000 Rows. The union query looks like this (I changed the field names to make it read easier, hopefully);
SELECT a1, a2, a3, a4, a5 FROM qry_A;
UNION SELECT ALL a1, b2 AS a2, b3 AS a3, a4, a5 FROM qry_B;
UNION SELECT ALL a1, c2 AS a2, c3 AS a3, a4, a5 FROM qry_C;
UNION SELECT ALL a1, d2 AS a2, a3, a4, a5 FROM qry_D;
UNION SELECT ALL a1, e2 AS a2, a3, a4, a5 FROM qry_E;
UNION SELECT ALL a1, f2AS a2, tblG.f3 AS a3, tblG.f4 AS a4, a5 FROM qry_F;
One solution I came across when searching the forums was to use an Append Query to append the query results to a table and base my report on that. This does indeed fix the problem.
But what I was wondering if it was is my query design that is causing it to be slow or is it just the fact that I am returning 12,000 rows? :confused:
In case it matters, I wanted to mention that I can’t use the report wizard to create the report. When I select the union query, the fields will be showed for awhile then they just disappear. That in and of itself doesn’t cause any trouble since I am creating the report using the design view and not the wizard.
I'm working on a login form and *not thinking* I removed access to the submenu. I had already set the form up so that you either login correctly or exit the program. I'm working in code for the form and I know it's possible to reactivate the menu in code, I just don't know how. And I can't switch to design mode. The form is set up to not allow anything to happen until the correct login has been entered, and I either had a typo when I set up my test login or the program is bugged and only says it's incorrect....
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
I'm working on an Access 2007 database and any changes I make to a form or control will not save. I click the save button, and try keyboard shortcuts but when I close the form I get a prompt "Do you want to save changes to <whatever>":
If I click "Yes" the changes still do not save and I when I close the form it starts the cycle over.If I click "No" the form closes and the changes are not saved (obviously).
There is no VBA in the project but I've still tried a recompile as suggested for older versions of Access but no luck. There are quite a few embedded macros but I'm not sure how they could be causing this.
I've got a DB where I need to simply change a few images on the form buttons. When I open the file I cannot access design view at all. There is no toolbar and the menu doesn't contain View, just file, edit etc. I can edit the properties of the records etc but this is all I can do. I thought maybe it had something to do with it being created in a different version of Access but still it will open in this way only. There is no messae telling me I'm locked from anything and I've tried ctrl + enter etc. I'm running Access 2003.
Does anyone know what's wrong? Its driving me nuts. :confused:
I'm trying to protect the frontend of my database from unwanted changes. How to disable the navigation pane, ribbon, and only display my navigation form. My navigation form has 10 buttons that open forms for data entry. I only want the users to be able to open forms and add data. I don't want them to be able to go to the navigation pane or anything other than the forms.
I have a sub form in DataSheet view and I would like to lock the design so that the User should not change the layout
Even when I set the property of the Datasheet “Allow Design Changes: Design View Only” users are able to unhide the hidden columns and they can also change the size of the column by dragging the column end line
Does any one know how to lock the design of datasheet ( I am using this sub form datasheet for data entry but do not want users to change the layout)
I'm going to make up names and values -- I'm interested in the structure.
Table ALPHA:
COLA DAT1 DAT2 DAT3 1 5 7 9 2 4 14 8
Table BETA:
COLA_IND DAT1 DAT2 DATN 1 a b c
Table CHARLIE:
COLA_IND DAT1 DAT2 2 d e
Table DELTA:
COLA_IND DAT1 DAT2 2 f g
Ok, the idea here is that the data in table ALPHA contains data with COLA a key such that selecting 2 would yield the data row "4, 14, 8."
Now, COLA_IND is a "COLA" key for table ALPHA (sorry, I can never remember which side is called the foreign key). So, from tables BETA, CHARLIE and DELTA, I can access any row in ALPHA based on the key "COLA_IND"
Here's the fun part. When I build my query, it wants to use an inner join on the keys from all these tables... In order words:
SELECT blah blah blah INNER JOIN blah ON (ALPHA.COLA=BETA.COLA_IND) AND (ALPHA.COLA=CHARLIE.COLA_IND) AND (ALPHA.COLA=DELTA.COLA_IND)
What I'm looking to do is expand BETA, CHARLIE and DELTA with the information from ALPHA based on the key COLA_IND. I don't think this is doing what I want.
I've got this problem and im 95% sure its going to need a query in order to achieve this answer im looking for.
I'm creating a Software Licensing Management db and its all working lovely. However my only problem remains is the graphical representation (text box within one of the forms) of howmany licenses are/aren't(doesnt matter if this number is a +/- number) available.
In order to achieve this answer I dont think you'l need the table structure of any of my tables other than these two:
However I would like to add a column to either of these tables named 'Availability' or something similar which will show the licenses available.
I've tried a number of Update/Append queries but all have failed. I want this Available running variable held within the table due to it not changing to much of the current db design as i baisically finished the project and they asked for it! any ideas of how to efficiently achieve this will be much appreciated!
I'm looking for a keyboard shortcut to expand the columns in "design view" of a query.
What I mean by this is rather than selecting all of the columns and double clicking to see the entire text, I'd like to be able to a shortcut.
The entire process as I see it involves 3 steps so I will need thesolution to the 3rd step.
(1) [ctrl+spacebar] to select initial column (2) [shift+arrows] to select all of the columns I need (3) [keyboard shortcut] will expand all of the columns "field" names to the size of the column heading
Alternatively, if you know of a shortcut that will expand the columns without having to select them first I'll take it!!
