"Patrick B" <newsgr***@devzoo.com> wrote in message
news:%23X3fB48MFHA.204@TK2MSFTNGP15.phx.gbl...
> 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);
> }