ADOX problem...?

I need to rename tables in code VB2005 (access database). On the

forum I found post about ADOX and this code to rename the tables:

Private Sub RenameTables(ByVal sTextToRemove As String)

Dim i As Integer

Dim dbRename As Database

Dim Connect As New PrivDBEngine

dbRename = Connect.OpenDatabase(tbDBPath.Text)

dbRename.CreateTableDef()

For i = 0 To dbRename.TableDefs.Count - 1

If dbRename.TableDefs(i).Name.Length >= sTextToRemove.Length - 1 Then

If dbRename.TableDefs(i).Name.Substring(0, 9) = sTextToRemove Then

dbRename.TableDefs(i).Name = dbRename.TableDefs(i).Name.Substring(9)

End If

End If

Next i

dbRename.Close()

dbRename = Nothing

MessageBox.Show("Tables in " + tbDBPath.Text + " have been renamed.

Rename Access Tables", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

But it doesn't work because i get an error on Dim dbRename As Database Dim Connect As New PrivDBEngine

What I need to do for this to work?

Thank you!

The code is for DAO so the first thing you need to do is add the DAO 3.6 COM library reference to your application (Project...Add Reference).

Then, I would declare those objects and fully qualify the types using the library name:

Dim dbRename As DAO.Database
Dim Connect As DAO.PrivDBEngine

|||

Hi,

Please do the following process to add the required references,

1. Right click on your project in Solution explorer and select Add references...
2. Select COM tab and search for following two libraries and add it,
- Microsoft ADO Ext. 2.8 for DLL and Security
- Microsoft DAO 3.6 Object Library
To see those dlls in References section,
Select Project -> Show all files
So under the References the following two dlls will be shown:
1. ADOX
2. DAO

The code does the following things in MS Access
1. Create database
2. Create a table
3. Rename a table

[The following code creates TestDB.mdb in C:\ and create a table named MyTable. Then it renamed as "MyNewNameTable"]

Imports ADOX

Imports DAO

Public Class Form1

Dim catalog As Catalog = New ADOX.Catalog()

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

CreateDatabaseAndTable("TestDB.mdb", "MyTable")

RenameTables("MyTable", "MyNewNameTable")

End Sub

Private Sub CreateDatabaseAndTable(ByVal databasename As String, ByVal tablename As String)

catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\" + databasename + ";" & _

"Jet OLEDB:Engine Type=5")

Console.WriteLine("Database Created Successfully")

Dim firsttable As Table = New Table()

firsttable.Name = tablename

firsttable.Columns.Append("CustID", ADOX.DataTypeEnum.adInteger)

firsttable.Columns.Append("CustName", ADOX.DataTypeEnum.adVarWChar, 30)

firsttable.Keys.Append("PK_CustID", 1, "CustID")

catalog.Tables.Append(firsttable)

firsttable = Nothing

catalog = Nothing

End Sub

Private Sub RenameTables(ByVal sOldName As String, ByVal sNewName As String)

Dim i As Integer

Dim databasepath = "C:\TestDB.mdb"

Dim dbRename As Database

Dim Connect As New PrivDBEngine

dbRename = Connect.OpenDatabase(databasepath)

dbRename.CreateTableDef()

For i = 0 To dbRename.TableDefs.Count - 1

Try

If dbRename.TableDefs(i).Name = sOldName Then

dbRename.TableDefs(i).Name = sNewName

End If

Catch ex As Exception

Console.WriteLine(ex.Message)

End Try

Next i

dbRename.Close()

dbRename = Nothing

MessageBox.Show("Tables in " & databasepath & " have been renamed. Rename Access Tables")

End Sub

End Class

For further information, please refer,
http://www.4guysfromrolla.com/webtech/013101-1.shtml
http://www.4guysfromrolla.com/webtech/tips/t030802-1.shtml
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/msjet/jetch02.mspx?mfr=true
http://www.codeguru.com/vb/gen/vb_database/microsoftaccess/article.php/c5149/

HTH,