VB.NET - Modifying MS Access Table Name with ADOX

Question

I'm trying to modifying a table name based on two textbox inputs. I have one form creating the table using OleDb so I want to use the inputted information from that form to search for the table in the database then rename it to what the use wants on the current form.

Example output From AAAAAAA_000 To BBBBBBB_111

Import ADOX
Import ADODB

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnModify.Click
    Dim ADOXCatalog As New ADOX.Catalog
    Dim ADOConnection As New ADODB.Connection
    Dim uc As Char = "_"
    ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Users\...\Documents\Visual Studio 2012\Projects\WindowsApplication3\WindowsApplication3\timer.mdb;" & _
    "Jet OLEDB:Engine Type=5;")

    ADOXCatalog.ActiveConnection = ADOConnection
    ADOXCatalog.Tables('" & frmOpen.TextBox1.Text & uc.ToString & frmOpen.TextBox2.Text & "').Name ='" & txtTextBox1.Text & uc.ToString & TextBox2.Text & "'

    ADOXCatalog.ActiveConnection.Close()
    ADOXCatalog.ActiveConnection = Nothing

    Me.Close()

End Sub

I know that this line seems to be the issue `ADOXCatalog.Tables('" & frmOpen.TextBox1.Text & uc.ToString & frmOpen.TextBox2.Text & "').Name ='" & txtTextBox1.Text & uc.ToString & TextBox2.Text & "'` However I'm not familiar with how to grab that text from a textbox to input into a database using ADO — Karl, Apr 03 '13 at 21:25

score 0 · Answer 1 · answered Apr 03 '13 at 21:37

0

Note: I am assuming that the table can be renamed by setting the Name property.
However, I haven't done that

dim oldTableName as string
dim newTableName as string

oldTableName = frmOpen.TextBox1.Text & uc.ToString & frmOpen.TextBox2.Text
newTableName = txtTextBox1.Text & uc.ToString & TextBox2.Text

ADOXCatalog.Tables(oldTableName).Name = newTableName

A few points to think of

Change the TextBox1, TextBox2 to appropriate name such as TablePrefix, TableSuffix.
You don't need uc.ToString, when uc is a string (e.g. const uc as String = "_")

answered Apr 03 '13 at 21:37

shahkalpesh

33,172
3
63
88

I got this error "Item cannot be found in the collection corresponding to the requested name or ordinal." – Karl Apr 03 '13 at 22:03
Whats the value of `oldTableName`? Does such a table exist in the database? – shahkalpesh Apr 03 '13 at 22:30
Yep, it exists. I have the frmOpen window come up on start where I'm able to type in a document number and revision in two textboxes (frmOpen.TextBox1 & frmOpen.Textbox2) then frmOpen window hides while the main window opens where I have a menu option to allow the table that was created to be modified where this code comes into play. Since the first window is hidden and not closed the text fields are still populated with this information. – Karl Apr 03 '13 at 23:32
@KarlBaker: Considering that you loop through table collection to match the item with `oldTaleName`, does the `Tables(oldTableName)` syntax not work? – shahkalpesh Apr 04 '13 at 06:13

score 0 · Answer 2 · answered Apr 04 '13 at 05:16

I found a way to modify a table name. I had to add ADOX.Table into the code with a for statement and use that to find the original table name (based on the data in the frmOpen window) with an if/then statement.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnModify.Click
    Dim ADOXCatalog As New ADOX.Catalog
    Dim ADOXTable As ADOX.Table

    Dim ADOConnection As New ADODB.Connection

    Const uc As String = "_"

    Dim oldTableName As String
    Dim newTableName As String

    oldTableName = frmOpen.TextBox1.Text & uc & frmOpen.TextBox2.Text
    newTableName = txtABINum.Text & uc & txtABIRev.Text

    ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Users\028861\Documents\Visual Studio 2012\Projects\WindowsApplication3\WindowsApplication3\timer.mdb;" & _
    "Jet OLEDB:Engine Type=5;")
    Try
        ADOXCatalog.ActiveConnection = ADOConnection
        For Each ADOXTable In ADOXCatalog.Tables
            If ADOXTable.Name = oldTableName Then
                ADOXTable.Name = newTableName
            End If
        Next ADOXTable
        ADOXCatalog.ActiveConnection.Close()
        ADOXCatalog.ActiveConnection = Nothing
        Me.Close()
    Catch ex As Exception

        MsgBox(ex.Message, MsgBoxStyle.Critical, "ADOX Error")

    End Try
End Sub

VB.NET - Modifying MS Access Table Name with ADOX

2 Answers2

Linked