Problem with INSERT INTO

[DianaW]

Hello,

I have one question regarding the use of an INSERT INTO query. I tried the following code, which I found on http://www.techonthenet.com/sql/insert.php, however unfortunately it does not work.
The text on the website says: “The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.” However, it just produces an error code, which says that Jet cannot find the dual table. Why doesn’t it work?

INSERT INTO tbX ( Col1, Col2, Col3 )
SELECT 'Test', 15, #3/3/2007#
FROM dual
WHERE not exists (select * from tbX where tbX.Col1 = 'Test');

Additionally, I try to do the same thing via Excel. I use here I code, which I found in a forum, too. However, unfortunately it doesn’t work as well… any hints?

Option Explicit

Private adoCn As ADODB.Connection
Private adoRs As ADODB.Recordset
Private adoCmd As ADODB.Command

Sub OpenDatabase()
    Dim sDBPfad As String
    
    sDBPfad = "C:\Test\Question.mdb"
    
    Set adoCn = New ADODB.Connection
    
    With adoCn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & sDBPfad
        .Open
    End With
End Sub

Sub InsertData()
    Call OpenDatabase

    Set adoRs = New ADODB.Recordset

    With adoRs
        .ActiveConnection = adoCn
        .Source = "tbX"
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open
        .AddNew
        .Fields("Col1").Value = "TEST"
        .Fields("Col2").Value = 99
        .Fields("Col3").Value = #3/3/2007#
        .Close
    End With
End Sub

Thank you.
Diana

 

[pbaldy]

Why not use

INSERT INTO tbX ( Col1, Col2, Col3 )
VALUES ('Test', 15, #3/3/2007#)

 

[DianaW]

Thank you, but...

Thank you!! Yes, that works. But how can I make check whether the new data doesn't already exist in the table?

Do you also have an idea re the excel code?

Diana

 

[pbaldy]

Lots of ways. You could make that field in the table not allow duplicates, and simply allow the attempted insert to fail. You could first check with a DCount or recordset and see if it exists before doing the insert.

You didn't say what went wrong with the Excel code, so I didn't really look at it. You don't have .Update, but IIRC ADO doesn't require it. What goes wrong?

 

[ByteMyzer]

Try:

INSERT INTO tbX ( Col1, Col2, Col3 )
VALUES ('Test', 15, #3/3/2007#)
WHERE NOT EXISTS
 (SELECT T2.*
  FROM tbX AS T2
  WHERE tbx.Col1='Test'
  AND tbx.Col2=15
  AND tbx.Col3=#3/3/2007#
 )
;

 

[DianaW]

There is an error message, which says (I have to translate it) that this in an unacceptable SQL command and that the programme expects a delete, insert, select or update query.

Hmm, but why does the SQL code suggested by the website not work?

Thank you.
Diana

 

[Jon K]

INSERT INTO tbX ( Col1, Col2, Col3 )
SELECT 'Test', 15, #3/3/2007#
FROM dual
WHERE not exists (select * from tbX where tbX.Col1 = 'Test');

For it to work, you need to have a table named "dual" with a single record in it.
.