Solved: Best way to Create or Append Table from large recordset???

[Danny]

Greetings,

I am currently using VBA to query data from an AS400 system. I am pulling about 900,000 records into an ADO recordset. I then add them to an access table by looping through using something like the code below. I am wondering if there is a more efficient way to do this given the large number of records. (Also linked tables are not an option. :banghead: )


Do While Not adoRec.EOF
adoConn.Execute ("INSERT INTO Table(Field1, Field2) " & _
"VALUES('" & adoRec.Fields(0).Value & "', '" & adoRec.Fields(1).Value & "')")
adoRec.MoveNext
Loop

Thanks

[OBP]

Danny, are you creating the table and then appending the records or appending them to an Existing table that already has records?
I would have thought that a VBA generated SQL statement based on your Recordset would avoid the Record by Record transfer.

This is from an SQL server and 2 tables, but it might give you the idea.


strSQL = "Insert into FichaCandidatura_TEMP " & _
"( Ref, TipoRef,Activo, DataEntrada, IDSexo) " & _
"SELECT Ref, TipoRef,Activo, DataEntrada, dbo.T_Sexos.DescSexo " & _
"FROM dbo.FichaCandidatura " & _
"LEFT OUTER JOIN dbo.T_Sexos ON dbo.FichaCandidatura.IDSexo = dbo.T_Sexos.ID " & _
"WHERE " & strSQL


I am not familiar with AS400 but can Access support A TransferDatabase operation with AS400? The transferDatabase can transfer just selected tables.

Edited 8-Dec-09 by geekgirlau. Reason: insert line breaks

[Danny]

I am currently appending the records to an existing, but empty table, but I could create a table if there is a faster way with that route. And no the way I have the code setup it is transfering each record 1 by 1. Is your code appending from 1 table to another? I think that would be better, but I do not know how to write that using a recodset.

[OBP]

Well it should create the Table using this

DoCmd.RunSQL strSQL