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

Why not use

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

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

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?

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#
)
;

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

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