Ive got a confirmation asp page and get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

/AbsenceForm/ConcernRecorded.asp, line 47

Below is the code this refers to - whats wrong i cant work it out?? Thanks

Woody


<%

MyYear=Replace(Request.Form("MyYear"), "'", "''")
MyName=Replace(Request.Form("MyName"), "'", "''")
MyTeacherName=Request.Form("MyTeacherName")
MyTeacherDept=Replace(Request.Form("MyTeacherDept" ), "'", "''")
MyDateIncident=Replace(Request.Form("MyDateInciden t") , "'", "''")
MyTimeIncident=Replace(Request.Form("MyTimeInciden t") , "'", "''")
MyDetailsIncident=Request.Form("MyDetailsIncident" )

if MyYear="" then MyYear=" "
if MyName="" then MyName=" "
if MyTeacherName="" then MyTeacherName=" "
if MyTeacherDept="" then MyTeacherDept=" "
if MyDateIncident="" then MyDateIncident=" "
if MyTimeIncident="" then MyTimeIncident=" "
if MyDetailsIncident="" then MyDetailsIncident=" "


Set MyConn=Server.CreateObject("ADODB.Connection")
ConnStr="DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("Absence.mdb")


SQL = "Insert Into TblMain (Year, Name, Teacher Name, " _
& "Teacher Dept, Incident Date, Incident Time, " _
& "Incident Details) Values ('"&MyYear&"', '"&MyName&"', '"&MyTeacherName&"', " _
& "'"&MyTeacherDept&"', '"&MyDateIncident&"', '"&MyTimeIncident&"', " _
& "'"&MyDetailsIncident&"')"


MyConn.Open connStr
MyConn.Execute (SQL)
MyConn.Close
Set MyConn = Nothing

It may not be the only problem, my asp/access is really rusty, but spaces in field names is just asking for trouble. Surround field names that contain spaces with square brackets.

SQL = "Insert Into TblMain (Year, Name, [Teacher Name], " _
& "[Teacher Dept], [Incident Date], [Incident Time], " _
& "[Incident Details]) Values ('"&MyYear&"', '"&MyName&"', '"&MyTeacherName&"', " _
& "'"&MyTeacherDept&"', '"&MyDateIncident&"', '"&MyTimeIncident&"', " _
& "'"&MyDetailsIncident&"')"

Just a glance, but your error may be caused by having spaces in the names of your column headers in the SQL statement. Give it a check.