VBA / SQL - detect if record exists in database table?

[TheWennerWoman]

Hello,

I have some SQL embedded in my VBA that takes the values entered in a form by the user and uploads them to a database. I want to add some basic error checking that will generate an error if they attempt to add a record that already exists.

This is the relevant part of my code:

Dim conn As New ADODB.Connection
Dim Centralconn As String
Set rs = New ADODB.Recordset

Centralconn = "Provider=SQLOLEDB; Data Source=stavos\moonrise; Initial catalog=budgets; User ID=user; Password=password; Trusted_Connection=No"
conn.Open Centralconn

conn.Execute "IF NOT EXISTS (SELECT TOP 1 FROM Reporting.Annual_Budget WHERE [Company_Code] = '" & stxt_bud_company_code & "' AND [Year] = '" & stxt_bud_year & "' AND [Project_Type] = '" & stxt_bud_projecttype & "' AND [Department] = '" & stxt_bud_department & "') INSERT INTO Reporting.Test_Annual_Budget ([Company_Code], [Year], [Department], [Project_Type], [Currency], [Value], [Create_Date_Time], [Created_By]) " & _
"VALUES ('" & stxt_bud_company_code & "', '" & stxt_bud_year & "', '" & stxt_bud_costcentre & "', '" & stxt_bud_projecttype & "', '" & stxt_bud_currency & "', '" & stxt_bud_value & "', '" & bud_Create_Date_Time & "', '" & bud_Create_User & "')"
conn.Close
Set conn = Nothing

As it stands, it will upload the record if it doesn't already exist but do nothing if it does - which is good. But how do I get a message to pop up to say "record already exists, nothing uploaded"?

Thanks for reading.

 

[RoryA]

You could create a recordset using the first part of your query. If the recordcount is 0, then run the insert part, otherwise pop up your message.

 

[TheWennerWoman]

Thank you, got it to work using your suggestion, much appreciated. Many thanks!