Click here to Skip to main content
14,422,431 members
Rate this:
Please Sign up or sign in to vote.
See more:
I want to create VP.Net code to get the names of all of the tables in an Access database and change the names of each table by adding “tbl” to the beginning of each table. Can this be done? Any help would be greatly appreciated.

What I have tried:

This is my start, but I am at a loss.

Imports System.Data.Oledb

Sub ChangeTableNames()
Dim varCountNumTables As Integer
Dim dt as DataTable

VarCountNumTables = dt.Tables.count
Dt.Tables(0).TableName = “NewTableName”

End Sub
Updated 5 days ago
Rate this:
Please Sign up or sign in to vote.

Solution 1

First of all, a data table does not contain tables. If you're talking about a data set then you must use a DataSet Class (System.Data) | Microsoft Docs[^]

You also wrote about an Access table so if these tables need to be renamed inside an Access database, you can use DoCmd.Rename method (Access) | Microsoft Docs[^]
Rate this:
Please Sign up or sign in to vote.

Solution 3

To read the table names from Access is simple: issue an SQL command:
SELECT Name FROM MSysObjects WHERE (Name Not Like "MSys*") AND (Type In (1,4,6))  ORDER BY Name
and it will return them.
But ... Access doesn't support table renaming via the ALTER TABLE SQL command, so you would have to create a new table with the new name and the same structure as the old, copy all the rows over and then delete the original.Since this sounds very much like a "one of" job, I'd do it manually in Access - it'll be a load easier, and probably both more reliable and quicker!
Richard MacCutchan 4 days ago
I just tried that and got an exception saying no read permission on MSysObjects. Any ideas?
OriginalGriff 4 days ago
I don't have either Access engine installed ATM so I can't test, but it's a permissions problem:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100