Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-17-2019, 09:47 AM   #1
Djblois
Newly Registered User
 
Join Date: Jan 2009
Posts: 592
Thanks: 0
Thanked 0 Times in 0 Posts
Djblois is on a distinguished road
Pass through Query to access AS400

I am accessing an AS400 database using the IBM iSeries Driver. It has worked for every table I need. However, now I am trying to access a table and it is giving me this error:


"The Operation Failed. There are too many indexes on table 'PWRDTA_HHHORDHP'. Delete some of the indexes on the table and try the operation again."


Since this is our 3rd party POS software, I have no ability to edit the # of indexes. So I needed to look for a solution. I found Pass Through Queries. http://www.kb.blackbaud.co.uk/articles/Article/52445


However, I cannot figure out how to do the connection String for the AS400. I keep getting this error for the Pass through query:


"ODBC--call failed. [IBM][System i Access ODBC Driver][DB2 for i5/OSJSQL0104 - Token ; was not valid tokens: <END-OF-STATEMENT>. (#-104).


What do I need to do to get the pass through working with the AS400?

Djblois is offline   Reply With Quote
Old 06-17-2019, 09:56 AM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,472
Thanks: 58
Thanked 1,416 Times in 1,397 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Pass through Query to access AS400

Hi. Just a guess but have you tried the same connection string you used for your linked tables in your passthru query?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-17-2019, 11:25 AM   #3
Djblois
Newly Registered User
 
Join Date: Jan 2009
Posts: 592
Thanks: 0
Thanked 0 Times in 0 Posts
Djblois is on a distinguished road
Re: Pass through Query to access AS400

I did try to go into the linked table manager and copy what it shows there for a connection string.

Djblois is offline   Reply With Quote
Old 06-17-2019, 11:46 AM   #4
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,472
Thanks: 58
Thanked 1,416 Times in 1,397 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Pass through Query to access AS400

Quote:
Originally Posted by Djblois View Post
I did try to go into the linked table manager and copy what it shows there for a connection string.
Hmm, if it didn't work, maybe try to copy the one from the MSysObjects table.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-17-2019, 12:54 PM   #5
Djblois
Newly Registered User
 
Join Date: Jan 2009
Posts: 592
Thanks: 0
Thanked 0 Times in 0 Posts
Djblois is on a distinguished road
Re: Pass through Query to access AS400

Quote:
Originally Posted by theDBguy View Post
Hmm, if it didn't work, maybe try to copy the one from the MSysObjects table.

I just checked and it is the same Connection string as in the Linked Table Manager.
Djblois is offline   Reply With Quote
Old 06-17-2019, 08:05 PM   #6
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,026
Thanks: 455
Thanked 302 Times in 261 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Pass through Query to access AS400

Im sure you have already been to this site, but just in case:

https://www.connectionstrings.com/as-400/

There is even a link to an AS400 Q/A forum...may be able to find some answers there.
__________________
It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious. - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 06-17-2019, 10:04 PM   #7
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,707
Thanks: 105
Thanked 1,510 Times in 1,423 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Pass through Query to access AS400

When Access can't link to a table I make a View that it doesn't choke on.


Galaxiom is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pass Data from MS Access to AS400 Alhakeem1977 Modules & VBA 19 06-13-2019 09:57 AM
Pass text box value to Access Pass Thru Query bconner Modules & VBA 3 02-16-2012 01:41 PM
Create DSN for AS400(iSeries) in Access on the fly VegaLA Modules & VBA 2 01-26-2011 09:50 AM
Access - AS400 Connection Timeout? billyboy General 0 12-30-2003 08:32 AM
[SOLVED] Access for AS400 files... poolban Reports 3 12-27-2001 07:26 AM




All times are GMT -8. The time now is 04:04 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World