rename an access table with VBscript 1

[prinand]

I am using VBscript in a webpage to modify tables, and one of the tasks is to change the name of a table, but this fails so far...

so I have my database (access XP database) and

connectionstring = "provider=microsoft.jet.oledb.4.0;data source=D:\database.mdb;persist security info=false"

set db = Server.CreateObject("ADODB.Connection")
db.Open connectionstring
Qry = "RENAME TABLE " & oldname & " TO '" & newname & "'"
db.execute Qry

but access does not know the rename command....
So any idea how I could achieve this ???

 

[Crowley16]

hmm, don't know of a rename table command in sql...

maybe you can try SELECT * FROM oldtblname INTO newtblname

and DROP oldtablename

Crowley - as in like a Crow

 

[BNPMike]

Microsoft don't expose metadata in a relational model. Ted Codd doesn't rule in Redmond. You need to walk the collections of the Database object.

 

[prinand]

thanks, I was hoping for a quicker solution but now I am indeed creating a new table, then insert, and then drop old table :

Qry = "CREATE TABLE [" & snewname & "] (id COUNTER, etc)"
db.execute Qry
Qry = "INSERT INTO [" & snewname & "] SELECT * FROM [" & sname & "]"
db.execute Qry
Qry = "DROP table [" & sname & "]"
db.execute Qry

and that does the trick. I tried combing the insert and create table, but that failed.
or... does anyone know how to copy a table in just one query (now my create must be exactly match the original table, I rather do not bother about the format of the original table, but just copy the format)

 

[flyover789]

you can use the copy object command:

DoCmd.CopyObject , "newname", acTable, "oldname"


HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[flyover789]

or in SQL.

SELECT yourtbl.* INTO newtable FROM yourtbl;

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[prinand]

Flyover, thanks. the SQL statement works perfectly.

so it is reduced now to :

qry = "SELECT * INTO [" & snewname & "] FROM [" & sname & "]"
db.execute Qry
Qry = "DROP table [" & sname & "]"
db.execute Qry

Thanks All !

 

[PHV]

Be aware that the new tabble lacks keys, indexes, relations, default values, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244