How To Change an Access Table Name Programmatically

Article translations Article translations
Article ID: 291017 - View products that this article applies to.
This article was previously published under Q291017
Expand all | Collapse all

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.

Properties

Article ID: 291017 - Last Review: June 29, 2004 - Revision: 4.2
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 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 3
  • Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 4
  • Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 5
Keywords: 
kbhowto KB291017

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com