Solved

Update query in MySQL crashing access, runs fine against MSSQL

Posted on 2013-06-20
4
566 Views
Last Modified: 2013-06-26
I am in the middle of trying to convert my first database from MSSQL to MySQL. The code in question is this:

Update Orders 
Set Paid=1 
Where 
(Select Sum(PaymentAmount) 
ordersFrom Payment 
Where Payment.InvoiceNumber=Orders.OrderNumber)>=InvoiceTotal

Open in new window


The objective is to mark all orders as paid if the customer has made sufficient payments. Payments are entered into a separate system and not directly onto the order form.

There are over 67000 payments in the payment  table, and over 70000 orders in the order table.

When running the code in an Access ADP file, in Access 2010 against the MSSQL server, it runs fine. When running the code in MySQL Workbench, the code runs fine. When running the code in an Access 2010 MDB file against the MySQL server, Access crashes with the following error in the Windows 7 64bit workstation event log:

Faulting application name: MSACCESS.EXE, version: 14.0.6024.1000, time stamp: 0x4d83e4fc
Faulting module name: ntdll.dll, version: 6.1.7601.17725, time stamp: 0x4ec49b8f
Exception code: 0xc0000374
Fault offset: 0x000ce6c3
Faulting process id: 0x878
Faulting application start time: 0x01ce6dcd7aa3a1a4
Faulting application path: C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE
Faulting module path: C:\Windows\SysWOW64\ntdll.dll
Report Id: c60ea7c2-d9c0-11e2-9dbb-000a3a56e2a7

Open in new window


The MySQL instance is 5.6.11 and the MSSQL instance is 2012 express. The server is a C2D 6600 2.4Ghz with 6GB RAM box.

Suggestions?
0
Comment
Question by:AMPLECOMPUTERS
  • 3
4 Comments
 
LVL 15

Assisted Solution

by:gplana
gplana earned 100 total points
ID: 39263089
Try this one:

Update Orders 
Set Paid=1 
Where InvoiceTotal <= 
(Select Sum(PaymentAmount)  orders
From Payment 
Where Payment.InvoiceNumber=Orders.OrderNumber);

Open in new window


Is it failing too?
0
 

Author Comment

by:AMPLECOMPUTERS
ID: 39263490
Running that code gives me a syntax error in Access, it says:

Run-time error '3075':

Syntax error in query expression 'InvoiceTotal<=(Select Sum(PaymentAmount) orders from Payment...blah blah blah.

If I remove the word "orders" from line 4, then it crashes Access just like my code did. It runs either way just fine from the Workbench.
0
 

Accepted Solution

by:
AMPLECOMPUTERS earned 0 total points
ID: 39266466
I have no idea what happened, but I had another place in the database with the same code, and it ran fine. I copies and pasted it into the form that was blowing up, still runs fine. I think the form was corrupt somehow so just to be sure I did a decompile and recompile.

Regardless, the problem is solved.
0
 

Author Closing Comment

by:AMPLECOMPUTERS
ID: 39277391
I will award 100 for trying, at least it made me really look hard a the database which did lead to a solution.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running sum query 6 33
DBF to ... Converter 5 43
Need more help autopopluating a number field 17 34
Set focus on next field when character count = 5 9 12
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
By default the complete memory dump option is disabled in windows . If we want to enable the complete memory dump for a diagnostic purpose, we have a solution for it. here we are using the registry method to enable this.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now