You have several options, but a lot depends on what the data source is behind your ADODB object. What works in SQL Server might not work with some other generic ODBC data source. More details of your scenario would help determine what advice to offer.
For MSSQL you have a couple of options. You can set an "if not exists" on the front of your statement like so (modify to fit your code, of course):
if not exists (select 1 from myTable where myKey = myKeyValue)
insert into myTable (myKey, myOtherStuff) values (myKeyValue, myOtherStuffValue)
raiserror ('Error in here', 16, 1)
Or, you can wrap the whole thing in a stored procedure and just pass parameters. This gives you a little more freedom as far as error checking and such.
Last, you can just execute the statement and let it error, and trap the errors in your VB code with an "On Error" paragraph. Here's a little utility routine I use all the time:
Sub ExecuteSQL(mySqlStmt As String)
On Error GoTo ExecuteSQL_Err
Dim myConn As ADODB.Connection
Dim myMsg As String
Dim i as Integer
Set myConn = New ADODB.Connection
Set myConn = Nothing
If myConn.Errors.Count > 0 Then
For i = 0 To myConn.Errors.Count - 1
myMsg = myMsg & vbCrLf & myConn.Errors(i).Number & ": " & myConn.Errors(i).Description
MsgBox "SQL Errors:" & vbCrLf & myMsg, vbOKOnly + vbCritical, "Execute SQL Statement"
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "Execute SQL Statement"
It assumes that you have a valid connection string somewhere called "strConnectString" to point to either an OLEDB provider or an ODBC data source.
Keep in mind though that if you're using an ODBC provider that doesn't support SQL (think "Text Provider") the technique will not work.