如何在VB.NET中通过SQL查询创建MS Access临时表及相关指导

一、创建MS Access临时表的方法 #

MS Access的临时表分两类：

• 本地临时表：名称以#开头（如#MyTempTable），仅在当前数据库连接会话中存在，关闭连接后自动删除，无需手动清理
• 普通临时表：如你使用的MyTempTable，会永久保存在数据库中，直到手动删除

在VB.NET中创建MyTempTable的标准流程：

1. 先检查表是否存在（避免重复创建报错）
2. 不存在则执行CREATE TABLE语句定义表结构

判断表存在的SQL（查询Access系统表MSysObjects）：

SELECT COUNT(*) FROM MSysObjects WHERE Name = 'MyTempTable' AND Type = 1

创建表的SQL语句（匹配你的字段需求）：

CREATE TABLE MyTempTable (
    ID COUNTER PRIMARY KEY,
    EMPID TEXT(50),
    [NAME] TEXT(100),
    [TOTAL CPI] TEXT(20)
)

二、表单加载时临时表的存在性 #

• 若使用普通临时表MyTempTable：表单重新加载时，只要数据库文件未被重置，表会一直存在，包括之前存储的数据
• 若使用本地临时表#MyTempTable：每次表单加载建立新连接时，旧表已自动消失，需重新创建

三、重复记录的处理：必须先清空再填充 #

如果表已存在且有旧数据，直接插入新数据会导致记录重复，因此必须先清理旧数据，再执行插入操作。常用两种方式：

1. 清空数据：DELETE * FROM MyTempTable（保留表结构，适合结构固定的场景）
2. 删除表重建：DROP TABLE MyTempTable + 重新创建表（适合表结构可能变化的场景）

四、现有代码修正建议 #

你的原代码存在几个关键问题：

1. jokenselect方法仅设置了命令文本，未执行SQL（如ALTER TABLE需要调用ExecuteNonQuery才会生效）
2. 未先判断表是否存在，第一次运行时会报错找不到MyTempTable
3. ALTER TABLE MyTempTable ALTER COLUMN ID COUNTER(1,1)语句多余，创建表时ID已设为自增字段，重复执行会报错

修正后的完整流程代码：

' 执行非查询SQL（CREATE/ALTER/INSERT/DELETE/UPDATE）
Public Sub ExecuteNonQuerySQL(ByVal sql As String)
    Try
        con.Open()
        cmd.Connection = con
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
    Finally
        con.Close()
    End Try
End Sub

' 检查指定表是否存在
Private Function CheckTableExists(tableName As String) As Boolean
    Dim exists As Boolean = False
    Try
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT COUNT(*) FROM MSysObjects WHERE Name = @TableName AND Type = 1"
        cmd.Parameters.AddWithValue("@TableName", tableName)
        exists = CInt(cmd.ExecuteScalar()) > 0
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Information)
    Finally
        con.Close()
        cmd.Parameters.Clear()
    End Try
    Return exists
End Function

' 表单加载事件处理
Private Sub Form_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ' 1. 检查并创建临时表
    If Not CheckTableExists("MyTempTable") Then
        ExecuteNonQuerySQL("CREATE TABLE MyTempTable (" & _
                           "ID COUNTER PRIMARY KEY, " & _
                           "EMPID TEXT(50), " & _
                           "[NAME] TEXT(100), " & _
                           "[TOTAL CPI] TEXT(20))")
    End If

    ' 2. 清空临时表旧数据
    ExecuteNonQuerySQL("DELETE * FROM MyTempTable")

    ' 3. 插入计算后的新数据
    Dim insertSql As String = "INSERT INTO MyTempTable (EMPID, [NAME],[TOTAL CPI]) " & _
                              "SELECT EC.EMPID, E.[NAME], " & _
                              "format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria)+" & _
                              "EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria)+" & _
                              "EC.APPEARANCE/(select MIN(MINSKILL.APPEARANCE) from tblEmployeeCriteria MINSKILL)*100*(select WCAPPEARANCE/100 from tblWeightingCriteria)+" & _
                              "EC.EDUCATION/(select MIN(MINSKILL.EDUCATION) from tblEmployeeCriteria MINSKILL)*100*(select WCEDUCATION/100 from tblWeightingCriteria),'#,##0.00') as [TOTAL CPI] " & _
                              "FROM tblEmployeeCriteria EC INNER JOIN tblemployee E ON EC.EMPID = E.EMPID " & _
                              "ORDER BY 3 DESC"
    ExecuteNonQuerySQL(insertSql)

    ' 4. 填充DataGridView
    cmd.CommandText = "SELECT [NAME], format([Total CPI],'#,##0.00') AS [Total CPI] FROM MyTempTable;"
    filltable(DataGridView3, "Temp")
End Sub

' 修正后的filltable方法（修复Case语法错误）
Public Sub filltable(ByVal dtgrd As Object, ByVal design As String)
    Dim publictable As New DataTable
    Try
        da.SelectCommand = cmd
        da.Fill(publictable)
        dtgrd.DataSource = publictable
        Select Case design
            Case "EmpInfo"
                dtgrd.Columns(0).Visible = False
            Case "EmpPic"
                dtgrd.MultiSelect = False
                dtgrd.SelectionMode = DataGridViewSelectionMode.FullRowSelect
                dtgrd.ReadOnly = True
                dtgrd.Columns(0).Visible = False
            Case "Temp"
                ' 可添加Temp视图的自定义设置
        End Select
        da.Dispose()
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Information)
    End Try
End Sub

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