Copy a table between databases

[Patrick B]

If you have a DataTable full of data, what's the easiest way to save the
DataTable as a new table in a Microsoft Access database?

This is a C# application. What the application needs to do is:

1) Run a select query on an Access database and fill a DataTable with
the result.
2) Create a new Access database.
3) Save the DataTable created in step 1 as a new table in the database
created in step 2.

So far I've been able to accomplish step 1 (using ADO.NET) and step 2
(using ADOX), but I'm stumped on step 3.

FYI, here's the code I use to accomplish steps 1 and 2:

// Step 1
public static DataTable SelectQuery(string databaseFilePath, string sql)
{
string connection = "User Id=Admin;"
+ "Password=;Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=\"" + databaseFilePath + "\"";
OleDbConnection oleDbConnection = new OleDbConnection(connection);
OleDbDataAdapter oleDbDataAdapter
= new OleDbDataAdapter(sql, oleDbConnection);
DataTable dataTable = new DataTable();
oleDbDataAdapter.Fill(dataTable);
return dataTable;
}

// Step 2
// requires reference to Microsoft ADO Ext. 2.7
public static void CreateAccessDatabase(string filePath)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=" + filePath + ";"
+ "Jet OLEDB:Engine Type=5");
}

// Step 3
public static void SaveDataTableIntoAccess
(string databaseFilePath, DataTable dataTable)
{
// how the heck do I code this?
}

// Example of putting it all together
[STAThread]
public static void Main(string[] args)
{
string filePath = "Orders.mdb";
string newFilePath = "NewOrders.mdb";
if (File.Exists(newFilePath )) {
File.Delete(newFilePath );
}
string sql = "select * from orders where OrderDate > #1/1/2005#";
// Step 1
DataTable dataTable = SelectQuery(filePath, sql);
// Step 2
CreateAccessDatabase(newFilePath);
// Step 3
SaveDataTableIntoAccess(newFilePath, dataTable);
}

 

[Val Mazur \(MVP\)]

Hi,

Check next KB with an example

http://support.microsoft.com/default.aspx?scid=kb;en-us;310347