When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
User Tips: Renaming Access Database Tables


By Justin Macklin

Introduction
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 a 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 Table object was read-write! This meant that I could rename a table in an Access database simply by setting the Name property of a Table object that corresponded to the database table I wanted to rename.

The Code
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 (newName).

Sub RenameTable (conStr, oldName, newName)
  'Create object and connect to DB...
  Dim objADOXDatabase
  Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")
  objADOXDatabase.ActiveConnection = conStr

  'Change the name...
  objADOXDatabase.Tables(oldName).Name = newName

  'Clean up...
  Set objADOXDatabase = Nothing
End Sub

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 accessed. The Name property of table oldName is then changed to newName. Finaly the code cleans up by setting the catalog object to Nothing.

There is no reason the code couldn't be adapted to rename other objects in the database. The MSDN Library (http://msdn.microsoft.com/library/en-us/ado270/htm/adproname.asp) states that the Name property is read/write on the Column, Group, Key, Index, Table, and User objects, but read only on the Catalog, Procedure, and View objects.

A Complete Example
The following example renames a table called test to changed in a database located at c:\example.mdb.

<%
Call RenameTable("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\example.mdb", "test", "changed")

Sub RenameTable (conStr, oldName, newName)
  'Has to be OLE DB connection
  'Create object and connect to DB...
  Dim objADOXDatabase
  Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")
  objADOXDatabase.ActiveConnection = conStr
 
  'Change the name...
  objADOXDatabase.Tables(oldName).Name = newName
  
  'Clean up...
  Set objADOXDatabase = Nothing
End Sub
%>

Limitations
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...


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article