Drop access table and index via asp 2

[benniesanders]

Hello, I have created a dynamic catering menu using an access database via asp. The user can create menus by creating two tables from an ASP page I've set up as an editor: the main table and the items table. i.e. menu and menu_items with a relationship. The create process works flawlessly, but how do I do the drop table and index syntax in ASP? I've looked everywhere and can't seem to find anything. Here's one bit of code I found in this forum but I get

Syntax error in DROP TABLE or DROP INDEX

errors.

sql = "DROP TABLE menu_items"
sql = "DROP INDEX menu_id"
objConn.execute(sql)

Thanks a million for any thoughts.

 

[DotNetGnat]

try:

sql1 = "DROP TABLE menu_items"
sql2 = "DROP INDEX menu_id"
objConn.execute(sql1)
objConn.execute(sql2)

-DNG

 

[benniesanders]

Thanks DNG. I made those changes and am still getting this error:

Syntax error in DROP TABLE or DROP INDEX

 

[DotNetGnat]

ok do this...

in the access database...create a new query(named testQuery) and have the following

DROP TABLE menu_items

now on the asp page...do this...

objConn.execute(testQuery)

and see if that works...

-DNG

 

[Sheco]

Well in that first bit of code you showed the only SQL being executed was the DROP INDEX bit, since it was basically overwriting your DROP TABLE syntax before executing it.... so at least we can narrow the problem down to the DROP INDEX statement.

 

[DotNetGnat]

and oh yes...change the order...

sql1 = "DROP TABLE menu_items"
sql2 = "DROP INDEX menu_id"
objConn.execute(sql2)
objConn.execute(sql1)

drop index first and then drop table...

-DNG

 

[Sheco]

This is actually not an ASP question at all.... it is an MS Access sql syntax question.

The way to debug this is to open up Access and click to make a new query, then switch to SQL view, then try hacking at the raw SQL and running it until you nail the SQL syntax that produces the desired results... then you put THAT syntax into your ASP.

Or also there an an MS Access programming forum on tek-tips...

 

[benniesanders]

I just KNEW somebody would say that, but hesitated to post there because I'd get the opposite reaction, it's an ASP issue. How is it an MS Access syntax question when I'm trying to execute it from an ASP. Some access syntax just doesn't work in asp. Doesn't anybody do this in asp? I think I need brackets around the table names. Many thanks again for your help.

 

[Sheco]

No it isnt an ASP issue. ASP knows nothing of Access or any other database.

ASP is merely hosting your ADO COM object.

The COM object is using OLEDB or ODBC to talk to the database...

ADO is completely unaware of anything other than the existance of an instance of the ADO objects.

You would have the same problem trying to execute the same SQL over any other connection to Access be it via Excel, Word, VB, VBScript, or whatever.

 

[Sheco]

... actually there is one difference... Excel, Word and the others would ususally be executed under the security context of the current user and the ASP would normally be executed under the security profile of the IUSER_XXX account.

But that shouldn't impact this situation because the reported error message is SYNTAX ERROR ... this is not referring to a VBScript syntax error but rather the fact that the jet database has rejected the sql syntax.

 

[DotNetGnat]

ok here is one of the similar situation...

MS Access Query

Select field1, replace(field2,"'","''") from blah

--> works fine in access

now if you put the same on ASP page...

sql = "Select field1, replace(field2,"'","''") from blah
"
rs.open sql, objconn

you will get the syntax error complaining that Replace() function is not valid...

but if you do the following...
create a new query in the access database with the same sql statement and execute the query on the asp page using

rs.execute(blahquery)...it works fine...

try making a query even in your case and see if that works...

-DNG

 

[Sheco]

Running it in access 2000 i got:
Undefined function 'replace' in expression.

But, even assuming it did work, you can get errors that are raised by OLEDB, ODBC, or ADO that still are not ASP specific.

ASP specific errors would be like failing to create the ADO connection or recordset object.

 

[Sheco]

i did get the following to work in access 2000:
SELECT blah.field1, Left(field2,10) FROM blah

 

[benniesanders]

Ok guys, I got it. My bad, my fault whatever you want to call it, but it wasn't the index I needed to drop it was the constraint.

ALTER TABLE " & tablename & " DROP CONSTRAINT " & constraint_name

DROP TABLE " & tablename
DROP TABLE " & tablename & "_items"

So now THANKFULLY the !&#$&@ client can dynamically delete those &*%#)@ tables and the relationship. Sorry for all of the confusion but many, many thanks for all of your ideas and help Sheco and DNG. I really appreciate it.

 

[Sheco]

Glad you got it working, and thanks for posting the ultimate solution.

 

[benniesanders]

You are very welcome and thanks for always being a great help. I've gotten good information from your posts many times, both of you.

 

[DotNetGnat]

glad to be of help...

-DNG