Moving Data with ADO 1

[bdavis96]

I am trying to move data from the AS/400 to an Access Database. Currently I am doing the following:

If Not (AS400_FMNOTE.BOF And AS400_FMNOTE.EOF) Then
AS400_FMNOTE.MoveFirst
Do While Not (AS400_FMNOTE.EOF)
PC_FMNOTE.AddNew
PC_FMNOTE("NEST#") = Trim$(AS400_FMNOTE("NEST#"))
PC_FMNOTE("NRORN") = Trim$(AS400_FMNOTE("NRORN"))
PC_FMNOTE("NSUFX") = Trim$(AS400_FMNOTE("NSUFX"))
PC_FMNOTE("NTEXT") = Trim$(AS400_FMNOTE("NTEXT"))
PC_FMNOTE.Update
AS400_FMNOTE.MoveNext
Loop
End If

AS400_FMNOTE = Recordset to the AS/400 Connection
PC_FMNOTE = Recordset to the Access DB Connection

If you are wondering about the Trim$ function, the AS/400 has formatted length fields, so I am getting rid of any extra spaces in each field.

Is there a faster/better way to move records then what I am doing?

 

[ArtieChoke]

One thing that would be much faster is if you can get a .csv type of file from the AS400 and import that into Access. Another option if you have an ODBC data source for the AS400 is to import the data from there. It just depends on how automated you need this to be.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[gmmastros]

I've never worked with an AS400 before, but I have disconnected recordset before. Here's an idea for you to try. It may be VERY fast, or it might not work at all. So, please backup your data first.

Here's the general idea. Get the data from the AS400. Disconnect your recordset. Connect your recordset to the access database. Update the batch.

I know you can disconnect a recordset from a database. Make all sorts of changes to it, and then reconnect it to the same database. I don't know if it will work when you reconnect it to another database.

Here's a link to an article that gives an example.

http://www.devx.com/getHelpOn/10MinuteSolution/20407

 

[bdavis96]

gmmastros,

Thank you for the idea. It ran through my code, but didn't seem to work. There are definitely some noticable differences between Access and AS/400 that might not make it possible to do that method, but I appreciate the help all the same.

 

[fredericofonseca]

Try the following.

create a linked table using ADODB or ODBC to the AS400 table.

Then run the following SQL

insert into my_access_table
select trim(fld1), trim(fld2) ... from my_linked_as400_table.

It should work, and should be a lot faster than using the method you are using presently.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com