I have an access database I'm trying to use a SQL SELECT statement on but

it keeps returning the Syntax Error in FROM Clause error message. The SQL

Statement is:

"SELECT * FROM tblTWD WHERE Continent = 'Africa' ORDER BY Continent,

Country, City"

The Thing I cant understand is, if I use "tblTWD WHERE Continent 

'Africa' ORDER BY Continent,Country,City" (Take out the SELECT * FROM

portion) It works fine. Ive used this same string on other Access

databases without a hitch so I'm beging to pull my hair out. Thanks for

any insight.



Jason

O.K. I figured out where the problem was, and for the benefit of curious 

folks out there I figured I'd post the sollution,

My code was



strSQL = "SELECT * FROM tblTWD WHERE Continent = 'Africa' ORDER BY 

Continent,Country, City"

objRS.Open strSQL, strDSN, adOpenStatic, adLockReadOnly, adCmdTabel



When I changed from adCmdTabel to adCmdText everything worked fine. Can 

some elaborate on the differences between the two, and why this would cause 

a problem.

Thanks again



At 02:18 PM 1/24/01 +0000, you wrote:

>I have an access database I'm trying to use a SQL SELECT statement on but

>it keeps returning the Syntax Error in FROM Clause error message. The SQL

>Statement is:

>"SELECT * FROM tblTWD WHERE Continent = 'Africa' ORDER BY Continent,

>Country, City"

>The Thing I cant understand is, if I use "tblTWD WHERE Continent 

>'Africa' ORDER BY Continent,Country,City" (Take out the SELECT * FROM

>portion) It works fine. Ive used this same string on other Access

>databases without a hitch so I'm beging to pull my hair out. Thanks for

>any insight.

>

>Jason

>

>---

>FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND

>INSIGHTS IN YOUR INBOX!

>Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips, 

>tools, and

>developments from the experts.  Sign up for one or more of EarthWeb?s

>FREE IT newsletters at http://www.earthweb.com today!










Jason Byrnes

Systems Administrator

Peabody Museum of Archaeology and Ethnology

11 Divinity Avenue

Cambridge, MA  02138

Phone:  (xxx)xxx-xxxx

Jason,



With adCmdTable, you are opening the recordset of the specified table, NOT

running a SQL query (in the classic sense).

adCmdText basically says "Run this QUERY"

adCmdTable says "Open this TABLE".

In fact, (my recordset ADO skills are slim - i always use sql queries) when

you open a table, you aren't even necessarily restricting the data. Then you

can use the ADORecordset filters etc for finding the data you need. You

definately want command text.

In it's simplest form, all really HAVE to do is (with a connection object of

"objConn"):



	strSQL = "SELECT * FROM tblTWD WHERE Continent = 'Africa' ORDER BY

Continent,Country, City"

	set objRS = objConn.Execute(strSQL)



-Peter

Jason,



When you use the adCmdTable optional parameter you are telling ADO that you

want to open a table.

However there is no table called:



SELECT * FROM ..... blah, blah, blah



and so an error is thrown. Normally you'd do something like:



strTableName = "Continents"

objRS.Open strTableName, objConn, adOpenForwardOnly, adLockReadOnly,

adCmdTable



Actually, if you want to open the whole table, adCmdTableDirect is faster.



When you use adCmdText you are telling ADO that you want it to assume that

strSQL is some text command that you want passed to the DB Engine to

evaluate (ie an SQL statement). ADO passes your SQL statement to the DB, and

it tries to work out what to do with it.



Cheers

Ken





----- Original Message -----

From: "Jason Byrnes" <byrnes@f...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, January 25, 2001 6:13 AM

Subject: [asp_databases] Re: Syntax Error in FROM Clause





> O.K. I figured out where the problem was, and for the benefit of curious

> folks out there I figured I'd post the sollution,

> My code was

>

> strSQL = "SELECT * FROM tblTWD WHERE Continent = 'Africa' ORDER BY

> Continent,Country, City"

> objRS.Open strSQL, strDSN, adOpenStatic, adLockReadOnly, adCmdTabel

>

> When I changed from adCmdTabel to adCmdText everything worked fine. Can

> some elaborate on the differences between the two, and why this would

cause

> a problem.

> Thanks again