How do I loop through tables in an access database in VB.NET?

Question

0.00/5 (No votes)

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

Posted 14-Jan-20 17:24pm

Member 11561472

Updated 14-Jan-20 20:18pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2020-01-14T17:46:00

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[^]

OriginalGriff · Answer 2 · 2020-01-14T19:58:00

To read the table names from Access is simple: issue an SQL command:

SQL

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!

How do I loop through tables in an access database in VB.NET?

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0