How to Update DB with AS400 Field Values 1

[PaulaJ]

We use an ODBC connection to an AS400 for our Primary BackEnd Database. In an Access DB, I use an Append Query to select the records from a table in the Access DB using empty values in the fields to be updated from the AS400 BackEnd. Then I use an Update Query for this update.

I know that the connections work correctly, as I can use a Select query and get exactly what I'm looking for. I can't do this in one step, as my key field sizes don't quite match.

My question is: How do I enter the "Update To" field to access the AS400 fields? I've tried the entire connection string and all the portions thereof. It always puts whatever I enter in quotes and then updates exactly that to the field. I end up with the connection string in the Last Name. Any suggestions?

 

[lameid]

I didn't quite follow your methodology...

Once you link an ODBC data source as a table you can use it just as any access table (albeit much slower). The best solution is usually to write a SPT (SQL Pass Through) query to perform as much of the work for you as possible. Then you update based of that query or table. An SPT query is one in which you pass as much of the native code to the system as possible and specify the ODBC connect string so that the server executes as much of the query as possible before returning the results.

I don't think the AS400 ODBC driver is going to support much but a simple select with some criteria thrown in to narrow the results really helps a lot.

 

[PaulaJ]

I am using a Linked Table in my Access DB to an AS400 Data File through ODBC. Any "Select Query" using the Linked File works perfectly. I want to use that same Selected Data to Update fields in my Access DB Table. (ie: I have a group of empty rows except for the Customer ID in my Access DB Table, I want to fill in the Customer Names and Addresses from my Customer Master File that resides on the AS400)

What I can't figure out is how to enter the "Update To" line in the "Update Query" to accomplish this.

 

[lameid]

In the update to row enter the queryname.field name of the field you want to update from. If your query is qryAS400 and the As400 field is custName for the customer name then your update to line for customer name would be
[qryAS400].[custName]

qryAS400 will have to be updateable for this to work. If it is not and this is a one time thing, I'd make a table based on qryAS400 and then use the resultant table instead. If this will be common occurence, you would need to use VBA and recordsets to make these changes.

 

[PaulaJ]

Thanks for trying to help. I ended up running an additional select query to pull in the AS400 data. Not my first choice, but I don't have anymore time to play with it.
Thanks anyway.

 

[wiegelman]

Hi PaulaJ,

Sorry for the late post - haven't been to this site in a while. I work with MS Access and an AS/400 daily. Below is an SQL statement that should help you to solve your problem with just one "update" query.

Info:
AS400Table = name of AS/400 table
AccessTable_After = name of MS Access table

UPDATE AS400Table INNER JOIN AccessTable_After ON AS400Table.ID_NUMBER = AccessTable_After.ID_NUMBER SET AccessTable_After.LastName = [AS400Table]![LAST_NAME], AccessTable_After.FirstName = [AS400Table]![FIRST_NAME], AccessTable_After.MI = [AS400Table]![MIDDLE_NAME];

If I had a place to upload and MS Access DB to, I'd send you an example - hope this helps.

Tom

 

[PaulaJ]

I see how this would work generally. Am I correct in assuming that this means, I cannot use the "Design View" of the Update Query to Update the Access DB fields with the AS400 data? Also, what exactly should I be putting between the [AS400Table]. The Whole Path like a connection string (i.e. Server name, DSN, USRID, Password, Library Name, File Name) There are a plethora of possibilities.

 

[lameid]

As400Table is the name of the link to the AS400 table in Access.

You can write an update query in design view.

The key thing is that you must include both the AS400Table, the Destination table, join the tables appropriately, switch to an update query and under the appropriate destination field in the update to line place As400Table.FieldName (the SQL wiegelman used would use a "!" instead of a "."; "." is ANSI SQL compliant).

Most people post the SQL statements to the forums because describing what a query looks like in the QBE (design view) is cumbersome at best.

 

[wiegelman]

Thanks for the clarification lameid, I saw your post as I was composing my reply below.

Hi Paula,

You can use the "Design View", I just posted the SQL since I cannot "upload" the picture in the "Design View" to this forum. If you create the 2 tables, then get into "SQL View" then paste in the SQL code and go back to "Design View", you'll see it.

Your 1st post stated you are using ODBC to connect to your AS/400 - I made the assumption that this is still the case. I also assumed that you have a table on the AS/400 "linked" into Access - no DSN required and if your Client Access properties are set up to prompt you for username/password, that is where it comes from. The "AS400Table" is the name of that "linked" table on the AS/400 where your data lives and the name that is shown in MS Access for this table. Using a linked ODBC table is just a quick and dirty way of doing this, the SQL "Update" syntax should be the same no matter how you make the connection to the table (as far as I know).

Sorry for the confusion I may have caused.

Tom

 

[PaulaJ]

Here is my SQL statement:
UPDATE [MC#FILEHCA_MCPMEMB] INNER JOIN [Dental Referrals Master] ON [MC#FILEHCA_MCPMEMB]![MMBR#] = [Dental Referrals Master].[ID] SET [Dental Referrals Master]![ClaimTermDate] = [MC#FILEHCA_MCPMEMB]![MTRM1DT];

When I try to save it, I get the error message "Join expression not supported". We are using "Rally Attachmate" not "Client Access". We are doing similar things using Crystal Reports, without a problem. Does the access path of the PF on the AS400 matter?

I got the "Design View" to take the query by Adding both tables, adding a link MMBR# to ID and then adding the field from the AS400 as a column and putting [ClaimTermDate] in the "Update To" Section. It ran but then gave me the error "You tried to assign a Null value to a variable that isn't a Variant Data Type." All of our dates have the ISO AS400 Default Date of "0001-01-01", so I'm sure there wasn't a "Null" value in any of the Date fields.

I'm starting to wonder if Microsoft ever intended it's products to work with an AS400 database.

 

[wiegelman]

Hi Paula,

That looks like it should work and I don't think you need to specify the access path to the Physical File (PF) but, I am not familiar with "Rally Attachmate" so I cannot say for sure. You did correct by adding the 2 tables and creating the join between them on MMBR# to ID. From what you said "adding the field from the AS400 as a column and putting [ClaimTermDate] in the 'Update To' Section", it appears that you are trying to update a field on the AS/400, my understanding is that you want to update a field in the Access table. If this is the case, then put the Access table field 'ClaimTermDate' as the column and the AS/400 field date field 'MTRM1DT' in the 'Update To' section in the QBE of Access - this way, the data will be 'pulled' from the AS/400 and saved into the Access table. For some reason, the QBE in Access is not interpreting your SQL statement correctly - the "SET [Dental Referrals Master]![ClaimTermDate] = [MC#FILEHCA_MCPMEMB]![MTRM1DT]" should be 'pulling' the data from the AS/400 and putting it into Access. The field being updated should be in the column, and the data is coming from the 'Update To' field.

I think you may be getting the error about the NULL since the Access table currently has NULL in the date field and the AS/400 doesn't allow NULL in its date field,

If you go to

http://www16.brinkster.com/wiegelman/

and look at the 2nd line before the bottom of the page 'PaulaJ', I put up a link that you can right click on and "Save target As" to get a copy of the MS Access file that I created to demonstrate what I did. Let me know if this helps and also when you are done with it so that I can remove the file from the website.

Tom

 

[PaulaJ]

Got it. Thanks it was just what I needed. That &quot;Update To&quot; part has always seemed backwards to me. I got the query to work by linking to a logical with a matching key path. The PF key path was not by Member#. Now there is only one more piece, can you think of any way I can only update those records that are not &quot;Null&quot; on the AS400. Access interprets the AS400 Default (0001-01-01) as 1/1/1901 and updates the date field as such. I tried to put in criteria as [MC#FILEHCA_MCLMEM2]![MTRM1DT]<>'0001-01-01' and I get a &quot;Data Type Mismatch in Criteria Expression' Error.

 

[wiegelman]

Cool! So, I took down the link to the Access file since you will not need it.

Did you try Is Not Null as the criteria? I cannot re-create what you have on your system and I have not worked with dates in that format, we use YYYYMMDD without the dash seperators which allows the field on the AS/400 to be a numeric - so we don't have to worry about date formating on the AS/400, we format the data when we pull it off onto the PC.

One thing you could try is using the &quot;#&quot; sign which is used in Access when you are searching for dates. I would try criteria as [MC#FILEHCA_MCLMEM2]![MTRM1DT]<>#0001-01-01#

Let me know.

Tom

 

[PaulaJ]

The ISO Date Default is an RPG ILE addition. It makes it very easy to transfer dates back and forth between PC platforms (Access, Crystal, Excel)and the AS400. It also makes RPG Date logic a snap. As long as you leave the field in the date format and do not attempt to manipulate the date, it works quite well. However, Access does not know quite what to do with the default and translates it badly. Microsoft contends that it is an AS400 problem and therefore wanted to charge us big bucks to even look at the issue. It caused some big issues at Y2K time.

Anyhow, I have the main issue resolved and I thank you very much for taking the time to work it through with me. I have bookmarked your web site in my Favorites, so I'll be talking to you again.

Thanks, PAULAJ

 

[wiegelman]

PaulaJ,

Glad to be of assistance.

BTW, thanks for the info. on the ISO Date Default for the AS/400. I use and administer it but I do not program in RPG at all!

Have a great day.