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.
