Access (2013) crashes when using Query Design: workaround

Access (2013) consistently crashes when using Query Design. I have discovered a workaround:

Always save and close the Query you are editing before running it. Never run it directly from Query Design (the Run! menu item or Datasheet view).

A little bit of a hassle, but a lot better than crashing your whole project!

The one place this doesn't work is if you want to preview the results of an Append Query before actually doing an Append. To do this, you either have to take your chances by peeking at it in Datasheet view, or convert the Append Query back to a Select Query, run it (as above) to see the results, and then convert it back to an Append Query and run it again (as above).

Now this would be great (sort of) if Append Queries always ran with the same results as their corresponding Select Queries. However, I have discovered that that is not always the case. I have posted a separate thread about this issue, "Calculated Field Expressions work in Select Query but not as Append Query" (showthread.php?p=1230863).

I hope this helps somebody, and that somebody can help me with that other problem.

Thanks!


Post your answer or comment

comments powered by Disqus
Is anyone else having problems with Access 2007 crashing when creating a pivot table?

I try to create the pivot, and it creates, but it says the details can't be displayed and then it crashes.

Can someone tell me if they have had this problem in the past and how to fix it?

Could it possibly be my if statements within my query?

I have a database with 4 separate forms. In one form I have a control that is an option group with 5 different options. I run the following code on the option group:
Private Sub A5_Click()
If Me.A5.Value = 8 Then
Me.A5a.Visible = True
Else
Me.A5a.Visible = False
End If
If Me.A5.Value = 9 Then
Dim Response As Integer

Response = MsgBox("This will clear the text box as well. Do you want to continue?", vbYesNo + vbCritical + vbDefaultButton2, "Clear values?")

If Response = vbYes Then
Me.A5a.Value = Null
Me.A5a.Visible = False
Else
Me.A5.Value = 8
Me.A5a.Visible = True
End
End If
End If
End Sub

When I run the code in Access 2003, the procedure works as stated. However, Access 2007 crashes as soon as I choose any option in the control. Any suggestions?

Hi,

The access database crashes and creates a backup when the following function is called in the ConvertToPDF function of Lebans code.

blRet = ConvertUncompressedSnapshot(mUncompressedSnapFile, sOutFile, _
CompressionLevel, PasswordOwner, PasswordOpen, PasswordRestrictions, PDFNoFontEmbedding)

Does anybody has an idea why this happens?

Thanks.

Hi,
I have a form with a sub form datasheet. The subform has conditional formatting, and click event code that opens related forms. A very specific sequence of events always causes Access to crash ("microsoft access has encountered a problem...etc".)
1) apply a filter to the subform (using the built in right click menu)
2) click a record to open a related form.
3) close the related form. The "Close" sub routine includes code that refreshes the original form
4) during the refresh, Access crashes.
It only crashes if you have applied a filter.
Removing the "refresh" code prevents the problem, but is unfortunately vital to the functionality.
I have found a KB and hotfix that seems to be an exact match (KB888994), but is for the wrong version. The hotfix is specific to Access 2002. I am using Access 2003 with SP3. I'm running XP, with SP3.
The database is Access 2000 format; I have tried converting it to Access 2003, but the same problem remained.

Any suggestions?
Thanks,
Nicholas

i have a field from a table that says clientid, and also workerid from another table. now, a scheduletable was created,

when i try to make a report, i want to group all of the details pertaining to that client, as well as if i want it the other way around. how do i accomplish this?

my access keeps crashing when i try to do the right click group on thing. @_@?

hello

just want to ask if it is possible to create a data entry form from a query.... well you see i have to tables [owner] & [property] that i've join using query design... is it possible??? if yes.... can anybody give details how to do this..

thanks in advance....

When I run my query access crashes. If I remove the SuperPrice select statement it works fine. I'm using Windows7 Ultimate 64, Access 2007 SP2. Any help would be appreciated.

SELECT Parts.*, DealerParts.Bin, DealerParts.Note, (SELECT RetailPrice FROM Parts P WHERE P.PartNumber=Parts.SuperceededBy) AS SuperPrice
FROM Parts LEFT JOIN DealerParts ON Parts.PartNumber=DealerParts.PartNumber
WHERE DateDiff('d',parts.dateadded,Now())

Greetings: I run a referral nonprofit (mostly for support groups) and have constructed a db in Access. Now I need to take the data in the db and convert it into HTML for our new website.

There are 102 categories; each support group fits into one or more categories; when a client requests information, he/she is sent to the groups in one category. I have constructed a query so that I can put together a list of paragraphs for each category showing details about each group in that category. It operates by iterating through each row and concatenating paragraphs for each group in that category. Unfortunately, when I try to open the recordset for that query and make the strSQL the same as the SQL produced by the query designer, I get a vague error message requesting a label, statement, or end of statement. For starters here's the DAO approach with the SQL --

Code: Private Sub cmdDesignSearchResultsHTML_Click() ' This sub iterates qryWebAllCatsTheirGroupsTheirPublicContacts for which groups belong in each category and which contacts belong to each group to develop the HTML for ' the search results page of the web site. ' Bring in an array of all categories 3 digit numbers to use in the loop Dim CatNumberList(1 To 200) As Integer Dim db As Database Dim strSQL As String Set db = CurrentDb strSQL = "SELECT tblCategories.OurNumber, tblCategories.ID_Category, tblCategories.NameInAccess FROM tblCategories ORDER BY tblCategories.OurNumber" Set rs = db.OpenRecordset(strSQL) ' Check for no categories found in list in categories -- just here to prevent program from crashing in weird situation If Not rs.EOF Then rs.MoveFirst Else: MsgBox "No category numbers found -- very strange. Please investigate.", vbOKOnly + vbCritical, "Something's very wrong!" Exit Sub End If ' Loop through SQL from qrySortCatOurNumberbyOurNumber to assign each 3 digit category number to the CatNumberList array rec = 0 Do While Not rs.EOF rec = rec + 1 CatNumberList(rec) = rs!OurNumber rs.MoveNext Loop ' Close everything for a reiteration rs.Close Set rs = Nothing Set db = Nothing rec = 0 ' SQL from the qryWeballCatsTheirGroupsTheirPublicContacts copied here from design wizard's SQL page -- something's rotten in Denmark. SELECT tblCategories.ID_Category, tblCategories.OurNumber, tblGroup_Contact_Join.Status, tblGroup_Contact_Join.GroupID, tblGroups.DisplayPosition, tblGroups.GroupName, tblGroups.Description, tblContacts.ID_Contacts, tblContacts.LastName, tblContacts.FirstName, tblContacts.Status, tblContactPhonePublic.Prelude, tblContactPhonePublic.PhoneNum, tblContactPhonePublic.PhoneExt, tblContactPhonePublic.Notes, tblContacts.ConfEmail, tblGroups.WSW_URL FROM (tblGroups INNER JOIN (tblCategories INNER JOIN tblGroup_Category_Join ON tblCategories.ID_Category = tblGroup_Category_Join.CategoryID) ON tblGroups.ID_Group = tblGroup_Category_Join.GroupID) INNER JOIN ((tblContacts INNER JOIN tblContactPhonePublic ON tblContacts.ID_Contacts = tblContactPhonePublic.RefNum) INNER JOIN tblGroup_Contact_Join ON tblContacts.ID_Contacts = tblGroup_Contact_Join.ContactID) ON tblGroups.ID_Group = tblGroup_Contact_Join.GroupID WHERE (((tblGroup_Contact_Join.Status)"Inactive")) ORDER BY tblCategories.OurNumber, tblGroups.DisplayPosition DESC , tblGroups.GroupName; .

I did some obvious things (running it first rather than second, changing the " to ' or "") but to no avail. Alas.

I'm new here and couldn't find a way to post a jpg screen shot of the query design, but when I plugged this SQL into a blank SQL screen for Access, poof, it worked perfectly and backwardsly created the same query structure I put forth in the designer. Hmmm...

Thank you for kind consideration -- and any suggestions where I have unfortunately and unintentionally broken protocol.

Dr.C.

I am taking a college class and need to use Access 2007.

I recently installed Office 2007 Ultimate purchased from Digital River.

Whenever I use any of the wizards, Access 2007 crashes, even when I try to set Referential Integrity for table relationships.

All wizards seem to crash, queries, forms, reports, etc. All of the other office applications seem to work just fine.

I have Windows XP Professional SP3, on a Dell Dimension 8300 (five years old) with a P4 processor and 1 GB RAM.

Any ideas would be very welcome.

I am helping someone with an Access 2003 db.
She is trying to set up a query using the Access Query Wizard/Designer.
The query requires an INNER JOIN between tables.
I cannot figure out where to put the INNER JOIN parameter in the query designer which is used to design and execute queries INSIDE the Access User Environment (as opposed to VBA code or external db).

I am really hoping that someone can help with this.
She does not understand straight SQL queries and relies on Access's Query Builder/Wizard/Designer to set up her queries.
I figure if I can set up a few examples, it will be very helpful.
When I refer to the Access Query Builder/Wizard/Designer, I mean the screen in Access that shows the following components of a query:
Field:
Table:
Sort:
Show:
Criteria:
or:

I just need to know where/how to include an INNER JOIN clause.

Here is the SQL Query:

SELECT TransactionID, EventID
FROM TransactionHistory
INNER JOIN Events
ON TransactionHistory.EventID = Events.EventID


So I guess I am trying to map the SQL statement above to the Query Builder UI (screen fragment shown below):
Field: EventID
Table: Events
Sort:
Show: [X]
Criteria:
or:[/b]


I appreciate any help that can be provided.
Thanks,
Bill

hello all !
first i'm new here and i hope this is the correct place to put this thread
well,
when i execute my query from ado.net object it works with col LIKE "%" & @param & "%"
but when i copy this to access query designer it dosen't work till i replace % with * charactere
can any one explain to me why ?
and another question
why this query doesn't work :

	Code:
	select NumArticle From article WHERE texte LIKE "*" & @srchtext & "*" AND idtype IN (SELECT IdType FROM TYPE WHERE Libelle
=@clas)

and when i remove

	Code:
	AND idtype IN (SELECT IdType FROM TYPE WHERE Libelle =@clas

it works
knowing that the values given are correct and respect the relation(idtype of table article is the same returned by "SELECT IdType FROM TYPE WHERE Libelle =@clas" when executed alone)
hope i'm clear enough !
thank you all

Hi All,
I have coded a database that is being used by approx 50 users. One of my users hooked his laptop up to a projector and when he pressed Function F10 to put the picture on the screen it crashed Access. Tried this a couple of times on his laptop and the same crash. Re-installed the database...still crashed.
Has anyone experienced this, or know of a fix. Tried googling it, but can't find another occurance?
Cheers
MidgeY2K

OK here is my situation I have an Access Database that I store demographic and financial information about a client. I then have several queries that will export the data into CSV files saved into a folder with the clients name.

I then have the below code that will look in a folder for the specific word document the user wants to print and the folder the CSV file is stored. I merge the two and save the file in the folder for the client. This works for the most part but on occasions I get and error that word has to be restarted. The Module Name is mso.dll (Version 10.6626.0) I have read that I need to make sure the ~filename.doc files need to be deleted, which I have done, that did not seem to help.

If I open word and open the file I saved it opens just file, I only get the crash when I am creating the file, or when I close the file.

Any ideas?

myView is passed when the user click on the button. 2 is for print preview and 1 is to send it to the printer.

Here is my Code:


	Code:
	myDocumentPath = "R:SBA Documents"
 
 'Get the SBA Name
    myFolderName = Me.cmbLoan.Column(1)
    'Remove any special characters and replce with an
    'underscore _ also change to uppercase
        myFolderName = Replace(myFolderName, ",", "")
        myFolderName = Replace(myFolderName, ".", "")
        myFolderName = Replace(myFolderName, "&", "")
        myFolderName = Replace(myFolderName, "-", "_")
        myFolderName = Replace(myFolderName, "  ", " ")
        myFolderName = Replace(myFolderName, " ", "_")
        myFolderName = UCase(myFolderName)
 
    'Determine if it exists
    If Dir(myDocumentPath & "" & myFolderName, vbDirectory) = "" Then
        MkDir myDocumentPath & "" & myFolderName
    End If
 
'504 Eligibility ChecklistA
 
If Me.ckbEligibility1 = -1 Then
    myDocName = myDocumentPath & "" & myFolderName & "504 Eligibility ChecklistA.doc"
    myDataSource = myDocumentPath & "" & myFolderName & "tmp_Submission.csv"
    myTemplateName = myTemplatePath & "504 Eligibility ChecklistA.doc"
      Set oApp = GetObject(myTemplateName, "Word.Document")
          'oApp.Application.Visible = True
          oApp.MailMerge.OpenDataSource Name:=myDataSource, LinktoSource:=True, AddToRecentFiles:=False
          oApp.MailMerge.Destination = wdSendToNewDocument
          oApp.MailMerge.SuppressBlankLines = True
          oApp.MailMerge.Execute
 
          oApp.Application.Documents(1).SaveAs (myDocName)
 
          If myView = 2 Then
            oApp.Application.Visible = True
            oApp.Close SaveChanges:=wdDoSaveChanges
          Else
            oApp.Application.ActiveDocument.PrintOut
            oApp.Application.Documents(1).Close
          End If
 
          Set oApp = Nothing
End If



I've recently been experimenting with SQL 2005 Express for when we come to move our main db out of access and onto a SQL backend..

I've created a few of our main tables in access and transferred some data into it, im now trying to re-create one of our main queries utilising the SQL tables, im running into an issue though and I have no idea what it means !

Here is the query..


	Code:
	SELECT dbo_PPIN_Physical_PINS.PPIN, dbo_PPIN_Physical_PINS.PPIN_IX, dbo_PPIN_Physical_PINS.PPIN_RoHS,
dbo_PPIN_Physical_PINS.PPIN_Manu_Series, dbo_PPIN_Physical_PINS.PPIN_Part_Number, dbo_SDX_Supplier_Details.SDX_Short_Name,
MRU_PIN_Finder_Sub_Query.Disty, MRU_PIN_Finder_Sub_Query.OPIN_IX, MRU_PIN_Finder_Sub_Query.OPIN_Disty_PN,
dbo_SDX_Supplier_Details.SDX_ID
FROM (dbo_PPIN_Physical_PINS LEFT JOIN MRU_PIN_Finder_Sub_Query ON dbo_PPIN_Physical_PINS.PPIN_IX =
MRU_PIN_Finder_Sub_Query.OPIN_PPIN_IX) INNER JOIN dbo_SDX_Supplier_Details ON dbo_PPIN_Physical_PINS.PPIN_Manu_ID =
dbo_SDX_Supplier_Details.SDX_ID;

I have created this using Query Designer and it appears to be a carbon copy of the one in the original db which is using access tables.

I am getting the following error..


	Code:
	[Microsoft][ODBC SQL Server Driver][SQL Server]The multi part identifier "MS1.SDX_ID" could not be bound. (#4104)

Anyone able to shed some light on this?

Hello my dear experts,
I have designed a database application in Access 2007, and I always knew I would need to share it with some users that have Access 2000 or 2003, now, because of that I kept the .mdb extension, the application works pretty good in my computer as well as in others with Access 2007, however when I try to open it in an Access 2000/2003 environment I get the following message:
"This database is in an unrecognized format.
The database may have been created in a later version of access than the one you are using. Upgrade your version of MS access to the current one, the open this database."

At first I though it could have been a library reference problem, so it occurred to me to export form by form and table by table to another database (just a few, not all of them since I was just experimenting) it did open that time, but I got the following error when opening a form:

"The expression On load you entered as the event property setting bla bla bla,..."
then I thought,"Excellent, it is the reference of the libraries" I made the changes to the corresponding one, but didn't work, I kept getting the same error... I am now stuck and I really need to have this app ready to be used in earlier versions of access than 2007, PLEASE HELP!

Hi, Big Jim here:

I am really not sure where to ask this one.....

My boss and I are in a jam. We have been using Access to run a reporting process, but one of our tables will exceed the maximum fields allotted this month. Our thought, dump the table into SQL Server and use the GUI interface provided in Access Projects.

Unfortunately, the query designer seems to have a few drawbacks. The one that effects us the most is in using UPDATE queries where more than one table is used to determine records to be updated. In attempting it, we get the message: "The designer does not graphically support the Optional FROM clause SQL construct".

Now I know we can manually create Update Queries, but we often need 1,000+ in a short period of time. Manually punching in all the fields involved and other code just isn't timely.

Question: Is there some alternative, service pack or anything else that would allow us to graphically create these Update Queries using Access Projects or even SQL Server 7.0? I would hate to have to scrap all the work we did over something that seems so minor.

Thanks in advance!

Big Jim


Set Up:
Windows XP
Office XP
SQL Server 7.0

Hi all

I am putting together a quick and dirty db to hold just a few thousand bits of data for some tests I’m about to start.

For various reasons it is better to hold some of these in Excel, and to link Access to Excel.

The problem I have is that when I use Access to edit a “record” in Excel, it will work for the first one or two records, but then Access crashes and I get the very familiar “Sorry for the inconvenience, please send this error file to uSoft blah blah blah” (which I always do, but wonder if anyone ever takes any notice of them!).

The problem occurs whether the spreadsheet is open in the background, or is just sitting closed in its directory with Excel not running.

At the moment, I only have a tiny amount of data as I’m still putting it together, so it’s not a “size” issue; I first noticed the problem with only about ten records!

I expect I’ll move everything to Access (not ideal but hopefully it wont crash quite so often), but I’m curious to know if any of you have actually made a successful link between Access and Excel (and used it for editing data, not just looking, which is fine).

My system: Office 2003 pro/developer, Win XP pro.

Thanks for any thoughts

Skeletal

Hi there,

I am hoping to have this Access 2003 front-end linked up to a SQL server which stores the back-end table.

What I am wondering and I really hope it works this way is.......if users can access the standard query grid through Access front-end with the tables now stored on the back-end on the server.

Will users still be able to use the query grid to do query as they normally would in Access 2003's query design view?

I am new in this arena, and am hoping that I can get some input from experts.

Thank you

Hi everybody!

I have a problem that is way past my understanding of access at the moment. I will do my best to explain and hope somebody has any ideas!

I have a form ProjectManager which is based on the table tblProjects and tblDeliverables (one project can have many deliverables). The subform is based on tblDeliverables. These two are working well together. BUT, ofcourse I want to include som fields in the subform together with tblDeliverables from another table tblWeekly, where I can sum up some used hours. This I had to solve by using a "make table" query as I was not able to update values in the subform when it was a normal query. Anyways, I got this to work.

But now (After thinking I actually solved this) i cannot enter any new records in the subform tblDeliverables without it giving me the errormessage that i also have to put a value in the projects field! (This does not come up when the form is based only on tblDeliverables).

So it seems that when i base the subform on a "make table" query, the connection with the main form is lost? Anybody know a work around for this?
I've uploaded some images, hope you understand my question!

Hi there,

I have a large Access database and some of the queries are based on other queries (sql or other).

Now when I try and view the design mode of some of these queries, it takes an astonishingly long time as it appears that Access runs the child query contained before it allows the 'parent' query to show in design mode.

I can kind of see why it does it - but is there any other way of quickly viewing a query in design mode without Access first running the other queries it's based upon?

(NB: Someone said in the 'old days'(!) Access used to have this feature, but it is no longer there?!?!?!)

Thanks

I have made a couple of posts about this and had no response as yet. Maybe i didn't explain it well.

I have a form that logs emails. The emails are either "to" or "from" a person. I want to have two queries that pick out either to or from. Now each person i know sends me several emails over time obviously but i am only concerned with their last email or MY last email to THEM because that determines if i should email them back or if i am waiting for them to contact me. At the moment i am having problems filtering out the last record for each person which determines whether i need to email them or they need to email me. I also use the record to log when the last mobile text was sent, phone call etc so it is not just emails. The following is what my formsfields look like

CommunicationID
Communication type
TO or From
Date
Day (automatically taken from the date)
Subject

I cannot for the life of me get the filter to work properly. Currently i am either getting more than one record for each person or the results are jumbled up. I really need a better understanding of how to use the totals thingy in the querys design view. I'd really appreciate some help on this. Thanks....Ross

Hello,

I wonder if anyone can help me. I have a problem which I think is query related. I have a query which reads the date from a form text box (which works OK in other queries). For some reason when this query runs, it crashes Access and makes a backup of the database. Can anyone see where Im going wrong? Thanks (SQL below).


	Code:
	SELECT dbo_MG_BILL_OF_LADING.CREATE_DT, dbo_MG_BILL_OF_LADING.BL_NUM, dbo_MG_BILL_OF_LADING.BL_ID,
dbo_MG_BL_ITINERARY.BL_ITINERARY_ID, dbo_MG_BL_ITINERARY.SERVICE_CD, dbo_MG_BILL_OF_LADING.TRADE_CD,
dbo_MG_BILL_OF_LADING.POR_LOCATION_CD, dbo_MG_BILL_OF_LADING.PDL_LOCATION_CD INTO DB_MOTHER_DECISION_TRUNK
FROM ((((dbo_MG_LOCATION INNER JOIN (dbo_MG_BILL_OF_LADING INNER JOIN dbo_MG_BL_ITINERARY ON dbo_MG_BILL_OF_LADING.BL_ID =
dbo_MG_BL_ITINERARY.BL_ID) ON dbo_MG_LOCATION.LOCATION_CD = dbo_MG_BL_ITINERARY.FROM_LOCATION_CD) INNER JOIN dbo_MG_LOCATION
AS dbo_MG_LOCATION_1 ON dbo_MG_BL_ITINERARY.TO_LOCATION_CD = dbo_MG_LOCATION_1.LOCATION_CD) INNER JOIN dbo_MG_SERVICE ON
dbo_MG_BL_ITINERARY.SERVICE_CD = dbo_MG_SERVICE.SERVICE_CD) INNER JOIN dbo_MG_LOCATION AS dbo_MG_LOCATION_2 ON
dbo_MG_BILL_OF_LADING.POR_LOCATION_CD = dbo_MG_LOCATION_2.LOCATION_CD) INNER JOIN dbo_MG_LOCATION AS dbo_MG_LOCATION_3 ON
dbo_MG_BILL_OF_LADING.PDL_LOCATION_CD = dbo_MG_LOCATION_3.LOCATION_CD
WHERE (((dbo_MG_BILL_OF_LADING.CREATE_DT)>=[Forms]![TS_FRT]![Date]) AND ((dbo_MG_BILL_OF_LADING.TRADE_CD)="ASAEUR") AND
((dbo_MG_SERVICE.VESSEL_TYPE_CD)="CONT") AND ((dbo_MG_LOCATION.CONTINENT_CD)="ASA") AND
((dbo_MG_LOCATION_1.CONTINENT_CD)="EUR") AND ((dbo_MG_BILL_OF_LADING.DELETED_FLG)="N"));



Hello members...

I have a unique dilemma; the database is made to open without any menus/toolbars visible and i have developed shortcut menus for reports and forms.
I had to make some queries accessible via buttons to users to be able to modify the design and view data for printing purposes etc.

Problem is, when the database is opened in a locked/secure state users do not have design access, they may open the queries but cannot view their results etc. Is there anyway to make the query design (default) menu/toolbar visible when they are in query design only?

thanks in advance!

Regards, Martin

Hi there!

Hope someone can help me with this - I'm using Access 2002 and when I do a new query and add a table, I often can't get select all the fields by double-clicking on the top of the table - it just doesn't respond at all.

The problem resolves itself if I maximize the window and then restore to normal size again. After that double-clicking selects all the fields in the table as you would expect.

This is so weird - I never used to have it and I've made sure I've installed the latest service pack, but it didn't help.

I know it's not the mouse as I can use the maximize workaround (and I tried a USB mouse instead of my PS2 one, and it didn't help).

Anyone got any ideas? - it's got to be something really daft.....


Not finding an answer? Try a Google search.