[Last Call] Learn how to a build a cloud-first strategyRegister Now


AS400 table linked into Access 2010 where clause ignored

Posted on 2014-08-15
Medium Priority
Last Modified: 2014-08-23
I have a Access 2010 database that uses AS400 linked tables in queries. One field, defined as character; size 1 in AS400 is ignored in the Access where clause.  I can query on other character fields and it works fine. When I do a make table in Access and copy the AS400 table into a local table, I get the expected return from the where clause. This field has two values Y or P and I know it is case sensitive so I use caps (Y or P).  Has anyone had this happen before? Is there a way around it so I do not have to do a make table?

Question by:LeLeBrown
LVL 46

Expert Comment

by:Kent Olsen
ID: 40263827
Hi Le,

When one of our AS/400 gurus shows up, the first thing that they're going to ask is:

What version of Access are you running?
What version of DB2 are you running?
What version of OS/400 are you running?
What driver are you running from the system running Access?


Author Comment

ID: 40263863
MS Access is 2010
AS400 and DB2 is V5R4M0  I believe
I am not sure what you mean by driver... I have a ODBC connection to the AS400 to link to the tables. Not sure if that answers that question.
LVL 46

Expert Comment

by:Kent Olsen
ID: 40263891
What ODBC connector are you using?  Probably in "Data Sources" or "Machine Data Sources"
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40264219
Can you post the SQL you were using?
Try making a query from the as40 table like this:

SELECT YourField
FROM YourTable
WHERE cstr(YourField)="P"
LVL 46

Expert Comment

ID: 40266120
Are you writing pass-through queries or regular Access queries?

Author Comment

ID: 40266274
There is a pass-through query that create a table and the query I am trying to write is an Access query. The table returned from the pass-through query contains all records "P" and "Y" rows.
I will have to look at the ODBC data source at work. I am not sure if it is 64 or 32.

Accepted Solution

LeLeBrown earned 0 total points
ID: 40267574
I refreshed the linked AS400 table in Access and nothing changed.
I deleted the table and linked it again and now it is working.

Author Closing Comment

ID: 40280349
No other posts were suggestions on a fix. I found the solution on my own.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

829 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