Access bombing out when importing through ODBC from AS/400 Databas

[Guest]

Is anyone familar with Access bombing out when you try to import data through
ODBC from a remote database using a QUERY? It does NOT happen if I import
data WITHOUT using a query. In other words, if I double click on one of the
linked tables to open it up, it opens FINE. But if I use a query (in order
to filter massive amounts of data) the program consistently bombs out. I
"Link" to the remote database tables rather than importing the tables first
because of the massive amounts of data I would have to import. I have
checked the KB about this problem and it seems to be aware of the problem but
it says it was handled in Access SP3, which I already have. I use Access
2002 in an XP environment. The ODBC driver I use is the IBM ODBC driver
necessary to connect to the AS/400 system.
Thanks

 

[Joe Fallon]

I have worked with AS400 and Access in the distant past.
I used queries all the time.
So I would have to say there is something wrong with your query.
At least in terms of how the ODBC driver modifies it and sends it to the
AS400.

I recommend you go back to the beginning and do some testing to see the best
way to interact with the AS400.
In other words link to a simple table with only a handful of records.
Then open it in table view and ensure you can see all data in all
columns/rows.

Then write a simple query to return all data.
The add a WHERE clause for String data.
Then try numeric data.
Finally try date data. You may discover that the required date format is not
what you have been using.

Also, consider using SQL Pass Through queries - in that case ensure the
syntax is exactly what the DB Server expects.
e.g. Oracle, SQL Server, As400 could all use different syntax for the same
query. (Or in many cases now they could all be the same.)

 

[Guest]

Thanks Joe. Rather than try to figure out why it is bombing out, I started
using Pass-through queries and they work GREAT. In fact, I have another
question about pass-through queries I wrote last week. (Please see my post
entitled 'Importing Data from multiple remote databases"). Thank You.