This is related to my previous thread: http://stackoverflow.com/questions/3069806/sql-query-takes-about-10-20-minutes

However, I kinda figured out the problem. The problem (as described in the previous thread) is not the insert (while its still slow), the problem is looping through the data itself Consider the following code:

 Dim rs As DAO.Recordset
Dim sngStart As Single, sngEnd As Single
Dim sngElapsed As Single



Set rs = CurrentDb().QueryDefs("select-all").OpenRecordset
MsgBox "All records retreived"

sngStart = Timer
Do While Not rs.EOF
    rs.MoveNext
Loop
sngEnd = Timer
sngElapsed = Format(sngEnd - sngStart, "Fixed") ' Elapsed time.

MsgBox ("The query took " & sngElapsed _
    & " seconds to run.")

As you can see, this loop does NOTHING. You'd expect it to finish in seconds, however it takes about 857 seconds to run (or 15 minutes). I dont know why it is so slow. Maybe the lotusnotes sql driver?

any other ideas? (java based solution, any other solution)

What my goal is: To get all the data from remote server and insert into local access table