Query Crashing Access

[JonReedTDC]

Well hello again!



My IT guy and myself (neither an Access guru) are stumped with an simple action that ends up crashing Access.

Below is a screenshot of how I have the Query connected.

A little background:
- The tables are externally linked through and OBDC connection to an AS400/IBMi server
- Each table is linked through the OrderNumber (except IESFILE_CUSTMAST which is through customer code and just brings in the customers full name)

At this point I can run the query without issue and it pulls in all the data I need. However I have tried to do a few simple things that end up crashing the Access (AKA it becomes non responsive)

1. When I put a criteria on the date field (second column) the Access will become non responsive and crash. I tried doing >1/1/16 and >#1/1/16# and both just crash Access.
2. When I create a second query (which summarizes miles drive by OrderNumber) and link it to the Test 3 query then run the query it will crash Access.

Any ideas why my Access is crashing whenever I try to add criteria or link another query to it?

Thanks Everyone~!
Jon

[JoeM]

Can you do a Pass-Through Query? Those are usually more efficient, as all the "heavy lifting" is usually done on the server side, and just the results are brought back.
See: https://support.microsoft.com/en-us/kb/303968

[JonReedTDC]

Well, it looks like I have to create the query through SQL code... My skill set isn't advance enough in Access to build a query in that manner.

[JoeM]

Well, it looks like I have to create the query through SQL code... My skill set isn't advance enough in Access to build a query in that manner.

Actually, you don't!
If you just switch your current query to SQL View, you will get the SQL code of query.
There are sometimes some subtle differences between Access SQL and SQL, but we can help you sort those out, if you run into issues.

[InsuranceGuy]

Well hello again!

1. When I put a criteria on the date field (second column) the Access will become non responsive and crash. I tried doing >1/1/16 and >#1/1/16# and both just crash Access.
2. When I create a second query (which summarizes miles drive by OrderNumber) and link it to the Test 3 query then run the query it will crash Access.

A few things:
Don't call the field [Date]. It's a reserved word and can cause problems.
Next, don't use DateSerial. Every time any row is being returned, it's going through a VBA function before being displayed. That's likely causing your responsiveness issues.
iSeries is much more forgiving at dealing with missing data than Access & the DateSerial function is.

Show the full definition of your Date: DateSerial(...) function here, and I'll see if I can help you.


Jeff