如何在VB.net(VS2010)中通过TextBox创建指定表名的Access2010表？

Hey there! I've tackled this exact scenario before, so here's a practical, step-by-step solution to create an Access 2010 table using VB.NET (Visual Studio 2010) where the table name comes from a TextBox input:

First, Prep Your Project #

• Ensure your WinForms project references System.Data.OleDb (it’s included by default in VS2010, but double-check your project references just in case).
• Have an existing Access 2010 database (.accdb file) ready – create one in Access 2010 if you don’t already have one.

Full Button Click Event Code #

Drop this into your form’s code-behind. I’ve included input validation, error handling, and clear comments to walk you through each step:

Imports System.Data.OleDb
Imports System.Text.RegularExpressions

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' Grab the input table name from TextBox1
        Dim tableName As String = TextBox1.Text.Trim()

        ' Step 1: Validate the input to avoid errors
        If String.IsNullOrEmpty(tableName) Then
            MessageBox.Show("Please enter a table name!", "Input Missing", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Return
        End If

        ' Block invalid characters (Access only allows letters, numbers, underscores)
        Dim invalidCharCheck As New Regex("[^a-zA-Z0-9_]")
        If invalidCharCheck.IsMatch(tableName) Then
            MessageBox.Show("Table names can only use letters, numbers, and underscores!", "Invalid Name", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End If

        ' Check for Access reserved words (e.g., "Table", "Select")
        Dim reservedWords As New List(Of String) From {
            "ADD", "ALL", "ALTER", "AND", "ANY", "AS", "ASC", "AUTOINCREMENT",
            "BETWEEN", "BY", "CASCADE", "CASE", "COLUMN", "CONSTRAINT", "CREATE",
            "DATE", "DELETE", "DESC", "DISTINCT", "FROM", "GROUP", "IN", "INSERT",
            "INTO", "JOIN", "KEY", "LIKE", "NULL", "NUMBER", "ON", "OR", "ORDER",
            "PRIMARY", "SELECT", "SET", "TABLE", "TEXT", "UPDATE", "WHERE"
        }
        ' Add more reserved words if needed – this is a condensed list

        If reservedWords.Contains(tableName.ToUpper()) Then
            MessageBox.Show("That's an Access reserved word – pick a different name!", "Invalid Name", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End If

        ' Step 2: Set up the database connection string
        ' Replace the file path with your actual .accdb location
        Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourFolder\YourDatabase.accdb;Persist Security Info=False;"

        ' Step 3: Build the CREATE TABLE SQL (customize columns here!)
        ' I added an autonumber ID and a text column as an example
        Dim createTableSql As String = $"CREATE TABLE [{tableName}] (
            ID COUNTER PRIMARY KEY,
            ItemName TEXT(255),
            Price CURRENCY,
            CreatedDate DATETIME
        )"

        ' Step 4: Execute the SQL command
        Using connection As New OleDbConnection(connectionString)
            Try
                connection.Open()
                Using command As New OleDbCommand(createTableSql, connection)
                    command.ExecuteNonQuery()
                    MessageBox.Show($"Table '{tableName}' created successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    TextBox1.Clear() ' Clear input after success
                End Using
            Catch ex As OleDbException
                ' Handle common Access errors
                If ex.ErrorCode = -2147217900 Then
                    MessageBox.Show($"Table '{tableName}' already exists!", "Duplicate Table", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Else
                    MessageBox.Show($"Database error: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If
            Catch ex As Exception
                ' Catch-all for other issues (file not found, permission errors)
                MessageBox.Show($"Unexpected error: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using
    End Sub
End Class

Important Tips #

• Database Path: Update the Data Source in the connection string to match your .accdb file location. For a relative path (e.g., database in your project’s bin folder), use Application.StartupPath & "\YourDatabase.accdb".
• Column Customization: Tweak the CREATE TABLE statement to add/remove columns or change data types (Access supports TEXT, NUMBER, DATE/TIME, CURRENCY, etc.).
• Permissions: Make sure your app has read/write access to the database file. Avoid storing the file in protected folders like Program Files unless you adjust permissions.
• Safety: Wrapping the table name in square brackets [] prevents issues if you ever allow spaces in names (our validation blocks them now, but it’s a good habit).

内容的提问来源于stack exchange，提问作者Ysoga