0

I am new to stackoverflow, so forgive me if I break any forum rules. I currently use MS-Access as a front-end view to many ODBC-linked SQL server tables. My issue arises when I try to load a single column of data from a local table to an "updateable" table on the SQL server. Just pasting in 10k records takes upwards of 15 minutes and I'm looking for a solution on how to use a VB module to reduce this time. Using this link, How to increase performance for bulk INSERTs to ODBC linked tables in Access?, I was able to create a macro that runs at about the same speed as just pasting in, but I really need to increase the speed since it's just too slow.

My local table name is [tblTempSmartSSP] which contains one field (circuits)

My SQL Server ODBC-Linked table [dbo_REF_CIRCUIT_LIST_UPDATEABLE_RM] has a matching field name (circuits)

Sub PtqTest()
Dim cdb As DAO.Database, rst As DAO.Recordset
Dim t0 As Single, i As Long, valueList As String, separator As String

t0 = Timer
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset("SELECT circuits FROM tblTempSmartSSP", dbOpenSnapshot)
i = 0
valueList = ""
separator = ""
Do Until rst.EOF
    i = i + 1
    valueList = valueList & separator & "(" & rst!circuits & ")"
    If i = 1 Then
        separator = ","
    End If
    If i = 1000 Then
        SendInsert valueList
        i = 0
        valueList = ""
        separator = ""
    End If
    rst.MoveNext
Loop
If i > 0 Then
    SendInsert valueList
End If
rst.Close
Set rst = Nothing
Set cdb = Nothing
Debug.Print "Elapsed time " & Format(Timer - t0, "0.0") & " seconds."
End Sub

Sub SendInsert(valueList As String)
Dim cdb As DAO.Database, qdf As DAO.QueryDef

Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("dbo_REF_CIRCUIT_LIST_UPDATEABLE_RM").Connect
qdf.ReturnsRecords = False
qdf.SQL = "INSERT INTO dbo_REF_CIRCUIT_LIST_UPDATEABLE_RM (Circuits) VALUES 
" & valueList
qdf.Execute dbFailOnError
Set qdf = Nothing
Set cdb = Nothing
End Sub

Thank you in advance for any insight you provide!!

Ryan

Erik A
  • 31,639
  • 12
  • 42
  • 67
Ryan Morris
  • 1
  • 1
  • Be careful here. This is wide open to sql injection. My friend bobby tables loves this type of code. http://bobby-tables.com/ You need to use properly parameterized queries here instead of building up a string and executing it. – Sean Lange Jun 08 '18 at 16:55
  • Why arent you using transferspreadsheet or transfer text? Or some kind of join that checks for primary key = null? – Doug Coats Jun 08 '18 at 17:51
  • You can try a [disconnected ADO recordset](https://stackoverflow.com/a/32830596/7296893). I highly recommend using recordsets above queries, that alleviates the risk of SQL injection too. – Erik A Jun 08 '18 at 19:38
  • 1
    Are you just appending data? Just use an append query from the local table to the linked table. Definitely stay away from looping one record at a time in code – dbmitch Jun 08 '18 at 19:47

0 Answers0