Convert Access Table to Excel

[nnuswantari]

Hi, I am a poor programmer, I need your help
I want convert data (table) from .mdb format to .xls format. Please tell me, sintax for solving my problems. Thanx

 

[ZmrAbdulla]

From Access it is easy if you are using "TransferSpreadSheet" or "OutputTo" within Access VBA.

If you are trying to do it from VB.NET then load the table to datatable then write to excel sheet. That also tricky if the records are more than 65000.



Zameer Abdulla

 

[nnuswantari]

Okey friend... I know that I can use Access VBA for converting. But my problem that I want to convert more than 497 database access to excel. Each file contain 10 tables. So I will convert about 4970 tables. Can you tell me to make it easy???

 

[ZmrAbdulla]

here is a sample code not fully tested to get all the tables of all the mdbs in a folder to datatable one by one.

There are many sample code available to export to excel. You will have to do the error check and all

Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class Form1

    Private Sub btnConvertAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConvertAll.Click
        Dim fPath As String = Application.StartupPath
        'Get All MDBs
        Dim files As String()
        files = IO.Directory.GetFiles(fPath, "*.mdb")
        For Each sFile In files
            'Get TableNames from each db
            Dim dtSchema As New DataTable
            Dim conn As New OleDbConnection(GetConnectionString(sFile))
            conn.Open()
            dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                        New Object() {Nothing, Nothing, Nothing, "TABLE"})
            conn.Close()
            For i = 0 To dtSchema.Rows.Count - 1
                Dim tbl As String = dtSchema.Rows(i).Item(2)
                Dim dtTable As New DataTable
                dtTable = GetDataFromTable(sFile, tbl)
                If dtTable.Rows.Count > 0 Then
                    ' Here you will export the datatable to excel file
                    
                End If

                ' Here you will export the datatable to excel file

            Next i
        Next
    End Sub

    Function GetConnectionString(ByVal dbName As String) As String

        Dim constrBuilder As New OleDb.OleDbConnectionStringBuilder
        constrBuilder.Provider = "Microsoft.Jet.OLEDB.4.0"
        constrBuilder.DataSource = dbName
        Return constrBuilder.ConnectionString.ToString()

    End Function
    Public Function GetDataFromTable(ByVal dbName As String, ByVal tblName As String) As DataTable

        Dim conn As New OleDbConnection(GetConnectionString(dbName))
        Dim strSQLList As String = "SELECT * FROM [" & tblName & "]"
        Dim OledbAdapter As New OleDbDataAdapter(strSQLList, conn)
        Dim dt_TableName As New DataTable
        Try
            conn.Open()
            OledbAdapter.SelectCommand.CommandType = CommandType.Text
            OledbAdapter.Fill(dt_TableName)
        Catch exInvalid As System.InvalidOperationException
            MessageBox.Show(exInvalid.Message)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
            conn.Dispose()
        End Try

        Return dt_TableName
    End Function
End Class

Zameer Abdulla