Using criteria (If/then) when exporting Excel data to Access DB

[bdubb1979]

Hi,

I have an Excel VBA procedure that exports sample data from an Excel worksheet into an Access DB table (the Access table has already been created, I'm only adding a new record to it). I only want this export to occur once daily (Day1, Day2, Day3, etc.) so there are not redundant records added to the Access table.

Is there a way to add conditions to the Excel VBA procedure to have it check the Access DB to see if a record has been added for that day? I want to use an If - then statement to check if the data to be added has the same date as a record already in the Access DB. However, I don't know how to reference an Access field from Excel VBA. Additionally, there are hundreds of records in the Access DB, so I would also need to specify where to look.

Any help would be great!

I'm running Excel 2003 and Access 2002.

Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    Set cn = New ADODB.Connection
    MyConn = "MyConnection"
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With
    Set rs = New ADODB.Recordset
    rs.Open Updtbrx, cn, adOpenKeyset, adLockOptimistic, adCmdTable
        With rs
            .AddNew
            .Fields("Empty Field") = "Empty"
            .Fields("Sample Date") = SampleDate '[COLOR=Red]***Want to check Excel data against this field in the Access file[/COLOR]
            .Update
        End With
  
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub