delete a record from access

[jeh]

Hello,

I have a two tables in Access that I'm writing data to from excel. One is a historical table, and the other is used to handle temporary records. Cust_out.

Here is what I want to do: I want to make a third table that I can write a record to. If this record matches any record in the Cust_out table, I want to delete both records in the third table and the Cust_out table, but not the tables.

I'm sure there is a better way to do this, but this the only thing that i figured I might be able to understand at this point.

Thanks very much for yuor time,

Jim

 

[mdmilner]

What about reading the xls data into an array and then comparing the individual field values in the array with the Access table? This might cut out the write/erase step if it's unnecessary.

xls - read into array
open access
create a recordset option but use a WHERE/filter property to limit the number of records returned if possible
Walk the recordset comparing all (necessary) fields for a match
If nomatch - write to the Cust_out Access table

Access portion would be something like:
Please note, I made the assumption that the first array value would be your keyfield for use in building the filtering (WHERE) creating your recordset. I also assumed you didn't need to look at the first column (first column is always 0, not 1 in a recordset or table). This is just one way to accomplish this.

Oops, and a disclaimer. This is what could be written to run within Access. I haven't yet in my career tried to declare DAO objects within Excel so I am not 100% certain this will run as-is, nor did I include error handlin.

Public Function Comparator(ByVal aValues() As String)
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim X As Integer

Set dbs = CurrentDB()

strSQL = "SELECT * FROM tblname WHERE fld1='" & aValues(0) & "'"
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
  Do Until rs.EOF
    For X = 1 to 10
      If Not (.fields(X).Value = aValues(X)) Then
        ' Found a comparison value not equal
      End If
    Next X    
    .MoveNext
  Loop
End With

Set rs = Nothing
Set dbs = Nothing
End Function

 

[jeh]

Thanks mdmilner,

I have been away for a while. I will try this.

Thanks for your time,

Jim