Excel VBA insert into access query - Check if record exists

brian_msbc

Registered User.
Local time
Today, 02:34
Joined
Dec 12, 2016
Messages
17
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:

Code:
 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?
 
Ive made progress since posting this, my current script looks like this:

Code:
 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.
 
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:
Code:
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
 
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.
 
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.
 
thanks Stopher... I'm new to access... that is good advice.
 

Users who are viewing this thread

Back
Top Bottom