View Single Post
  #7 (permalink)  
Old July 15th, 2009, 05:16 PM
Rood67 Rood67 is offline
Authorized User
Join Date: Mar 2004
Location: Knoxville, TN, USA.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Rood67

Hi Mac,

Yes, that is exactly what I want to do. THANK YOU very much.

I want to open a "read" connection to the AS400, a "write" connection to local Access table. As I step through the AS400 recordset, i can perform calculations, my custom concatenation to join date parts back together, etc...

And the other big, BIG thing, is to write back to the AS400 small changes to text fields. Case in point, if we add a movie title mid way through the order taking period, we denote that by putting the title into the system, but the product name has ZZZ added so the proprietary order program will sort it to the end of the weeks products.

But when we get ready to ship, so the customer doesn't see something like ZZZ Haunting In Connecticut or ZZZ Knowing, I have to go in and manually edit the title to remove the ZZZ.

What I have been hoping for is a way to pull up titles with the ZZZ in the name, and programatically change the name field in the three different tables it resides in.

Another problem that this will eliminate is strictly for my OCD... when I manually edit the name field, the proprietary software defaults the name field to all caps, so ZZZ Knowing becomes KNOWING. By updating using 3 different pass through queries in Access, I've gotten around this. But it's still editing 3 different queries. That's 3 chances to make a typo. If I pick it from a list of recordset results, and write back right([Name],len([Name])-4), then there's no chance for a screw up.

You truly hit the proverbial nail on the head with this last coding example. Now to see if I can get some simple select queries written to start testing and building my way up to the read in non-local table / write out local table, and then try to write back to the AS400.

My only remaining problem, concern, question is this... is there a way to signify in VBA code that my queries to the AS400 are pass through type? Because as I've stated before... normal select queries to the AS400 from Access are SLLLOOOOWWWWW. I am pretty confident this is because Access is reading in the entire AS400 table into local memory, then culling down the displayed result set based on the query criteria.

But the pass through, as defined by selecting the Pass Through option in query builder window, and supplying the DSN that point to the AS400 is tens to hundreds times faster, again, I am pretty confident in the reason why being the nature of the beast it runs the query on the AS400, and only passes back the filtered result set.

So, how in VBA code to I specify that my query is a pass through and not just a select query? Or am I just missing the simple point that by doing the query as a result set it is a pass through by default because the connection is established with a DSN?

Thanks again for all the info and help,
Reply With Quote