How To Change an Access Table Name Programmatically (291017)


The information in this article applies to:

  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Visual Basic Professional Edition for Windows 5.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 5.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0 SP3
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0 SP4
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0 SP5
This article was previously published under Q291017

SUMMARY

There are several ways to change a Microsoft Access table name in a Visual Basic application, some of which include the use of SQL, OLE automation, or Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX). The simplest method is to use ADOX to rename a table.

MORE INFORMATION

To use a Visual Basic 6.0 project to change an Access table name programmatically by using ADOX, do the following:
  1. Open a new Standard EXE project in Visual Basic. Form1 is created by default.
  2. On the Project menu, click to select References, and then add references to Microsoft ActiveX data object 2.x library and Microsoft ADO Ext. 2.x for DDL and Security.
  3. Paste the following code into the form load event:
    Dim cn As ADODB.Connection
Dim t As Table

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data Source=C:\biblio.mdb"
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
cat.Tables("Authors").Name = "Authors2"

'Iterate through the tables collection
For Each t In cat.Tables
  If t.Name = "Authors2" Then
    Debug.Print t.DateModified
  End If
Next

cn.Close
Set cn = Nothing
  4. Save and execute the Visual Basic project, and note that the Immediate window displays the DateModified property value of the "Authors2" table, which is equal to the current day's date.
Modification Type:MinorLast Reviewed:6/29/2004
Keywords:kbhowto KB291017
©2004 Microsoft Corporation. All rights reserved.