Syntax error in INSERT INTO query

I am writing a VB6 program that interacts with a ms access 97 database. I have an APPEND query in access that works OK and want to run this query from VB. The code below returns a "syntax error" but I cannot for the life of me see the problem.

The code below is firstly a simplified query to test if the INSERT INTO is working .... It fails! and below that is the full query which also fails! Any help greatly appreciated.


Private Sub cmdRoster_Click()
Dim adoUmpiresRS, adoFixturesRS, adoPlaying1RS, adoPlaing2RS As Recordset

Dim SQLtext As String
Dim db As New Connection

Set adoUmpiresRS = New Recordset
Set adoFixturesRS = New Recordset
Set adoPlaying1RS = New Recordset
Set adoPlaying2RS = New Recordset
Set adoFixturesRS = New Recordset
Set adoconstraintsrs = New Recordset

With db
'áppfilepath contains the path & filename
' the open statement works OK without error
.Open "PROVIDER=MSDataShape;Data PROVIDER=" & _
"Microsoft.Jet.OLEDB.4.0;Data Source=" _
& appfilepath

' this SELECT query works OK & returns the correct number
' of records
adoUmpiresRS.Open "SELECT apoolID,club,grade,sex FROM [a pool list]", db, adOpenDynamic, adLockOptimistic

' heres the simplified append query that has a syntax error
.Execute "INSERT INTO constraints ([ApoolID]) VALUES ('9999')", recs, adExecuteNoRecords

'HERES THE ACTUAL QUERY THAT RUNS OK IN ACCESS97
'construct the append query text
SQLtext = "INSERT INTO Constraints ( ApoolID, datefrom, timefrom, dateto, timeto, datenotified, notifiedby )" _
& " SELECT [A pool list].ApoolID, Fixtures.date, [time]-#12/30/1899 1:0:0# AS starttime, Fixtures.date, [time]+#12/30/1899 0:15:0# AS finishtime, Now() AS today, ""Auto"" AS notifby " _
& "FROM [A pool list] RIGHT JOIN Fixtures ON ([A pool list].sex = Fixtures.sex) AND ([A pool list].grade = Fixtures.grade) AND ([A pool list].club = Fixtures.club1);"

'run the append query - but it has a syntax error in VB
db.execute SQLtext,recs,adExececuteNoRecords


Thankyou

why the parentheses?

The parenthesis [] around field names in the query are generated by ms access97 and were copied from the sql view in the query editor and pasted into vb6 code.

I put paretnthesis around the field name in the simplified query just in case the field name was a reserved vb word. Either way (with or without parenthesis) the query still returns a "syntax error"

hmm... confrusing - I've never messed with table constraints in VB before... I'm perplexed

My guess is the Now() function is causing the problem. When you execute the query from within Access, Now() is recognized as a function. However, when you pass the SQL through ADO from ASP, Now() is not recognized as a valid SQL term since it's an Access-specific function.

well... I thought it was the statement above that one that he was having the problem with though

Thanks for your advice guys, I finally found the syntax problem (don't I feel like a goose!). below is the code as I've ammedied it, you'll see that I've enclosed the table name "constraints" in parenthesis [] because it seems that "constraints" is a reserved VB6 word and you ned to tell VB that it's a table name and not the reserved word function your using. The now() function in the VB query works fine!

'----------------------------------------------------------------
'Use APPEND query to add dates & times when umpires are playing
'to the constraints file in the database
'---------------------------------------------------------------
' N.B. warmup time = 1 hr & warm down time = 15 min
frmRoster.ProgressBar1.Value = 30 'two thirds done
frmRoster.text1.Text = "Writing when umpires are playing"
Set adoConstraintsRS = New ADODB.Recordset
adoConstraintsRS.CursorType = adOpenKeyset
adoConstraintsRS.LockType = adLockOptimistic
'contraints inside [] becasue its a VB6 reserved word
SQLtext = "INSERT INTO [Constraints] ( ApoolID, datefrom, timefrom, dateto, " _
& "timeto, datenotified, notifiedby ) SELECT [A pool list].ApoolID, " _
& "Fixtures.date, [time]-#12/30/1899 1:0:0# AS starttime, Fixtures.date, " _
& "[time]+#12/30/1899 0:15:0#+#1:15:00# AS finishtime, Now() AS today, ""Auto"" AS " _
& "notifby FROM [A pool list] RIGHT JOIN Fixtures ON (([A pool list].club " _
& "= Fixtures.club1) OR ([A pool list].club = Fixtures.club2)) AND " _
& "([A pool list].grade = Fixtures.grade) AND ([A pool list].sex = Fixtures.sex);"
'run the APPEND query & show user the progress
adoConstraintsRS.Open SQLtext, db, , , adCmdText
frmRoster.text1.Text = "Sucessfully updated constraints file"