Re: AS400 to Access

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Rob T (RTorcellini_at_DONTwalchemSPAM.com)
Date: 07/19/04 

Date: Mon, 19 Jul 2004 14:05:36 -0400

We copy info off of our 400 all the time. I'm not sure if my way is the
best way to do it, but it works. ;-)

I would probably avoid copying the info into Access. Especially if you're
going to keep deleting records and adding new ones. The database likes to
keep growing, even though you deleted the older records. You'll probably
have to keep compacting the database on a regular basis.......

I have a vb program that opens an painfully slow ODBC connection to the 400
and selects the desired columns/records and puts them in a dataview. I then
simply do an Insert for each record into our SQL server, since I need to
manipulate some of the non-standard date records on-the-fly.

If you have a lot of records, you should watch your memory usage. I do
around 45,000 records and it takes around a minute to do it....most of that
time is wasted just opening the ODBC connection!

Good luck!

-- Rob T.

"Brad Allison" <ballison@ukcdogs.com> wrote in message
news:ewesoLbbEHA.1004@TK2MSFTNGP11.phx.gbl...
> I posted this to the adonet newsgroup and was recommended to post here.
> Thank you for any help:
>
> Okay, I need a bit of advice. I have an ODBC data adapter being filled by
> AS400 which is very slow. If I were they user I would think that the
> computer locked and try to stop the program, reboot, whatever.
>
> So because management is still "investingating" ways of getting a faster
> connection from our primary AS400 (which will never go away) to the
internet
> and internal employees I think some of the data will be able to be handled
> with Access. I know, not the best way but something for now. I am not
> familiar with DTS but what my thoughts are is to run a procedure once a
week
> during off hours that will fill the dataset (ds1) from the AS400's ODBC
data
> adapter then update the Access OLEDB data adapter using the same dataset.
> Does anyone have any thoughts on this process?
>
> I am trying my best to get the approval to the purchase of SQL, but
nothing
> yet and I have some projects lining up that would greatly benefit if
> something like this is possible.
>
> Thanks for any insight, comments, thoughts, ideas.
>
> Brad
>
> PS Besides SQL, the other ways being considered is: ASNA and another data
> connection (can't remember it right now).
>
>
>

Relevant Pages

  • Re: SqlDataAdapter.Update()
    ... string-chewing algorithm wouldn't work well in SQL. ... looping over words in the string and parsing them off, ... >> Dim cn As New SqlConnection ... >> understood that the data adapter did something like this inside the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADO.NET SQL Parameters
    ... I tried updating the SQL SELECT statement in my ... suppose the other possibility is to have another data adapter containing the ... "William Vaughn" wrote: ... I want to SELECT rows first based upon one column's values ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL server vulnerability through ODBC
    ... Look into Application Roles in SQL Server. ... You can grant the necessary select/insert/delete/update privileges to the ... from setting up an ODBC connection or some way in SQL administration to ...
    (microsoft.public.sqlserver.security)
  • Re: ADO.NET SQL Parameters
    ... I figured out how to update the SQL SELECT statement from the IDE ... for instances like this I should just create a "new" data adapter in my code ... I want to SELECT rows first based upon one column's values ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Use Group Policy to set ODBC
    ... Is there any way to automate the ODBC connection setup using group policy? ... Both solution can not provide SQL Authentication, ... Cahnge NameofServer + NameofDatabase ... PART "Driver:" EDITTEXT ...
    (microsoft.public.win2000.group_policy)