ADO UpdateBatch SQL Server Example?

[lameid]

Does anybody have a working example of using an ADO Recordset to batch update SQL Server? - I am wondering about my connection string, recordset properties order things are set etc.

One record at a time performance is killing performance... I am simply missing something in the initial instantiation of the recordset.

It actually told me that it was already open when I set the locktype to batchoptimistic before calling the open method. I didn't find much on this other than some others had it come up.

I am just not having any luck finding good examples...

What I am really after is bulk loading records into SQL from access via VBA for speed. I really don't have many SQL permissions so I was really hoping pushing things in as a batch would work and be faster.

Edit to add code, error line after change in [red]red[/red].
The below works but is not updating a batch...

Global Const gConSQLServer = "A_SQL_SERVER" 

Global Const gConSQLDB = "A_Database"


Global Const gConSQLConnect = "Provider=sqloledb;Data Source=" & gConSQLServer & ";" & _
                              "Initial Catalog=" & gConSQLDB & ";" & _
                              "Integrated Security=SSPI;"


Public Sub DAOToADO(strDAOSQL As String, _
    strTargetTable, _
    Optional strConn As String = gConSQLConnect _
  )
  
  'Writes All values from Access SQL statement (strDAOSQL)
  'to Target table (strTargetTable)
  'in Supported ADO Connection opened by connection string (strConn)

  Dim conn As ADODB.Connection
  
  Dim rst As DAO.Recordset
  Dim rstSQL As New ADODB.Recordset
  Dim strTable As String
  Dim aFieldNames() As String
  Dim flds As DAO.Fields
  Dim fld As DAO.Field
  Dim i As Integer
  Dim lngFieldCount As Integer
  
  Set conn = New ADODB.Connection
  conn.Open strConn
  
  Set rst = fnThisDB(blRefresh:=True).OpenRecordset(strDAOSQL)
  
  Set rstSQL = New ADODB.Recordset
  
  rstSQL.Open strTargetTable, conn, adOpenDynamic, adLockOptimistic
  
  'Resize array sized to take all field names in DAO recordset
  Set flds = rst.Fields
  lngFieldCount = flds.Count
  ReDim aFieldNames(flds.Count - 1)
  
  'Loop Over fields and populate field name array
  i = 0
  For Each fld In rst.Fields
    aFieldNames(i) = fld.Name
    i = i + 1
  Next fld
  
  
  While Not rst.EOF
    rstSQL.AddNew 'Add SQL / ADO Record
      
      'Assign all the values in source DAO Recordset in Target ADO Recordset
      For i = 0 To (lngFieldCount - 1) Step 1
        rstSQL.Fields(aFieldNames(i)).Value = rst(aFieldNames(i)).Value
      Next i
    
    rstSQL.Update 'Update / save Recordset value

    rst.MoveNext

  Wend
 
'Cleanup
  rstSQL.Close
  Set rstSQL = Nothing
  
  conn.Close
  Set conn = Nothing
  rst.Close
  Set rst = Nothing
  
End Sub

If I try to update for batch updates...

Public Sub DAOToADO(strDAOSQL As String, _
    strTargetTable, _
    Optional strConn As String = gConSQLConnect _
  )
  
  'Writes All values from Access SQL statement (strDAOSQL)
  'to Target table (strTargetTable)
  'in Supported ADO Connection opened by connection string (strConn)

  Dim conn As ADODB.Connection
  
  Dim rst As DAO.Recordset
  Dim rstSQL As New ADODB.Recordset
  Dim strTable As String
  Dim aFieldNames() As String
  Dim flds As DAO.Fields
  Dim fld As DAO.Field
  Dim i As Integer
  Dim lngFieldCount As Integer
  
  Set conn = New ADODB.Connection
  conn.Open strConn
  
  Set rst = fnThisDB(blRefresh:=True).OpenRecordset(strDAOSQL)
  
  Set rstSQL = New ADODB.Recordset
  
  rstSQL.CursorLocation = adUseClient 'Added for batch
  'rstSQL.Open strTargetTable, conn, adOpenDynamic, adLockOptimistic

  [Red]'Fails here... says the recordset is already open[/red]
  [red]rstSQL.Open strTargetTable, conn, adOpenStatic, adLockBatchOptimistic[/red] 'Changed so it will work with batch updating, lock method requires batch optimistic
  
  
  'Resize array sized to take all field names in DAO recordset
  Set flds = rst.Fields
  lngFieldCount = flds.Count
  ReDim aFieldNames(flds.Count - 1)
  
  'Loop Over fields and populate field name array
  i = 0
  For Each fld In rst.Fields
    aFieldNames(i) = fld.Name
    i = i + 1
  Next fld
  
  
  While Not rst.EOF
    rstSQL.AddNew 'Add SQL / ADO Record
      
      'Assign all the values in source DAO Recordset in Target ADO Recordset
      For i = 0 To (lngFieldCount - 1) Step 1
        rstSQL.Fields(aFieldNames(i)).Value = rst(aFieldNames(i)).Value
      Next i
    
    'rstSQL.Update 'change to update batch below 

    rst.MoveNext

  Wend
  
 'rstSQL.UpdateBatch 'Should load entire data as batch for one continuous stream
 
'Cleanup
  rstSQL.Close
  Set rstSQL = Nothing
  
  conn.Close
  Set conn = Nothing
  rst.Close
  Set rst = Nothing
  
End Sub

 

[dhookom]

I'm not sure if this is going to work for you but I build an insert statement with about 50 "UNION SELECT " clauses. You create your basic string of INSERT INTO YourTableName (...fields...) SELECT .... UNION ALL SELECT .... UNION ALL SELECT ...

You can step through your records while incrementing a value until it reaches 50. At that point, execute the SQL statement. You need to make sure you check for EOF for the recordset and handle the last group of records that don't add up to 50.





Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

That looks suspicious to me:

Public Sub DAOToADO(strDAOSQL As String, _
...
Dim rstSQL As [red]New[/red] ADODB.Recordset
...
Set rstSQL = [red]New[/red] ADODB.Recordset
...

Either Dim it as New from the beginning and skip Setting it to New,
or Dim it as ADODB.Recordset and Set it to New

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson