Cannot create table using VBSCRIPT.

[Thomas L. Ickes]

(Sorry about the first post......hit CTRL-ENTER while trying to clean up my
gibberish).

Anyway, to continue.

I need to create a table on the fly using vbscript. It's an older version
database so I cannot directly make changes since I cannot convert the table
(it's a customer's). My SQL command is simple enough "CREATE TABLE NewTable
( 'newCOLname' int )". Every time I try to execute it I receive:
a.. Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in CREATE TABLE statement.
/stripCASE/clearCASES.asp, line 268

I'm going bonkers! I also tried using ADOX

DIM objADOXDatabase SET
objADOXDatabase = Server.CreateObject("ADOX.Catalog")
objADOXDatabase.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=f:\Inetpub\wwwroot\stripCASE\schedule.test.mdb"
DIM objFirstTable
SET objFirstTable = Server.CreateObject("ADOX.Table")
objFirstTable.Name = tableNAME & "archive"
objFirstTable.Columns.Append "CustID", adInteger
objFirstTable.Columns.Append "CustName", adVarWChar,30
objADOXDatabase.Tables.Append objFirstTable

The ADOX works okay, creates the database, table, and columns. Only problem
is, the database already exists and if I try running it on an existing
database I receive a message that the object is no longer valid.

Does anybody have a clue as to why my simple CREATE TABLE does not work or
how to us something else to add these tables.

My last resort is to create a completely new database with all the tables,
columns, etc but the customer has a MASSIVE amount of forms and queries, I'd
hate to have to get in to that.

p.s. This routine is to archive old records but still have them available
for later use.

 

[Roger Carlson]

Only thing I can think of is to remove the single quotes from the
newCOLname.

 

[Thomas L. Ickes]

Tried that.....tks anyway.

Only thing I can think of is to remove the single quotes from the
newCOLname.

 

[Douglas J. Steele]

Already answered in another group to which you posted the same question.

If you feel you need to post to more than one group (HINT: it's seldom
necessary), please have the courtesy to cross-post (send the one message to
all groups at once), rather than multi-post (send individual messages to
each group). In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times. (It also uses fewer server
resources)

I see you're using Outlook Express. Click the "Newsgroups:" label to the
left of the box containing the name of the current newsgroup. That will open
a dialog that will let you add additional newsgroups to your post. Note that
it's generally consider to be A Bad Thing to cross-post to more than about 2
or 3 newsgroups. (In fact, at
http://www.microsoft.com/presspass/features/2001/Mar01/Mar27pmvp.asp
Microsoft suggests that "One group will suffice")

 

[John Nurick]

Just a thought: IIRC early versions of JET had little if any support for
SQL DDL. If your code successfully executes the CREATE TABLE in a new
database but not in this old-version one, could that be the cause? If
so, can you use DAO to add the table?