I was using a linked server to do this. As long as your file has a PK you can
use to just grab new rows, you should be in good shape. I had to turn on
Collation Compatibility to get good performance.

Another option would be to use an ActiveX script to set your SQL Query to
something like:

select * from OPENQUERY(AS400, 'select * from FILE where FILE_PK >
MAX_PK_LOCAL')

or

select * from AS400.Owner.Library.File where File_PK > Max_PK_Local


It can be done without too much trouble and could probably even be done with
Parameters to the query and Global Variables. Take a look at www.sqldts.com
for some great examples on all sorts of DTS stuff.

-Pete

"Jeff Turner" <jturner (AT) discussions (DOT) microsoft.com> wrote:

Pete, thanks for the response.

I've tried to create a linked server using the the MS OLE DB provider for
DB2 with no success. I found an article that stated you must use MS SNA
Server or MS Host Integration Server to configure the data source for the OLE
DB provider (Microsoft Knowledge Base Article - 218590). Neither of which I
am running or have licensing for. I was able to create a linked server using
the ODBC OLE provider pointing at my Client Access ODBC DSN. However, I could
only return a few rows from my DB2 table(s) (i.e. my query returns 89 rows
from a table that has 62000+ rows using select * from
AS400.[owner].[library].[file]).

Regarding the PK's on my tables most use a key that is not in any sequential
order (i.e. a vehicle VIN number). I had an idea of making a join on the two
tables returning all row from my AS/400 table and only rows from my local
table where the join was equal, then looking for null values in a column from
my local table. I've done this in MS Access before and it works well for
appending new data (SELECT * FROM [AS/400 Table] LEFT JOIN [Local Table] ON
[AS/400 Table].VIN = [Local Table].VIN WHERE [Local Table].VIN Is Null.
Unfortunately I have to figure out the linked server problem first.

-Jeff

"Peter A. Schott" wrote:

I think I would try another version of Client Access. I know that our older,
beta version worked without any problems while the newest release definitely
had issues. Never could figure that out. You may also want to try using
OPENQUERY instead of AS400...file notation. That may give you some success.

Do you have a local test box you can install the drivers on to see if the
problem can be replicated? That may be worth a try.

-Pete

"Jeff Turner" <jturner (AT) discussions (DOT) microsoft.com> wrote:

Client Access. Then, in SQL Server EM, add a linked
server using the Microsoft ODBC Connection and add in the
connection string, DSN=<name of your DB2 DSN>. The Product
name field will be DB2. You then add the valid
credentials on the next applet.