Export access db tables to excel

Question

I am searching for a tutorial or code to help me understand how to export an access db with multiple tables to an excel spreadsheet with each table having its own sheet.

Jayraj Chhaya · Answer

To export multiple tables from an Access database to an Excel spreadsheet with each table on a separate sheet you can use C# and the ADO.NET library. using System; using System.Data; using System.Data.OleDb; using Excel = Microsoft.Office.Interop.Excel; namespace AccessToExcelExporter { class Program { static void Main(string[] args) { string connectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\your\database.accdb;; using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); DataTable tables = connection.GetSchema(Tables); Excel.Application excelApp = new Excel.Application(); Excel.Workbook workbook = excelApp.Workbooks.Add(); foreach (DataRow table in tables.Rows) { string tableName = table[TABLE_NAME].ToString(); OleDbDataAdapter adapter = new OleDbDataAdapter($SELECT * FROM [{tableName}] connection); DataTable data = new DataTable(); adapter.Fill(data); Excel.Worksheet worksheet = workbook.Sheets.Add(); worksheet.Name = tableName; for (int i = 0; i we establish a connection to the Access database retrieve the table names and then export each table to a separate sheet in an Excel workbook.

RICHARD KNOX · Answer

private void button1_Click(object sender EventArgs e) { using (AC.con) AC.OpenConnection(); string[] restrictionValues = new string[4]; restrictionValues[3] = Table; DataTable tables = AC.con.GetSchema(Tables restrictionValues); Excel.Application excelApp = new Excel.Application(); Excel.Workbook workbook = excelApp.Workbooks.Add(); foreach (DataRow table in tables.Rows) { string tableName = table[TABLE_NAME].ToString(); OleDbDataAdapter adapter = new OleDbDataAdapter($SELECT * FROM [{tableName}] AC.con); DataTable data = new DataTable(); adapter.Fill(data); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.Add(); worksheet.Name = tableName; DataTable dt = new DataTable(); adapter.Fill(dt); //Write Column names to Excel worksheet for (int i = 0; i I figured out what I needed to do. Here is the code I created please let me know if I missed anything or anything I could have done better.

RICHARD KNOX · Answer

I resolved my previous issue but I now have a new issue. Been reading thru a lot of posts but am unable to figure this one out.

RICHARD KNOX · Answer

I am trying to export the tables from an access db to excel programically thru C# not just utilizing the application.

Naimish Makwana · Answer

Sure I can guide you on how to export data from an Access database to Excel. Each table in the Access database can be exported to a separate sheet in an Excel workbook. Here’s a step-by-step guide: Open the Access database file. Click the External Data tab. Click the Excel button in the Export group1. A wizard will open. Enter a name for the file choose a folder to put the Export file and click Save 21. Choose other options in the wizard such as including formatting if you want to open the destination file and if you want to export filtered or selected records2. Click OK 2. If you have chosen to open the destination file when complete Excel will open automatically2. Repeat these steps for each table you want to export. Please note that this process exports each table to a separate Excel file. If you want each table in a separate sheet within the same Excel file you would need to manually move or copy the sheets into one workbook after the export process. Also keep in mind that calculated fields totals and controls created in Access will export as values not as calculations3. When you export a form report or datasheet that contains subforms subreports or subdatasheets only the main form report or datasheet is exported. You must repeat the export operation for each subform subreport and subdatasheet that you want to export to Excel3. Thanks

RICHARD KNOX · Answer

I tried the first answer and received this error.

Tahir Ansari · Answer

using System; using System.Data.OleDb; using Excel = Microsoft.Office.Interop.Excel; namespace AccessToExcel { class Program { static void Main(string[] args) { string accessDbPath = YourAccessDBPath.accdb; string excelFilePath = YourExcelFilePath.xlsx; ExportToExcel(accessDbPath excelFilePath); } static void ExportToExcel(string accessDbPath string excelFilePath) { // Connection string for Access database string connStr = $Provider=Microsoft.ACE.OLEDB.12.0;Data Source={accessDbPath};; // Create an instance of Excel application Excel.Application excelApp = new Excel.Application(); excelApp.Visible = false; // Create a new workbook Excel.Workbook workbook = excelApp.Workbooks.Add(); using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); // Retrieve table names from Access database DataTable tables = conn.GetSchema(Tables); foreach (DataRow row in tables.Rows) { string tableName = row[TABLE_NAME].ToString(); // Create a new worksheet for each table Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(); worksheet.Name = tableName; // Retrieve data from Access table string sql = $SELECT * FROM [{tableName}]; OleDbDataAdapter adapter = new OleDbDataAdapter(sql conn); DataTable dt = new DataTable(); adapter.Fill(dt); // Write data to Excel worksheet for (int i = 0; i