Hello,
I have a SQL table which has 8000 records.
I am writing a vba code to fetch the whole table into a local access table
I cant link the table as its not allowed from the admin side.
The only way I see is using a record set and add row by row using .AddNew
Below is the code.
It is taking 8 mins to copy the data.
Is there any other way I can make it quicker?
Dim r As New ADODB.Recordset
Dim r1 As New ADODB.Recordset
Dim DC As New DataConnection
Dim strSQL As String
Debug.Print "autostart" & Now
CurrentDb.Execute "DELETE * FROM Accesstable;"
strSQL = "SELECT * FROM SQL Table ;"
r.Open strSQL, DC.ConnectionName, adOpenKeyset, adLockOptimistic
strSQL = "SELECT * FROM Accesstable"
r1.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Do While r.EOF = False
r1.AddNew
r1("Col1") = r("Col1")
r1("Col2") = r("Col2")
r1("Col3") = r("Col3")
r1.Update
r.MoveNext
Loop
r.Close
r1.Close
Set r = Nothing
Set r1 = Nothing
Debug.Print "autoend" & Now
End Sub
Please, any help would be appreciated