Connection to AS400 with ODBC (1 Viewer)

[a2knovice]

Ok, here are some questions:
A) Where does this code go, in the Click event?
B) How do I actually execute the query?
C) When does the Append Query come into play?
D) What is the purpose of the Pass Through Query; why not just modify and execute the Append Query?
E) I don't want to "hard code" anything, I want to use the Item Number field on the form in the WHERE clause of the SQL statement. You know, a user enters an Item Number into a field on a form; the SQL fetches the data from the AS400 based on the Item Number entered, and populates fields(columns) in the appropriate Access table. Simple and basic.

 

[KeithG]

Can you post your DB? It hard and will take a long time to explain when you don't have the basic under standing of Access VBA objects.

 

[a2knovice]

Sorry, KeithG, I can't post it for the very reason you stated earlier.

But in very simplistic terms, think of it as a table with two columns, Item Number and Description. There is a form with an Item Number field and a button. When an Item Number is entered, I want to get the description from the Item master on the AS400 and populate the table with the description. That's all. How difficult can this be? My AS400 files are linked; I have an Append Query that works like a charm when I "hard code" an Item Number in the WHERE clause. Now I just need to know how to use the Item Number field on the form inplaceof the "hard coded" Item Number in the WHERE clause, and how to execute the Append Query on the click event.

Now in real life I'll be fetching many more data elements from the AS400, but once I get the basics worked out I'll modify the syntax.

 

[KeithG]

I understand what you have to do the problem is you do not how to do it. When does this project need to be complete? Can you tell me the table name name and field names in AS400 so I can post an example for you. Or if you can give me the SQL statement from the pass through query and the table name you are trying to append to that would be great.

 

[a2knovice]

Ok, here are the two Queries:

Pass Through:
SELECT AS400LIB_FileNm.FLDA, AS400LIB_FileNm.FLDB
FROM AS400LIB_FileNm
WHERE (((AS400LIB_FileNm.FLDA)="1447"));

Append:
INSERT INTO tblTEST ( ItmNbr, [Desc] )
SELECT AS400LIB_FileNm.FLDA, AS400LIB_FileNm.FLDB
FROM AS400LIB_FileNm
WHERE (((AS400LIB_FileNm.FLDA)="1447"));

When I manually run the Append query, it works great. I need to replace the "1447" with the Item Number field name on the form, and I need to execute the query on a click event.

 

[KeithG]

Okay, here is something I quickly made for example. You will notice that when you open up the form you can enter a client ID and then when you hit the button it modifies the SQL statement in the pass through query then executes the append query based off of the passthrough query. I didn't get to test to make sure it working becuase I can not connect to you AS400 but this should give you a good idea.

After working on this I believe that linking the AS400 table to you database would be the wat to go for you. I explined the process in one of my other posts

 

[a2knovice]

Good morning, KeithG --

Thank you very much for the code samples. After applying your code snippets to my database and changing the Library, File and Field names to the actual names, the module keeps aborting on the "Set qryDef" statement with a run-time error '3265': Item not found in this collection. Any ideas?

 

[KeithG]

Did you use a valid query name in the Set qryDef line. It sounds like Access can not find the query.

 

[a2knovice]

Ok, I changed the query name in the Set qryDef statement to the Pass Through query. The module appeared to have worked - no errors - but the table was not updated with data.

When does the Append Query statement get changed with the variable from the form? I do not see that anywhere in the syntax you were kind enough to send me. You show the Pass Through Query statement change, but not the Append Query. As I see it, the append query is still using the hard coded parameter. I purposely used an invalid item number hard coded in the Pass Through and Append queries to test that the code in the module was using the variable from the form.

 

[KeithG]

After review the sample db a found a few errors so I am reposting it. When click the button on the form the passthrough query will be updated and the Append query will run appending the record to the database table.

One of my errors was with the append query, there should not be any criteria for the query at all because it is just taking the record from the passthrough query and putting it in tblTest.

 

[a2knovice]

KeithG,

You are have been more than patient; thank you! We have achieved some success -- the table is being populated with data, but 29,631 records worth. Instead of fetching only the item required, it populates the table with every record in my AS400 file. I used your zipped example and changed the names to reflect my actual names and ran the code as you have it. How can we get it to just fetch the record needed?

 

[KeithG]

There must be a problem with the SQL statement in the Pass Through query try replacing the ' qoutes aroung the client ID with " to see if that helps. Let me know.

 

[KeithG]

Does the pass through query produce more than one record? It is hard to determine the problem without actually seeing the database.

 

[a2knovice]

KeithG,

I changed the single quote to double quote with the same results. How does the Append query know only to fetch the single record when there is no selection criteria defined? It appears that the Append query is fetching all records due to the lack of selection criteria.

In your example, you used the same naming convention that I used, AS400Lib_FileNm, to name your PassThrough query. Everytime I see AS400Lib_FileNm in the VBA code or SQL statement, I change it to the appropriate AS400 Library and File Name. In the Append query SQL statement, should it be pointing to the PassThrough query name, or the AS400 Library and File Name?

 

[KeithG]

The append query should be referencing the passthrough query and the passthrouhg query should be referencing the AS/400 table. When running the passthrough query does it bring back more than one record? There must be a problem in the Where clause of the SQL statement.

 

[a2knovice]

KeithG,

- With the Append query referencing the Passthrough query, it aborts.
- With the Append query referencing the AS400 file, all records are inserted into the Access table.
- With the Append query modified to include the selection criteria using the strClient string defined in the module, a prompt window displays asking for a parameter. When I enter the Item Number again in this prompt window, I get exactly what I'm looking for.

 

[KeithG]

Alright!! You are referencing the actual AS400 table in the Append query?

 

[a2knovice]

Thanks for all your help and patience! This is not exactly what I was hoping for, having to enter the Item Number twice, but I'll have to work with it since I can't seem to get it to function properly This seems way too complicated for just getting data from the AS400 and adding to the table...or displaying on a form, for that matter.

Thanks for your guidance.

 

[KeithG]

Don't give up, keep trying you have gotten close to what you want! Sorry I couldn't be of more help, if I were sitting next to you we could have the database working in no time.


Good Luck

Keith

 

[a2knovice]

On more quick thing...ha ha. If I wanted to display the results on a form instead of adding to a table, would I just use the Passthrough query and then set the value of the field on the form to the value of the field in the file on the AS400?

That is: frmItmDesc.value = AS400Lib_FileNm.FieldNm