Query input must contain atleast one table or query

Question

I have a query in access that is suppose to check whether the item already exists in the database before inserting it:

INSERT INTO FinalizedPrintedStickers
Values('0000846043-481-9-0',
'48IG - 1F Straight Panel                                                        ',
'481                               ',
'0',
'0',
'',
'210',
'Printed')
 WHERE NOT EXISTS(SELECT [SN] FROM FinalizedPrintedStickers Where SN = '0000846043-481-9-0')

Now, I've gotten this error before but usually it's when there's no table for example if you "select * from test table" and you type "Select *" and leave out the from clause you get the same error. But I have a table ? Perhaps my where not exists syntax is wrong?

Edit:

Ok, I've added a table "Dual" as suggested with code copy pasted from this question : Table-less UNION query in MS Access (Jet/ACE)

Attempting to add a constraint as shown gave me this error :

after i press ok it highlights the word "Check"

I've never really dealt with constraints (in access atleast..) my syntax is probably wrong

Edit 2:

Adding constraints using ctrl G command

And when I press enter...

Adding constraints using ADO:

And when i press run...

Towandatoward

Shouldn't you put a space between 'values' and the bracket ? Not sure it's needed but worth trying... – Contamination

@JustinKirk You must execute that ALTER TABLE from ADO. Use CurrentProject.Connection.Execute – Faustina

I'm not using ADO, I'm using oledb xD , There's a way of doing this within access right? – Towandatoward

You can run the command via ADO from a one-off VBA routine. Check out the CreateDualTable() routine that @Faustina put in his answer. – Curly

If you don't want to use my CreateDualTable procedure, you can execute that statement with ADO in Access by opening the Immediate window (Ctrl+g), pasting the text from the next comment as a single line, and pressing enter to execute it. – Faustina

CurrentProject.Connection.Execute "ALTER TABLE Dual ADD CONSTRAINT there_can_be_only_one CHECK ((SELECT Count(*) FROM Dual) = 1);" – Faustina

Oh wow i didn't even know you could do that lol, Uh, "Microsoft Access has Stopped working" In the details it just says APPCRASH -_- – Towandatoward

I will just try using ADO in the vb provided with access – Towandatoward

The same thing happens,... Access Just crashes.. – Towandatoward

That's surprising and disappointing, Justin. I've been running that procedure without trouble in nearly all my new dbs the last couple years. Maybe yours is corrupted. I would create a new db, and see whether it works there. – Faustina

I tried creating a copy of the 2 tables into a seperate database (FinalizedPrintedStickers and Dual) and i get the same error... Maybe if i upgrade my version of access?? – Towandatoward

let us continue this discussion in chat – Faustina

Answer 1

This is one of those cases where a Dual table can be helpful. A Dual table is a single-row table that can be used in the FROM clause of a query when you don't really need a source table but the SQL parser insists on there being one.

Some database systems (e.g., Oracle) provide a Dual virtual table as "standard equipment", but in Access we need to create our own. For an excellent description of the process check out HansUp's answer here.

So, once you have your [Dual] table in place, i.e.,

id
--
 1

...then you can use this query do perform your INSERT (or not...):

INSERT INTO FinalizedPrintedStickers
    (
        SN,
        Field2
    )
    SELECT 
        "0000846043-481-9-0" AS SN,
        "48IG - 1F Straight Panel" AS Field2
    FROM Dual
    WHERE DCount("SN","FinalizedPrintedStickers","SN=""0000846043-481-9-0""")=0

Curly

Are you sure you have the query right? Select "0000846043-..." From dual will always return false because there's no records in dual.. wont it ? – Towandatoward

@JustinKirk No, the Dual table should always have exactly one record in it. – Curly

@JustinKirk The Dual table Gord mentioned includes a check constraint which ensures that table always contains exactly one row. – Faustina

Answer 2

try this you can use simple where Clause

INSERT INTO FinalizedPrintedStickers
    Values('0000846043-481-9-0',
    '48IG - 1F Straight Panel                                                        ',
    '481                               ',
    '0',
    '0',
    '',
    '210',
    'Printed')
     WHERE SN Not In(SELECT [SN] FROM FinalizedPrintedStickers Where SN = '0000846043-481-9-0');

Payload

FinalizedPrintedStickers is this table Exists or not/ – Payload

where Clause required to Remove the duplcate entry in Table.? – Payload

Please check with NOT IN Clause – Payload

Its just Query ot Query in some Language.VB,C#? – Payload

It's taken from C# Code, but i'm testing the query in actual access – Towandatoward

I will create sample.. Let me check inmy system... I – Payload