HELP - Database Wizard not generating correct SQL queries

I am using Access 2003, FrontPage 2003 and IIS V5.0 to publish my Web Site
on a local computer.

I created an Access Data Base and used the Database Results Wizard to
display data.

If I use the database as the data source in the Wizard, it runs fine. If I
use a Query instead, I get the following message

After I publish the site, I keep getting the following message:

Database Results Wizard Error
Description: Syntax error in query. Incomplete query clause.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

It looks like the Database Results Wizard is generating incorrect SQL. If I
write my own SQL, it runs fine.

Any suggestions?

John

maybe missing some quotes somewhere? can you view the SQL query to determine
that it's valid/not using reserved words

regards
paul

"John" <xxxjohnzzz@patmedia.net> wrote in message
news:uJDbUAPyDHA.384@TK2MSFTNGP12.phx.gbl...
> I am using Access 2003, FrontPage 2003 and IIS V5.0 to publish my Web Site
> on a local computer.
>
> I created an Access Data Base and used the Database Results Wizard to
> display data.
>
> If I use the database as the data source in the Wizard, it runs fine. If I
> use a Query instead, I get the following message
>
> After I publish the site, I keep getting the following message:
>
> Database Results Wizard Error
> Description: Syntax error in query. Incomplete query clause.
> Number: -2147217900 (0x80040E14)
> Source: Microsoft JET Database Engine
>
> It looks like the Database Results Wizard is generating incorrect SQL. If
I
> write my own SQL, it runs fine.
>
> Any suggestions?
>
> John
>
>

Since the Database Wizard is generating the SQL, I am not sure how I could
view it.

John


"Paul" <paul@nospam.com> wrote in message
news:uFw9APPyDHA.1060@TK2MSFTNGP12.phx.gbl...
> maybe missing some quotes somewhere? can you view the SQL query to
determine
> that it's valid/not using reserved words
>
> regards
> paul
>
> "John" <xxxjohnzzz@patmedia.net> wrote in message
> news:uJDbUAPyDHA.384@TK2MSFTNGP12.phx.gbl...
> > I am using Access 2003, FrontPage 2003 and IIS V5.0 to publish my Web
Site
> > on a local computer.
> >
> > I created an Access Data Base and used the Database Results Wizard to
> > display data.
> >
> > If I use the database as the data source in the Wizard, it runs fine. If
I
> > use a Query instead, I get the following message
> >
> > After I publish the site, I keep getting the following message:
> >
> > Database Results Wizard Error
> > Description: Syntax error in query. Incomplete query clause.
> > Number: -2147217900 (0x80040E14)
> > Source: Microsoft JET Database Engine
> >
> > It looks like the Database Results Wizard is generating incorrect SQL.
If
> I
> > write my own SQL, it runs fine.
> >
> > Any suggestions?
> >
> > John
> >
> >
>
>

Switch to "Code View" and look for a statement that begins with:

s-sql="SELECT


--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/


John <xxxjohnzzz@patmedia.net> wrote:
> Since the Database Wizard is generating the SQL, I am not sure how I
> could view it.
>
> John
>
>
> "Paul" <paul@nospam.com> wrote in message
> news:uFw9APPyDHA.1060@TK2MSFTNGP12.phx.gbl...
>> maybe missing some quotes somewhere? can you view the SQL query to
>> determine that it's valid/not using reserved words
>>
>> regards
>> paul
>>
>> "John" <xxxjohnzzz@patmedia.net> wrote in message
>> news:uJDbUAPyDHA.384@TK2MSFTNGP12.phx.gbl...
>>> I am using Access 2003, FrontPage 2003 and IIS V5.0 to publish my
>>> Web Site on a local computer.
>>>
>>> I created an Access Data Base and used the Database Results Wizard
>>> to display data.
>>>
>>> If I use the database as the data source in the Wizard, it runs
>>> fine. If I use a Query instead, I get the following message
>>>
>>> After I publish the site, I keep getting the following message:
>>>
>>> Database Results Wizard Error
>>> Description: Syntax error in query. Incomplete query clause.
>>> Number: -2147217900 (0x80040E14)
>>> Source: Microsoft JET Database Engine
>>>
>>> It looks like the Database Results Wizard is generating incorrect
>>> SQL. If I write my own SQL, it runs fine.
>>>
>>> Any suggestions?
>>>
>>> John

Kathleen,

Here is the SQL from the Access Query:

SELECT POST.[Last Name], POST.[First Name], POST.[Room Number],
POST.Address, POST.City, POST.State, POST.Zip, POST.[e-Mail],
POST.Telephone, POST.Cel, POST.Fax, POST.HRID, POST.[Supervisor HRID],
POST.[Org Code], POST.Level
FROM POST
WHERE (((POST.[Last Name]) Like "adams"));



Here is the Database Wizard Generated Statements:

<!--webbot bot="DatabaseRegionStart" s-columnnames="Last Name,First
Name,Room
Number,Address,City,State,Zip,e-Mail,Telephone,Cel,Fax,HRID,Supervisor
HRID,Org Code,Level"
s-columntypes="202,202,202,202,202,202,202,202,202,202,202,3,3,202,202"
s-dataconnection="post" b-tableformat="TRUE" b-menuformat="FALSE"
s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE"
b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE"
i-listformat="0" b-makeform="FALSE" s-recordsource="POST Query"
s-displaycolumns="Last Name,First Name,Room
Number,Address,City,State,Zip,e-Mail,Telephone,Cel,Fax,HRID,Supervisor
HRID,Org Code,Level" s-criteria s-order s-sql="SELECT * FROM "POST
Query"" b-procedure="FALSE" clientside suggestedext="asp"
s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256"
i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc"
u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc"
tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00"
width="100%"><font color="#000000">This is the
start of a Database Results region. The page must be fetched from a web
server with a web browser to display correctly; the current web is stored on
your local disk or network.</font></td></tr>"
startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>





"Kathleen Anderson [MVP - FrontPage]" <spiderwebwoman@mvps.org> wrote in
message news:%233Y4RqXyDHA.1272@TK2MSFTNGP12.phx.gbl...
> Switch to "Code View" and look for a statement that begins with:
>
> s-sql="SELECT
>
>
> --
>
> ~ Kathleen Anderson
> Microsoft FrontPage MVP
> Spider Web Woman Designs
> http://www.spiderwebwoman.com/resources/
>
>
> John <xxxjohnzzz@patmedia.net> wrote:
> > Since the Database Wizard is generating the SQL, I am not sure how I
> > could view it.
> >
> > John
> >
> >
> > "Paul" <paul@nospam.com> wrote in message
> > news:uFw9APPyDHA.1060@TK2MSFTNGP12.phx.gbl...
> >> maybe missing some quotes somewhere? can you view the SQL query to
> >> determine that it's valid/not using reserved words
> >>
> >> regards
> >> paul
> >>
> >> "John" <xxxjohnzzz@patmedia.net> wrote in message
> >> news:uJDbUAPyDHA.384@TK2MSFTNGP12.phx.gbl...
> >>> I am using Access 2003, FrontPage 2003 and IIS V5.0 to publish my
> >>> Web Site on a local computer.
> >>>
> >>> I created an Access Data Base and used the Database Results Wizard
> >>> to display data.
> >>>
> >>> If I use the database as the data source in the Wizard, it runs
> >>> fine. If I use a Query instead, I get the following message
> >>>
> >>> After I publish the site, I keep getting the following message:
> >>>
> >>> Database Results Wizard Error
> >>> Description: Syntax error in query. Incomplete query clause.
> >>> Number: -2147217900 (0x80040E14)
> >>> Source: Microsoft JET Database Engine
> >>>
> >>> It looks like the Database Results Wizard is generating incorrect
> >>> SQL. If I write my own SQL, it runs fine.
> >>>
> >>> Any suggestions?
> >>>
> >>> John

John:
Try single quotes around the string:

WHERE (((POST.[Last Name]) Like 'adams'));

See: http://support.microsoft.com/default.aspx?scid=kb;[LN];306430

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/


John <xxxjohnzzz@patmedia.net> wrote:
> Kathleen,
>
> Here is the SQL from the Access Query:
>
> SELECT POST.[Last Name], POST.[First Name], POST.[Room Number],
> POST.Address, POST.City, POST.State, POST.Zip, POST.[e-Mail],
> POST.Telephone, POST.Cel, POST.Fax, POST.HRID, POST.[Supervisor HRID],
> POST.[Org Code], POST.Level
> FROM POST
> WHERE (((POST.[Last Name]) Like "adams"));
>
>
>
> Here is the Database Wizard Generated Statements:
>
> <!--webbot bot="DatabaseRegionStart" s-columnnames="Last Name,First
> Name,Room
> Number,Address,City,State,Zip,e-Mail,Telephone,Cel,Fax,HRID,Supervisor
> HRID,Org Code,Level"
> s-columntypes="202,202,202,202,202,202,202,202,202,202,202,3,3,202,202"
> s-dataconnection="post" b-tableformat="TRUE" b-menuformat="FALSE"
> s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE"
> b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE"
> i-listformat="0" b-makeform="FALSE" s-recordsource="POST Query"
> s-displaycolumns="Last Name,First Name,Room
> Number,Address,City,State,Zip,e-Mail,Telephone,Cel,Fax,HRID,Supervisor
> HRID,Org Code,Level" s-criteria s-order s-sql="SELECT * FROM
> "POST Query"" b-procedure="FALSE" clientside
> suggestedext="asp" s-defaultfields s-norecordsfound="No records
> returned." i-maxrecords="256" i-groupsize="0" botid="0"
> u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc"
> u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY"
> preview="<tr><td colspan=64 bgcolor="#FFFF00"
> width="100%"><font color="#000000">This
> is the start of a Database Results region. The page must be fetched
> from a web server with a web browser to display correctly; the
> current web is stored on your local disk or
> network.</font></td></tr>"
> startspan --><!--#include file="_fpclass/fpdblib.inc"-->
> <% if 0 then %>
>
>
>
>
>
> "Kathleen Anderson [MVP - FrontPage]" <spiderwebwoman@mvps.org> wrote
> in message news:%233Y4RqXyDHA.1272@TK2MSFTNGP12.phx.gbl...
>> Switch to "Code View" and look for a statement that begins with:
>>
>> s-sql="SELECT
>>
>>
>> --
>>
>> ~ Kathleen Anderson
>> Microsoft FrontPage MVP
>> Spider Web Woman Designs
>> http://www.spiderwebwoman.com/resources/
>>
>>
>> John <xxxjohnzzz@patmedia.net> wrote:
>>> Since the Database Wizard is generating the SQL, I am not sure how I
>>> could view it.
>>>
>>> John
>>>
>>>
>>> "Paul" <paul@nospam.com> wrote in message
>>> news:uFw9APPyDHA.1060@TK2MSFTNGP12.phx.gbl...
>>>> maybe missing some quotes somewhere? can you view the SQL query to
>>>> determine that it's valid/not using reserved words
>>>>
>>>> regards
>>>> paul
>>>>
>>>> "John" <xxxjohnzzz@patmedia.net> wrote in message
>>>> news:uJDbUAPyDHA.384@TK2MSFTNGP12.phx.gbl...
>>>>> I am using Access 2003, FrontPage 2003 and IIS V5.0 to publish my
>>>>> Web Site on a local computer.
>>>>>
>>>>> I created an Access Data Base and used the Database Results Wizard
>>>>> to display data.
>>>>>
>>>>> If I use the database as the data source in the Wizard, it runs
>>>>> fine. If I use a Query instead, I get the following message
>>>>>
>>>>> After I publish the site, I keep getting the following message:
>>>>>
>>>>> Database Results Wizard Error
>>>>> Description: Syntax error in query. Incomplete query clause.
>>>>> Number: -2147217900 (0x80040E14)
>>>>> Source: Microsoft JET Database Engine
>>>>>
>>>>> It looks like the Database Results Wizard is generating incorrect
>>>>> SQL. If I write my own SQL, it runs fine.
>>>>>
>>>>> Any suggestions?
>>>>>
>>>>> John