Search results

YOU ARE A GENIOUS! I can't thank you enough. Cheers.

Hey KeithG -- I got the PassThrough query and Append query to work as designed...finally! Just one thing keeps occurring when the query runs, a window opens up asking me to Select Data Source. I have to choose the data source I created, then everything works great. Any idea how to stop this...

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

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...

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...

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...

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...

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...

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...

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...

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...

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"...

I'm sorry, KeithG, I have no idea what you just said in you last post. :confused:

Hello Robert88, Point 5 of my rather long winded post 6 occurrences ago pretty much tells the story. I was able to muddle my way through creating the Pass Through and Append queries as suggested by KeithG. I tested by manually running the Append query and it worked as designed. Now I need to...

You are correct; I changed the DSN to Read/Write and it was looking for the Access Table on the AS400. OK, so I linked the AS400 file to the Access Table, where do I go from here? I am absolutely lost at this point. I appreciate all the help you have provided me.

Then how does one go about getting realtime data from the AS400 and storing it in an Access table?

Almost there...I think. Here is my test scenario: 1) I have a Client Access ODBC User DSN that I created to allow a "Read Only" Connection Type for safety reasons. 2) I figured out that I needed to create a Function in a Module for the VB code required for the connection string and SQL...

I don't know what you mean by passthrough query and append query. Are you talking about creating some sort of MS Access query? This is going to be realtime fetching of data, wouldn't the SQL statement in a function be faster? I'm not an Access programmer, so forgive me if I sound lame...

Don't I need some kind of connection string first? My goal is to fetch data from the AS/400 database and store in access; I'll do this via SQL statements. But, don't I need some kind of connection string in a module of some sort?

I am an A2K novice and I need some help with connecting to an AS/400. I have my ODBC data source created and it works well. I'm comfortable with the SQL syntax I'll need in A2K to fetch my AS/400 data. I have no idea what is necessary in the way of VB code in A2K to do the connection. Can...