need correct ALTER TABLE syntax

Hello, I've been struggling with the syntax for adding new columns to an
Access 2000 db table through an admin .asp page. I found lots of examples,
but not 1 gives me the right combination. I am getting errors on everything
I try. Some errors are syntax, some say there is an invalid or unqualified
reference, and others say the object or provider is not capable of
performing the requested operation. Here are 2 samples of my attempts:

set cat = server.CreateObject("ADOX.Catalog")
set cat.ActiveConnection = objConn
Dim objTable, objColumn, objField, sfldname, ttable, dtype

Set objTable = server.CreateObject("ADOX.Table")
sfldname=lcase(request.form("fld"))
stname=request.form("tbl")
for each objTable in cat.tables
if objTable.Type = "TABLE" and lcase(objTable.name)=lcase(stname)
then
objconn.Execute "ALTER TABLE " & objTable.name & " ADD COLUMN "
& sfldname & " INTEGER"
'or I tried variations of the one below:
'objTable.Columns.Append sfldname, adInteger
else
end if
next

Any insights or help would be most appreciated,

~ Sue

Re: need correct ALTER TABLE syntax

Sue Barth wrote:
> Hello, I've been struggling with the syntax for adding new columns to
> an Access 2000 db table through an admin .asp page. I found lots of
> examples, but not 1 gives me the right combination. I am getting
> errors on everything I try. Some errors are syntax, some say there
> is an invalid or unqualified reference, and others say the object or
> provider is not capable of performing the requested operation. Here
> are 2 samples of my attempts:
> set cat = server.CreateObject("ADOX.Catalog")
> set cat.ActiveConnection = objConn
> Dim objTable, objColumn, objField, sfldname, ttable, dtype
>
> Set objTable = server.CreateObject("ADOX.Table")

This line is not necessary, since you later discard this table object in the
"for each" statement.

> sfldname=lcase(request.form("fld"))
> stname=request.form("tbl")
> for each objTable in cat.tables
> if objTable.Type = "TABLE" and
> lcase(objTable.name)=lcase(stname) then
> objconn.Execute "ALTER TABLE " & objTable.name & " ADD
> COLUMN " & sfldname & " INTEGER"

We cannot debug a dynamic sql statement unless we see what it is. This means
you must write it to the response object:

sql= "ALTER TABLE " & objTable.name & _
" ADD COLUMN " & sfldname & " INTEGER"

Response.Write sql & "
"

If you've created the statement correctly, you should be able to copy the
statement from the browser window to the clipboard, open your database in
Access, create a new query in SQL View, paste the statement in and run it
without modification. If it fails, you may get a more meaningful error
statement than the one reported to ADO.

> 'or I tried variations of the one below:
> 'objTable.Columns.Append sfldname, adInteger

Yes, why bother with JetSQL when you already have a reference to a table
object? Do you know where the ADOX documentation is? It can be found here:
http://msdn.microsoft.com/library/en-us/ado270/htm/admscadoa pireference.asp


When you go there, you will see that the syntax for the Append method is:
Columns.Append Column [, Type] [, DefinedSize]

Since you did not tell us what error you received for your attempt shown
above (which seems correct), it is impossible for me to say what went wrong.
Have you defined the adInteger constant? See
http://www.aspfaq.com/show.asp?id=2112

Here are several ADOX-related articles on ASPFAQ:
http://www.aspfaq.com/search.asp?q=ADOX

One last thing: unless you are creating an application for administrators
to, well, administer, Access databases, typically the need to add columns on
the fly is indicative of poor or inadequate database design. If I've raised
a doubt in your mind, tell us a little bit about your requirements and we
may be able to steer you towards a more robust solution.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: need correct ALTER TABLE syntax

And for a related link:

http://www.w3schools.com/sql/sql_alter.asp

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips