Inserting w duplicate key query

[BigAL]

Hello all,

I was wondering if someone could help me out. I'm hoping this is relatively straightforward but I'm not very experienced with SQL and have tried numerous variations on the query syntax but with no success.

I have a load of data in an excel spread sheet that I'm trying to put into an access using Excel VBA (Jet/ACE SQL I think!?). I do this by constructing INSERT INTO strings and looping through my spread sheet. Let's say the primary key is associated with the field [Company] which holds the company names. A simplified version of what I was using (it all works great ) is below:

    Set rs = New ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.ConnectionString = GetConnectionString()
    cn.Open
    cn.Execute strSQL

where strSQL =

INSERT INTO tblTestMDB ([Company], [Date])
VALUES ('BP', '30/09/2012')

Unfortunately, I am having issues with users adding the same company twice. I'd like it to:
- update the existing company info if the company already exists
- add the company if it is not already there.
I'm trying to do it in two stages. First an update, then an add. The first part works great :

UPDATE tblTestMDB
SET [Date]='21/03/2015'
WHERE [Company]='BP';

The second part is killing me (I have tried numerous variations on the syntax but I just don't know what I'm doing...) :

INSERT INTO tblTestMDB ([Company], [Date])
VALUES ('BP', '30/09/2012')
WHERE NOT EXISTS (SELECT * FROM tblTestMDB WHERE [Company] = 'BP');

I find the error messages rather cryptic but they include things like "Query input must contain at least one table or query." or "Missing semicolon ( ; ) at end of SQL statement." (<== No I'm not!?)
If anyone could suggest how to correctly structure the second query, I would be very grateful.

Thanks,
BigAL

 

[Guus2005]

Are you sure that "Company" is not the primary key which was set to "no duplicates"?
What Access version do you use and what kind of backend are you using?

HTH

 

[gemma-the-husky]

i generally find the easiest way to get complex SQL right is to design the query in the visual design, and then inspect the SQL.


(or even not bother - just design a stored query and run it!)