Hi,
I found some code on the net to send data from a worksheet in Excel to an existing table in Access, which I've modified to work with my worksheet and table layout. The only problem I'm having with it at this point is that the code crashes if I get to a duplicate record (the first field in the table is the key field, with duplicates not allowed).
I tried adding an error handler section to the code to skip over the .update section of the code if the record already exists, but I couldn't get it to work.
Here is the code I am currently using (the failed error handler is not included):
Can someone please help me add an error check to this code so that it will not attempt to add a record to the Access table if the record already exists? Also, are there any other potential error checks I should be thinking of adding to this? I'm using Excel 2003 and Access 2003, in case that is important.
Thanks in advance.
I found some code on the net to send data from a worksheet in Excel to an existing table in Access, which I've modified to work with my worksheet and table layout. The only problem I'm having with it at this point is that the code crashes if I get to a duplicate record (the first field in the table is the key field, with duplicates not allowed).
I tried adding an error handler section to the code to skip over the .update section of the code if the record already exists, but I couldn't get it to work.
Here is the code I am currently using (the failed error handler is not included):
Code:
Const myDB = "DSD Errors DB.mdb"
Private Sub CommandButton1_Click()
Dim cnn As ADODB.Connection
Dim MyConn
Dim rst As ADODB.Recordset
Dim i As Long, j As Long
Dim Rw As Long
[Q3].Select
'go to the correct sheet and determine the # of used rows
Rw = Range("A65536").End(xlUp).Row
'create the connection to the database
Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & "\" & myDB
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With
'create the recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:="DSD_Invoice_Requests", _
ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
'Load all records from Excel to Access,
'by looping through the rows and columns
myErrors = 0
For i = 2 To Rw
rst.AddNew
'field headings are in row 1 of the worksheet.
For j = 1 To 15
rst(Cells(1, j).Value) = Cells(i, j).Value
Next j
rst.Update
Next i
' Clean up
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
Can someone please help me add an error check to this code so that it will not attempt to add a record to the Access table if the record already exists? Also, are there any other potential error checks I should be thinking of adding to this? I'm using Excel 2003 and Access 2003, in case that is important.
Thanks in advance.