如何将数据从excel导入.mdb访问文件？ [英] How to import the data from excel to the .mdb access file?

问题描述

嗨朋友们，



这里我有一些问题是将excel中的数据导出为.mdb的格式。即时尝试代码显示如下，但它显示messageBox {导入失败，正确列表中的列名！，错误消息：'Microsoft.Jet.OLEDB.4.0'提供程序未注册本地机器} 。是有人可以帮助我。



最好的问候，

Thanes

Hi Friends,

Here i m have some problem were export the data which in excel to the format of .mdb. i m trying the code were showing below, but it shows the messageBox "{Import Failed, correct Column name in the sheet!, Error Message: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine}" . is that anyone can help me.

Best Regards,
Thanes

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

    ' delete the file with the same and create a new access file
    If File.Exists("C:\Users\Admin\Desktop\test\CA\book.mdb") Then
        File.Delete("C:\Users\Admin\Desktop\test\CA\book.mdb")
    End If

    Dim _accessData As Access.Application
    _accessData = New Access.Application()
    _accessData.Visible = False
    _accessData.NewCurrentDatabase("C:\Users\Admin\Desktop\test\CA\book.mdb", Access.AcNewDatabaseFormat.acNewDatabaseFormatAccess2000, , , )

    _accessData.CloseCurrentDatabase()
    _accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll)
    _accessData = Nothing

    ' 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=C:\Users\Admin\Desktop\test\CA\test.xls" & _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 Book.mdb.
    Dim _command As OleDbCommand = New OleDbCommand()
    _command.Connection = _connection

    Try
        _command.CommandText = "SELECT * INTO [MS Access;Database=C:\Users\Admin\Desktop\test\CA\book.mdb].[Sheet1] FROM [Sheet1$A4:D]"
        _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,

*注意：该程序可以创建.mdb文件，但无法将excel数据导入其中。

*Note: the program is can create .mdb file, but cannot import the excel data into it.

推荐答案

A4：D]
_connection.Open（）
_command.ExecuteNonQuery（）
_connection.Close（）
MessageBox.Show（ 导入完成！）

Catch e1 作为异常
MessageBox.Show（ 导入失败，正确列表中的列名！& Environment.NewLine& 错误消息：& Environment。 NewLine& e1.Messag e）
结束 尝试

结束 Sub ，

A4:D]" _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,

*注意：程序可以创建.mdb文件，但不能将excel数据导入其中。

*Note: the program is can create .mdb file, but cannot import the excel data into it.

在继续之前，请确保excel中的列名与.mdb文件中的列名相同。您收到此错误是因为您的系统没有Access数据库引擎。安装访问数据库引擎并重新启动系统。（如果要求）。

Before proceeding further make sure column names in the excel should be same as in the .mdb file. You are getting this error because your system don't have Access Database Engine. Install Access Database Engine and restart your system.(if it's asked for).

SELECT ... INTO Statement（ Microsoft Access SQL） [ ^ ]错误！

SELECT…INTO Statement (Microsoft Access SQL)[^] is wrong!

SELECT * INTO [MS Access;Database=C:\Users\Admin\Desktop\test\CA\book.mdb].[Sheet1] FROM [Sheet1

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