DROP TABLE IF EXISTS Error
Trevor
10 pages per screen). I cannot drop the old table. I am using the
sessionid to name the table, so that every user can browse
independently.
I tried numerous combinations using NOCOUNT and a whole load of
things. Everything fails. I saw a lot of stored procedures references,
but I haven't used stored procedures in Access, so I can't use them
now (without learning them, and books are mostly out of print on
ASP/Access - replaced by ASP.NET).
Can anyone help on this SQL problem?
Error:
======
mysql create table table335280279 (resultID integer not null,result
char(20) default null)
mysql drop table if exists (select * from dbo.sysobjects where id =
object_id('table335280279') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
session 335280279
Microsoft JET Database Engine error '80040e14'
Syntax error in DROP TABLE or DROP INDEX.
Code:
=====
myResultsTbl = "table" & cstr(session.sessionid)
sqlCreate = "create table " & myResultsTbl _
& " (resultID integer not null," _
& "result char(20) default null) "
existStr = "if exists (select * from dbo.sysobjects where id =
object_id('" & myResultsTbl _ & "') and OBJECTPROPERTY(id,
N'IsUserTable') = 1) "
sqlDrop = "drop table " & existStr
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Data Source=" & _
Server.Mappath("/database/searchResults.mdb") _
& ";Provider=Microsoft.Jet.OLEDB.4.0;"
conn.execute sqlDrop
conn.execute sqlCreate
Conn.close
set Conn = Nothing
Trevor Best
sa...@logicians.com (Trevor) wrote:
>mysql create table table335280279 (resultID integer not null,result
>char(20) default null)
>mysql drop table if exists (select * from dbo.sysobjects where id =
>object_id('table335280279') and OBJECTPROPERTY(id, N'IsUserTable') =
>1)
Sure you shouldn't be using: If exists(...) drop table ...
--
Ride Free (but you still have to pay for the petrol)
(replace sithlord with trevor for email)
trevor oakley
"Trevor Best" <bouncer@localhost> wrote in message
news:esq6jvkjgb4qaoilr...@4ax.com...
> On 8 Aug 2003 01:19:03 -0700 in comp.databases.ms-access,
> sa...@logicians.com (Trevor) wrote:
>
> >mysql create table table335280279 (resultID integer not null,result
> >char(20) default null)
> >mysql drop table if exists (select * from dbo.sysobjects where id =
> >object_id('table335280279') and OBJECTPROPERTY(id, N'IsUserTable') =
> >1)
>
> Sure you shouldn't be using: If exists(...) drop table ...
hair.
Normally I just check a book and I have the answer, but ASP is mostly out of
print, and I have only a few very basic books on ASP.
Trevor Best
oakley" <to006...@blueyonder.co.uk> wrote:
>
>"Trevor Best" <bouncer@localhost> wrote in message
>news:esq6jvkjgb4qaoilr...@4ax.com...
>> On 8 Aug 2003 01:19:03 -0700 in comp.databases.ms-access,
>> sa...@logicians.com (Trevor) wrote:
>>
>> >mysql create table table335280279 (resultID integer not null,result
>> >char(20) default null)
>> >mysql drop table if exists (select * from dbo.sysobjects where id =
>> >object_id('table335280279') and OBJECTPROPERTY(id, N'IsUserTable') =
>> >1)
>>
>> Sure you shouldn't be using: If exists(...) drop table ...
>I tried that and a hundred others and they all fail. I am tearing out my
>hair.
>
>Normally I just check a book and I have the answer, but ASP is mostly out of
>print, and I have only a few very basic books on ASP.
You confused me with the "MySQL" bit in your code where later on you
CreateObject with a connection to a MDB file and use SQL what looks
like SQL Server syntax, can you confirm what database you are actually
using? (MySQL, Jet or SQL Server)
Jet will most definately barf at that SQL syntax.
If you have a MDB with links to SQL Server tables, you should create
your connection direct to the SQL Server as Jet will try to interpret
your SQL and not understand if it's SQL Server specific.
If a jet database, the system table is called MSysObjects, jet/SQL
doesn't (AFAICS) have a "IF" so you'd need open a recordset based on
MSysObjects to determine if that table exists in VBA then execute the
drop if necessary.
trevor oakley
"Trevor Best" <bouncer@localhost> wrote in message
> On Fri, 8 Aug 2003 12:21:25 +0100 in comp.databases.ms-access, "trevor
> oakley" <to006...@blueyonder.co.uk> wrote:
>
> >
> >"Trevor Best" <bouncer@localhost> wrote in message
> >news:esq6jvkjgb4qaoilr...@4ax.com...
> >> On 8 Aug 2003 01:19:03 -0700 in comp.databases.ms-access,
> >> sa...@logicians.com (Trevor) wrote:
> >>
> >> >mysql create table table335280279 (resultID integer not null,result
> >> >char(20) default null)
> >> >mysql drop table if exists (select * from dbo.sysobjects where id =
> >> >object_id('table335280279') and OBJECTPROPERTY(id, N'IsUserTable') =
> >> >1)
> >>
> >> Sure you shouldn't be using: If exists(...) drop table ...
> >I tried that and a hundred others and they all fail. I am tearing out my
> >hair.
> >
> >Normally I just check a book and I have the answer, but ASP is mostly out
of
> >print, and I have only a few very basic books on ASP.
>
> You confused me with the "MySQL" bit in your code where later on you
> CreateObject with a connection to a MDB file and use SQL what looks
> like SQL Server syntax, can you confirm what database you are actually
> using? (MySQL, Jet or SQL Server)
Access 2002 (mysql is only a response.write for debugging)
>
> Jet will most definately barf at that SQL syntax.
> If you have a MDB with links to SQL Server tables, you should create
> your connection direct to the SQL Server as Jet will try to interpret
> your SQL and not understand if it's SQL Server specific.
> If a jet database, the system table is called MSysObjects, jet/SQL
> doesn't (AFAICS) have a "IF" so you'd need open a recordset based on
> MSysObjects to determine if that table exists in VBA then execute the
> drop if necessary.
google.com has hundreds of references to this, but they don't work for me. I
can't understand why, but it highlights the wrongs of blindly using code.
The problem is something to do with the N' it seems in the where. I don't
know what N' does, so I don't understand why it does not work. But judging
by posts, a lot of people have problems with drop table if exists.
Trevor Best
oakley" <to006...@blueyonder.co.uk> wrote:
>
>Access 2002 (mysql is only a response.write for debugging)
>
[]
>
>google.com has hundreds of references to this, but they don't work for me. I
>can't understand why, but it highlights the wrongs of blindly using code.
>The problem is something to do with the N' it seems in the where. I don't
>know what N' does, so I don't understand why it does not work. But judging
>by posts, a lot of people have problems with drop table if exists.
The N' on SQL Server casts the text as unicode for use with nchar,
nvarchar and ntext data types however since since you're using Access
(Jet database) and not SQL Server your syntax will not work.
AFAICS Jet does not support "if exists".
For ASP what I'd do is write a function called DropTable then use it
thus:
Function DropTable (pstrTable)
On Error Resume Next
' assuming you have a connection named "cn"
cn.Execute "Drop Table [" & pstrTable & "]"
End Function
DropTable "MyTable"
You might want to check err.number after the execute to see if it's 0
(dropped ok) or 3376 (table doesn't exist) well if it didn't exist in
the first place then we're now at a stage where we wanted to be. You
might want to handle any other error differently.