Run a query on multiple mdb files

Hi Experts,

I am a newbie to Access. I have about 300 mdb files all with the same name, same tables under different subfolders. for example:

H:\Southern\folder1\database.mdb
H:\Southern\folder2\database.mdb
H:\Southern\folder3\database.mdb
.
.
.
.
.
.
H:\Southern\folder300\database.mdb

I want to programatically acheive the following:

1) Search for each of the mdb files in the folders
2) Open the database, Run a query (extract a few columns by joining them)
3) export the result set to an excel sheet
4) perform this for all the 300 files.

Sample codes and pointers to acheive this will be greatly appreciated.'

thanks.

The following code executes the sample query on all the mdb files and exports the results in CSV file. CSV file can be opened in excel. If you want to create XLS file then you will have to call excel COM components. The other way can be to create excel file using OpenXML format. But that will be a long code.

I hope the code is helpful and runs correctly because i could not test it.

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Linq;

namespace Console1

{

class Program

{

static DataTable ReadAccessTable(string file, string query)

{

string provider = Type.GetTypeFromProgID("Microsoft.ACE.OLEDB.12.0") != null ?

"Microsoft.ACE.OLEDB.12.0" :

"microsoft.jet.oledb.4.0";

string connString = string.Format("provider={0};data source={1};", provider, file);

using (var connection = new SqlConnection(connString))

{

connection.Open();

SqlDataAdapter thisAdapter = new SqlDataAdapter(query, connection);

SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

DataSet thisDataSet = new DataSet();

thisAdapter.Fill(thisDataSet);

return thisDataSet.Tables[0];

}

static void Main(string[] args)

{

const string csvFile = "D:\\1.xsv";

const string query = "SELECT * FROM Table1";

var allLines = new List();

foreach (var file in Directory.GetFiles("D:\\SomeFolder","*.mdb",SearchOption.AllDirectories))

{

var table = ReadAccessTable(file, query);

var lines = table.AsEnumerable().Select(r => string.Join(",", r.ItemArray.Select(i=>i.ToString()).ToArray()));

allLines.AddRange(lines);

}

File.WriteAllLines(csvFile, allLines.ToArray());

}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Usman Zafar14y ago
The following code executes the sample query on all the mdb files and exports the results in CSV file. CSV file can be opened in excel. If you want to create XLS file then you will have to call excel COM components. The other way can be to create excel file using OpenXML format. But that will be a long code.

I hope the code is helpful and runs correctly because i could not test it.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;

namespace Console1
{
class Program
{
static DataTable ReadAccessTable(string file, string query)
{
string provider = Type.GetTypeFromProgID("Microsoft.ACE.OLEDB.12.0") != null ?
"Microsoft.ACE.OLEDB.12.0" :
"microsoft.jet.oledb.4.0";

string connString = string.Format("provider={0};data source={1};", provider, file);
using (var connection = new SqlConnection(connString))
{
connection.Open();

SqlDataAdapter thisAdapter = new SqlDataAdapter(query, connection);

SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

DataSet thisDataSet = new DataSet();

thisAdapter.Fill(thisDataSet);
return thisDataSet.Tables[0];
}
}

static void Main(string[] args)
{
const string csvFile = "D:\\1.xsv";
const string query = "SELECT * FROM Table1";

var allLines = new List();
foreach (var file in Directory.GetFiles("D:\\SomeFolder","*.mdb",SearchOption.AllDirectories))
{
var table = ReadAccessTable(file, query);
var lines = table.AsEnumerable().Select(r => string.Join(",", r.ItemArray.Select(i=>i.ToString()).ToArray()));

allLines.AddRange(lines);
}

File.WriteAllLines(csvFile, allLines.ToArray());
}
}
}