Excel VBA insert into access query - Check if record exists

[brian_msbc]

Not sure if this should go in the Access forums or the Excel, ill post to both...

Im using this VBA line to insert into an Access DB:

 rs.Open qry, conn, adOpenKeyset

rs is a RecordSet
qry is a String ("Insert INTO Table (a,b,c) Values(a,b,c)"

I want to preface this command with a check to see if the record already exists. If so, I want to skip the insert command. Any Suggestions?

 

[brian_msbc]

Ive made progress since posting this, my current script looks like this:

 qry2 = "Select a,b,c FROM table WHERE a = """ & a & """"
rs.Open qry2, conn, adOpenKeyset
       If (rs.EOF) Then
       MsgBox ("Hello")
       End If

Im successfully getting rs.EOF = False when the record exists, and rs.EOF = True when it doesn't exist (I think, I might have that backwards).

Problem is, once its open I cant run it again. (Operation is not allowed when the object is open) I need to be able to run then multiple times in a loop. Still trying to figure out how to accomplish this.

 

[sneuberg]

Look at this it seems that when you do a find on a ado record set the recordset will be at the eof and probably bof too. Maybe something like this:

rs.Open qry, conn, adOpenKeyset

rs.Find "a = 'a' And b = 'b' And c = 'c'"
If Not (rs.EOF And rs.BOF) Then
    rs.AddNew
    rs!a = a
    rs!b = b
    rs!c = c
    rs.Update
End If

 

[brian_msbc]

I really just want to be able to pass an SQL query, see if records exist, and then do something based on that information. Then I want to be able to run that test again with different variables each time, and sometimes different tables.

 

[brian_msbc]

I just guessed at using rs.Close... and so far.. success!

 

[stopher]

If you use a primary key or apply an index to your table with duplicates set to no, then the code is unnecessary. Under such conditions, duplicates will be rejected and non-duplicates will be accepted.

No need to write code, just let the database engine do the work for you.

 

[brian_msbc]

thanks Stopher... I'm new to access... that is good advice.