Query for MS Access to insert record if not exists

Here is a MySql query which is fine, but it does not work on MS-Access:

INSERT INTO ProcedureCodeModifier (ProcedureCode, Description)
SELECT * FROM
(
    SELECT 'A1', 'Dressing for one wound'
) AS tmp
WHERE NOT EXISTS (SELECT ProcedureCode FROM ProcedureCodeModifier
                  WHERE ProcedureCode = 'A1')
LIMIT 1;

Answers


Remove LIMIT 1.

That would be SELECT TOP 1 ... in Access, but it's not needed here.


As stated here

I would do something like this: check to see if the code exists in the ProcedureCodeModifier table and if it doesn't, then run your Insert Into SQL. You might have to play with this a bit, depending on if your Code field is a TEXT or an INT, but this should get you most of the way there.

Dim db as Database
Dim rec as Recordset
Dim sSQL as String

Set db = CurrentDB
Set rec = db.OpenRecordset("SELECT ProcedureCode FROM ProcedureCodeModifier WHERE ProcedureCode = 'A1'")

This refreshes the dataset so you can get an accurate record count rec.MoveFirst rec.MoveLast

If your record count is 0, then the code isn't in the DB yet so you need to add it

If rec.RecordCount = 0 Then
  sSQL = "INSERT INTO ProcedureCodeModifier (ProcedureCode, Description) VALUES ('A1', 'Dressing for one wound')";
  DoCmd.RunSQL sSQL
EndIf

Always set your connection variables to Nothing so the connection closes!

Set db = Nothing
Set rec = Nothing

"TOP 1" clause is must into main query.

INSERT INTO ProcedureCodeModifier (ProcedureCode, Description)
    SELECT TOP 1 
        'A1' AS ProcedureCode, 
        'Dressing for one wound' AS Description 
    FROM  
        ProcedureCodeModifier 
    WHERE 
        NOT EXISTS (SELECT TOP 1 ProcedureCode, Description 
                    FROM ProcedureCodeModifier 
                    WHERE ProcedureCode = 'A1');

If ProcedureCodeModifier table is empty or if there is only one record in the table then "TOP 1" clause must be omitted in subquery. I have used Top 1 Clause in subquery for some performance issue.


Need Your Help

SelectItem in UICollectionView on first Load

swift select uicollectionview row cell

I have a UICollectionView and there user can select on cell to use the Function in it. So and i need it to set the first cell to selected like in the First Screenshot. But the Problem there is when...

Foundation 5 Topbar not working consistently in rails 4

javascript ruby-on-rails drop-down-menu ruby-on-rails-4 zurb-foundation

I'm using foundation 5 in my rails 4 app. The topbar menu works fine when I send a request. I can hover the items and nested items no problems. Then, I click on one of the items, which has a link_to