How to handle dups in UpdateBatch 
Author Message
 How to handle dups in UpdateBatch

I'm fairly new to updating database records so sorry in advance if this is a simple question.

I have users at a remote site that I need to have update my SQL database.  I would like to update the records via batch updating to reduce the number of network trips.  The problem I run into is one of duplicate keys. How do I handle this?
Below is code I'm testing in Excel:


Set Cn = New ADODB.Connection

strSQL = "Driver={SQL Server};Server=(local);Database=Financial;UID=user;PWD=pwd"
Cn.Open strSQL
'Cn.DefaultDatabase = "Financial"
'Cn.CursorLocation = adUseClient

Set RS = New ADODB.Recordset
RS.Open "tblPlan", Cn, adOpenKeyset, adLockBatchOptimistic, adCmdTable
If RS.EOF = False Then
End If

Do While WS.Cells(CurrentExcelRow, DeptCol) <> ""


  RS.Fields(1) = WS.Cells(CurrentExcelRow, DeptCol)
  RS.Fields(2) = WS.Cells(CurrentExcelRow, VendCol)
  RS.Fields(3) = WS.Cells(CurrentExcelRow, Month1_Col)
  RS.Fields(4) = WS.Cells(CurrentExcelRow, Month2_Col)
  RS.Fields(5) = WS.Cells(CurrentExcelRow, Month3_Col)
  RS.Fields(6) = WS.Cells(CurrentExcelRow, Month4_Col)
  RS.Fields(7) = WS.Cells(CurrentExcelRow, Month5_Col)
  RS.Fields(8) = WS.Cells(CurrentExcelRow, Month6_Col)
  RS.Fields(9) = WS.Cells(CurrentExcelRow, Month7_Col)
  RS.Fields(10) = WS.Cells(CurrentExcelRow, Month8_Col)
  RS.Fields(11) = WS.Cells(CurrentExcelRow, Month9_Col)
  RS.Fields(12) = WS.Cells(CurrentExcelRow, Month10_Col)
  RS.Fields(13) = WS.Cells(CurrentExcelRow, Month11_Col)
  RS.Fields(14) = WS.Cells(CurrentExcelRow, Month12_Col)

CurrentExcelRow = CurrentExcelRow + 1


Patrick Hatcher

*** Sent via Developersdex http://www.*-*-*.com/ ***
Don't just participate in USENET...get rewarded for it!

Mon, 13 Oct 2003 05:08:22 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Search table for dups

2. Need Help With Hide Dups!

3. Getting rid of dups

4. delete dups but leave one

5. Not just find dups, delete them

6. Deleting dups-3rd post. Help please

7. Deleting dups...Help!

8. removing dups in combo box list

9. How to combine these dups...

10. Checking for dups???

11. Refine sub to check for dups in a file

12. Application handle from mutex handle?


Powered by phpBB® Forum Software