SQL to Access table takes a lot of time to load data

[usertest]

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

[June7]

You mean you have a SQLServer table? SQL is not a database, it is a language.

What do you mean by you can't link the table? You are linking when you establish a connection. How and where is DC.ConnectionName set? VBA can set links to SQLServer tables.

You could try setting up a Pass-Through query and use that as source for an INSERT SELECT action. Review https://kb.blackbaud.com/knowledgebase/Article/52445 and https://stackoverflow.com/questions/...hrough-queries

With a Pass-Through query you could review data (not edit) without having to save to a local table and use as source in Access queries. However, I don't know if this will allow faster performance when querying the dataset.

Note for future: Please post code between CODE tags to retain indentation and readability.

[usertest]

Hi,
I created the pass-through query as you mentioned.
Thanks for that.
Quick question: Can I also compare records from Pass through query and access tables, and if the ID's do not match, create a new access table and add those records?
Is that possible?

Thanks!

[June7]

Work with the pass-through as you would any table except for editing. So try a Find Unmatched query. Access has a query build wizard for that. But you might not need that for UPDATE/INSERT issue - check out the UPSERT method. Review https://stackoverflow.com/questions/...crosoft-access

[NoellaG]

Tip: when working with SQL data (SQL server, MySQL, Oracle, ...) always try to avoid RBAR (Row By Agonizing Row) operations. More info https://www.red-gate.com/simple-talk...agonizing-row/