I'd been trying for days to rename a Microsoft Access table from ASP. Apparently it's easy to rename a SQL Server table using a stored procedure (
exec sp_rename oldtable, newtable), but there isn't an equivalent that
works in Access. It looked like the only way around it was to create a new
table and then use a
SELECT INTO statement to transfer the data, followed by
DROP statement to delete the original. Hardly efficient and really ugly.
Then I read the excellent article by by Ramesh Balaji and Scott Mitchell titled: Working with ADOX. If you haven't read the article yet, I highly encourage you to. Essentially, ADOX, which stands for ADO Extensions, is an additional ADO object layer that provides information to a database's schema. Using ADOX you can do tasks such as listing the tables of a database, the tables columns and data types, etc. After reading the ADOX article, I quickly realized that ADOX could be used to form the basis of a routine to perform a table rename in MS Access.
A bit of searching on MSDN found that the
Name property of the
object was read-write! This meant that I could rename a table in an Access database simply by
Name property of a
Table object that corresponded to the database
table I wanted to rename.
The following code shows a module function,
RenameTable, that expects as input
parameters a valid connection string (
conStr), the current (old) name of the table
oldName), and the name you want to rename the table to (
The code is fairly straightforward. It instantiates a
Catalog object and
sets its ActiveConnection property to the passed-in connection string.
This allows the schema objects (including the database tables) to be
Name property of table
oldName is then changed to
Finaly the code cleans up by setting the catalog object to
There is no reason the code couldn't be adapted to rename other objects in
the database. The MSDN Library
states that the
Name property is read/write on the
User objects, but read only on the
A Complete Example
The following example renames a table called
changed in a
database located at
The code only seems to work with OLEDB connections (not DSNless connections) as ones as shown in the example. Furthermore, appropriate Database and File permissions (NT/2000/XP) have to be in place to edit the contents of an Access database file. For clarity there is no error handling in the code. This can easily be added.
|Return to user tips...|