Problem With INSERT INTO

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