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;
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. 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.
