Excel File - Microsoft Access

[reubenfoo]

Hi all,

i would want to be able to import data from an excel file into a MS access database.. Pls check my codes to see what is wrong.

1. I've created a Primary key - APCITID increment of 1 in MS Access table
2. The table name is called "APCITI"
3. The Message Box Shows "The Import is Complete!" however no data goes in the Access Database.
4. Overall, the coding doesn't show any error but nothing is imported into access.

THE CODING IS:

Private Sub startBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startBtn.Click

        If TextBox1.Text = "" Then
            Me.Close()
        End If
        Dim _filename As String = TextBox1.Text
        Dim _conn As String

        _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 8.0;"
        Dim _connection As OleDbConnection = New OleDbConnection(_conn)
        Dim da As OleDbDataAdapter = New OleDbDataAdapter()
        Dim _command As OleDbCommand = New OleDbCommand()

        _command.Connection = _connection
        _command.CommandText = "SELECT * FROM [Sheet1$]"
        da.SelectCommand = _command
        Try
            da.Fill(ds1, "sheet1")

            MessageBox.Show("The import is complete!", "Import File", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch e1 As Exception
            MessageBox.Show("Import Failed!", "Import File", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try
        Dim daA As SqlDataAdapter
        Dim conn As SqlConnection
        Dim cb As SqlCommandBuilder

        conn = New SqlConnection("Data Source=Servername;Initial Catalog=mydb;Integrated Security=True")
        Dim sel As String = "SELECT * FROM APCITID"
        daA = New SqlDataAdapter(sel, conn)
        cb = New SqlCommandBuilder(daA)
        daA.MissingSchemaAction = MissingSchemaAction.AddWithKey
        da.Fill(ds2, "APCITID")

        For Each dr As DataRow In ds1.Tables(0).Rows
            Dim expression As String
            expression = "PMCO =" + CType(dr.Item(0), Integer).ToString
            Dim drs() As DataRow = ds2.Tables(0).Select(expression)
            If (drs.Length = 1) Then
                For i As Integer = 1 To ds2.Tables(0).Columns.Count - 1
                    drs(0).Item(i) = dr.Item(i)
                Next
            Else
                Dim drnew As DataRow = ds2.Tables(0).NewRow
                For i As Integer = 0 To ds2.Tables(0).Columns.Count - 1
                    drnew.Item(i) = dr.Item(i)
                Next
                ds2.Tables(0).Rows.Add(drnew)
            End If
        Next
        Form2.Show()
    End Sub

reubenfoo

 

[cjard]

last time I checked, a connection string like this:

SqlConnection("Data Source=Servername;Initial Catalog=mydb;Integrated Security=True")


was for SQLServer, not Access. Maybe your database is empty because you didnt insert anything into it (and all your data is in a SQLServer instance somewhere)

 

[reubenfoo]

i've changed the connection string to:

conn = New SqlConnection("Data Source=C:\mydatabase.mdb;User Id=admin;Password=;")

There is no error but the excel data does not get inserted into the access database table.

 

[JohnH]

SqlConnection ... mdb

System.Data.SqlClient classes doesn't work with Access, System.Data.OleDb classes does.

 

[reubenfoo]

i've changed my coding to:

Private Sub startBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startBtn.Click

       
        Dim _filename As String = TextBox1.Text
        Dim _conn As String

        _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 8.0;"
        Dim _connection As OleDbConnection = New OleDbConnection(_conn)
        Dim da As OleDbDataAdapter = New OleDbDataAdapter()
        Dim _command As OleDbCommand = New OleDbCommand()

        _command.Connection = _connection
        _command.CommandText = "SELECT * FROM [Sheet1$]"
        da.SelectCommand = _command
        Try
            da.Fill(ds1, "sheet1")

            MessageBox.Show("The import is complete!", "Import File", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch e1 As Exception
            MessageBox.Show("Import Failed!", "Import File", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try
        Dim daA As OleDbDataAdapter
        Dim conn As OleDbConnection
        Dim cb As OleDbCommandBuilder

        conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CitiDirect\CitiDirect.mdb;User Id=admin;Password=;")
        Dim sel As String = "SELECT * FROM APCITI"
        daA = New OleDbDataAdapter(sel, conn)
        cb = New OleDbCommandBuilder(daA)
        daA.MissingSchemaAction = MissingSchemaAction.AddWithKey
        da.Fill(ds2, "APCITI")

        For Each dr As DataRow In ds1.Tables(0).Rows
            Dim expression As String
            expression = "PMCO =" + CType(dr.Item(0), Integer).ToString
            Dim drs() As DataRow = ds2.Tables(0).Select(expression)
            If (drs.Length = 1) Then
                For i As Integer = 1 To ds2.Tables(0).Columns.Count - 1
                    drs(0).Item(i) = dr.Item(i)
                Next
            Else
                Dim drnew As DataRow = ds2.Tables(0).NewRow
                For i As Integer = 0 To ds2.Tables(0).Columns.Count - 1
                    drnew.Item(i) = dr.Item(i)
                Next
                ds2.Tables(0).Rows.Add(drnew)
            End If
        Next
        Form2.Show()
    End Sub

There is no error message but the excel's data is not imported into the Microsoft Access.

 

[cjard]

Is it getting into the datatable?

I did start reading your code, but it really irritates me when developers use meaningless, dumb variable names like ds1 and ds2; am I supposed to work out, every time I read it, whether it's the access dataset or the excel dataset? What's wrong with giving them a sensible name in the first place?

 

[reubenfoo]

oh, i solved it using data tables instead.. thanks for your help though YOU'VE BEEN GREAT!