无法使用vb.net将数据从Excel导入到.mdb访问文件 [英] Cannot import the data from Excel to .mdb access file using vb.net

问题描述

朋友，

我最近正在做一个程序，使用vb.net将数据从Excel导入到.mdb访问文件.但是我有一些问题，Excel文件中的数据无法导入到访问文件中.谁能帮我?请您帮忙....

谢谢.

最好的问候，
泰恩斯

Hi Friends,

I m recently doing a program were import the data from Excel to .mdb access file using vb.net. But i have some problem, the data were inside the excel file is cannot import into the access file. can anyone help me? Please your help is required....

Thanks.

Best Regards,
Thanes

 Private DBPath As String
    Private conn As OleDbConnection
    Public Sub New()
        InitializeComponent()
    End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

DBPath = "C:\Users\Admin\Desktop\test\CA\test.mdb"

        
        If Not File.Exists(DBPath) Then
            Dim cat As New ADOX.Catalog()
            cat.Create(Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=") & DBPath)
            cat = Nothing
            MsgBox("Done create DBPath")
        End If

        conn = New OleDbConnection(Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=") & DBPath)
        conn.Open()
        MsgBox("Done connected to DBPath")
        Try
            Using cmd As New OleDbCommand("CREATE TABLE [test] ([id] COUNTER PRIMARY KEY, [no] INT, [name] MEMO, [class] INT);", conn)
                cmd.ExecuteNonQuery()
            End Using
            MsgBox("Done create Table and Columm")
        Catch ex As Exception
            If ex IsNot Nothing Then
                ex = Nothing
                MsgBox("Error in creating Table or Collum")
            End If
        End Try

        ' initialize the connect string
        Dim _filename As String = "C:\Users\Admin\Desktop\test\CA\test.xls"
        Dim _conn As String
        _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

        Dim _connection As OleDbConnection = New OleDbConnection(_conn)

        'Use OledbCommand object to select all the data from sheet1 and execute an ExecuteNonQuery to import data into test.mdb.
        Dim _command As OleDbCommand = New OleDbCommand()
        _command.Connection = _connection

        Try
            _command.CommandText = "SELECT num,name,no FROM [Sheet1$]"
            _connection.Open()
            _command.ExecuteNonQuery()
            _connection.Close()
            MessageBox.Show("The import is complete!")

        Catch e1 As Exception
            MessageBox.Show("Import Failed, correct Column name in the sheet!" & Environment.NewLine & "Error Message:" & Environment.NewLine & e1.Message)
        End Try

    End Sub
End Class

推荐答案

" _connection.Open() _command.ExecuteNonQuery() _connection.Close() MessageBox.Show(" ) 捕获 e1 As 异常 MessageBox.Show(" & Environment.NewLine& ; " & Environment.NewLine& e1.Message) 结束 尝试 结束 子 结束 类

" _connection.Open() _command.ExecuteNonQuery() _connection.Close() MessageBox.Show("The import is complete!") Catch e1 As Exception MessageBox.Show("Import Failed, correct Column name in the sheet!" & Environment.NewLine & "Error Message:" & Environment.NewLine & e1.Message) End Try End Sub End Class

您需要阅读excel并使用insert sql语句插入到Access数据库中，当前您正在创建访问文件和表，但未插入数据.使用select sql语句从excel文件读取不会插入数据以访问数据库.示例代码:

you need to read from excel and insert into access database using insert sql statement, currently you are creating access file and table but not inserting data. reading from excel file using select sql statement will not insert data to access database. sample code :

Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _filename + ";Extended Properties=Excel 8.0;"
Using conn As New OleDbConnection(connect)
	Using cmd As New OleDbCommand()
		cmd.Connection = conn
		cmd.CommandText = "INSERT INTO [MS Access;Database=" + DBPath + "].[test] SELECT * FROM [Sheet1

" conn.Open() cmd.ExecuteNonQuery() 结束 使用 结束 使用}

" conn.Open() cmd.ExecuteNonQuery() End Using End Using}

这篇关于无法使用vb.net将数据从Excel导入到.mdb访问文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！