Problems running Stored Queries from ASP

[jonathanfrascella]

I've got a standalone Access DB (with Forms, code, etc) that I need to create an ASP frontend for.

The problem is that the existing Tables, Fields and Queries don't have very forgiving names, with spaces used throughout. I've also encountered problems with SQL that doesn't seem to be standard, or it possibly could be an incompatibility between the SQL and JET Drivers I'm connecting with through my ASP application.

Example names:
Tables - H&S_Accidents_Data and H&S_Accidents_Data_Master

Query - Create H&S Accidents Data

The SQL inside the above query is:
SELECT [H&S_Accidents_Data_Master].* INTO [H&S_Accidents_Data]
FROM [H&S_Accidents_Data_Master];

In an ideal world, I would re-write the Query so it performs an INSERT of a SELECT results set, but I don't want to touch the code of Queries or change the table schema.

When (trying) to run the Query in my ASP code I get the following error:
Microsoft JET Database Engine error '80040e14'

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.


I've also got another Query that runs something along the lines of:
SELECT * FROM Reports WHERE ReportTitle LIKE "ACC*"

The query itself runs fine in Access, but ASP doesn't like the asterisk on the LIKE term, but when I change it to a "%" symbol, the ASP works and the Access returns no results.


My ASP code is as follows:

'(DB connections and paths already declared and opened successfully)
Set oCmd = server.CreateObject("ADODB.Command&quot
sSQL = "[Create H&S Accidents Data]"
oCmd.ActiveConnection = oConn
oCmd.CommandText = sSQL
oCmd.execute

Basically I'm just after some pointers as to whether my SQL code is the problem (in which case I've got a problem) or I need to use different drivers to open the DB through ASP.
Any help would be greatly appreciated.

 

[jonathanfrascella]

In addition to the above, when I run the SQL code for the sample query directly in my ASP code I get the following error:
Microsoft JET Database Engine error '80040e14'

Table 'H&S_Accidents_Data' already exists.


The query is trying to re-create the specified table by overwriting it with a copy of a "master" table. This works in Access, even when the table already exists (no additional DROP statements used) yet in ASP...

There are no other users on the database, so it shouldn't be locked.

 

[cmmrfrds]

Microsoft JET Database Engine error '80040e14'

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

The above seems to indicate that when either Jet or ADO when parsing the sql statement it is not finding a valid statement. Check that the sql string is being sent correctly. maybe a response.write of the string before you send it.

ADO requires a % as the wildcard.
SELECT * FROM Reports WHERE ReportTitle LIKE "ACC%"

You could create an ADO query in access to check the above out.

Microsoft wrote ADO to follow more closely with ANSI SQL, so some of the Access extentions don't work with ADO.
Rewrite to drop the table before running the sql.

 

[jonathanfrascella]

Thanks,

That makes a bit more sense now. My code is now dropping the table prior to re-creating it but it's a shame I can't access the Stored Queries directly without writing additional SQL in my ASP.

 

[cmmrfrds]

You should be able to run the query directly from ADO.

It may be something to do with how the brackets are being handled in the sql string. Experiment. Also, the & looks suspect in the name! Try creating a new query with an ANSI standard name - no space or special characters and run that first.

Use a name like. CreateHAndSAccidentsData

Maybe try directly on the execute.

oCmd.execute "[Create H&S Accidents Data]"