View Single Post
  #9 (permalink)  
Old July 16th, 2009, 07:54 AM
mmcdonal mmcdonal is offline
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I would not do any data corrections until I had the data in the local table. I would not concatenate the date fields, for example, until I got the table locally. So pull the data, then run your concatenation on the local table. This should speed things up.

You can do a Select query on the AS400 table like: "SELECT * FROM MoviesTable WHERE Title Like '%ZZZ%'" This will pull up all those records with ZZZ somewhere in the title. But if you know you only want movies whose title starts with ZZZ, then use 'ZZZ&'. Then just do this:

Code:
Dim sTitle As String
...
   sTitle = rs("Title")
   sTitle = Right(sTitle, Len(sTitle - 4)) 'removes the "ZZZ " to the left of the title
   rs("Title") = sTitle
   rs.Update
 
rs.MoveNext
...
There I would do the processing on the server side since you are only modifying data on the server database.

As to the last issue, DSN's can be slower, but they are more solid than using a Provider statement. I use DSN's mostly to prevent people from running my apps on an unauthorized machine. Provider statements are very portable.

Not being familiar with AS400, can you build the query on the AS400? I do that with SQL Server since it optimizes the query, as you say. I use Enterprise Manager, and create the View that way. I don't use Access to do that. Then you can even connect (link) the view directly as a Table object instead of a Query, if you want. Otherwise I use a Stored Procedure and then just pass variables to it. That is a little more complicated for starters.

Did that help?
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote