New or Update Access table

[vavs]

I have an Access table that I am updating from Excel using DAO. The code in VBA looks like this:

*/
Set cnn = New ADODB.Connection
MyConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source = S:\Public Folder\EDI for Mercedes\EDITracker.accdb"
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, LockType:=adLockOptimistic

With rst
.AddNew
.Fields("ASN") = Cells(lngRow, 1).Value
.Fields("ASN Line") = Cells(lngRow, 2).Value
.Fields("Part#") = Cells(lngRow, 3).Value
.Fields("Tag#") = Cells(lngRow, 4).Value
.Fields("Weight") = Cells(lngRow, 5).Value
.Fields("TagUM") = Cells(lngRow, 6).Value
.Fields("ShipDate") = Cells(lngRow, 7).Value
.Fields("GrossWT") = Cells(lngRow, 8).Value
.Fields("GWUM") = Cells(lngRow, 9).Value
.Fields("NetWT") = Cells(lngRow, 10).Value
.Fields("NWUM") = Cells(lngRow, 11).Value
.Fields("Carrier") = Cells(lngRow, 12).Value
.Fields("Truck#") = Cells(lngRow, 13).Value
.Fields("ASN ID") = Cells(lngRow, 14).Value

rst.Update
End With



' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

lngRow = lngRow + 1

Loop

/*

The last field ASN ID is a primary key. Here is my problem. For a wide variety of reasons, I am getting duplicate transmissions of data. I am trying to weed them out, but in the interim, I would like to adjust this program to update a record if the ASN ID already exists and create a new record if it does not. Basically if then logic. I am not sure how to check for the ASN ID prior to importing the recordset. Any help would be appreciated. BTW the field ASN ID is text not numeric.

 

[revans]

You'll want a function that checks to see if the key exists. Perhaps this

Public Function KeyExists(strKey As String) As Boolean
'return true if key exists in table, else return false
Dim rst As DAO.Recordset
Dim sql As String
Dim temp As Boolean
sql = "SELECT [ASN ID] FROM MyTable WHERE [ASN ID]='" & Replace(strKey, "'", "''") & "'" '  [COLOR=red]<--- change table name to your table name
[/COLOR]Set rst = CurrentDb.OpenRecordset(sql)
temp = Not (rst.EOF And rst.BOF)
Set rst = Nothing
KeyExists = temp
End Function

Then change your code like this

Instead of these two lines
 
With rst
.AddNew

use these instead
 
With rst
  If KeyExists(Cells(lngRow, 14).Value) Then
    .Update
  else
    .AddNew
  end if
... rest of your code to change the fields as normal

Note, it's been a while since I've used DAO (I stick to ADO). The If KeyExists Then .Update may be something like FindRow or something. The point is you're updating the row if it exists or adding a new row otherwise. Your code below that switch is the same (I think).

hth,

Rich