Export dataset to Ms Access .mdb file

[PROKA]

Its pretty urgent, I have to hand in a project in 1 hour.

I have this dataset with data in it, and I want to export it to an .mdf file

Please help ! ( my neck is going to be cut off if you dont help me

 

[Joe Mamma]

do you have ms access available for interop or only the jet engine?

 

[Joe Mamma]

how much of the schema do you need? just the tabes and columns??? or relations, defaults and constraints, too????

 

[PROKA]

actually, theres only one table in the dataset. so pretty much, that one table and the columns

 

[Joe Mamma]

just hacked this together . . . no test -
but this copies the schema to a Jet Database (creating it if it doent exist)
once the schema is copied, moving the data is trivial, isnt it???

usage:
ADOXJetXML.DatasetToJet.CopyDatasetSchemaToJetDB(someDataset, someFilePath);

NOTE: requires interop of the ADO and ADO extensions Libraries!

using ADOX;
using ADODB;
using System;
using System.Data;
using System.Data.OleDb;
namespace ADOXJetXML
{
 /// <summary>
 /// Summary description for DatasetToJet.
 /// </summary>
 public class DatasetToJet
 {
  static public void CopyDatasetSchemaToJetDB(DataSet ds, string jetFileName)
  {
   string connstr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;" +
    @"Data Source={0};Jet OLEDB:Engine Type=5;", jetFileName);
   Catalog cat = new CatalogClass();
   if (!System.IO.File.Exists(jetFileName))
    cat.Create(connstr);
   else
   {
    cat.ActiveConnection = new ADODB.ConnectionClass(); 
    (cat.ActiveConnection as ADODB.Connection).Open(connstr, "","", -1);
   }
   foreach(DataTable table in ds.Tables)
    cat.Tables.Append(CopyDataTable(table));
  }

  static private ADOX.Table CopyDataTable(DataTable table)
  {
   ADOX.Table adoxTable = new ADOX.TableClass();
   adoxTable.Name = table.TableName;
   foreach(System.Data.DataColumn col in table.Columns)
    adoxTable.Columns.Append(
     new ADOX.ColumnClass(), 
     TranslateDataTypeToADOXDataType(col.DataType),
     col.MaxLength);
   return adoxTable;
  }

  static private ADOX.DataTypeEnum TranslateDataTypeToADOXDataType(Type type)
  {
   string guid = type.GUID.ToString();
   return 
    guid == typeof(bool).GUID.ToString() ? ADOX.DataTypeEnum.adBoolean : 
    guid == typeof(byte).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedTinyInt: 
    guid == typeof(char).GUID.ToString() ? ADOX.DataTypeEnum.adChar : 
    guid == typeof(DateTime).GUID.ToString() ?  ADOX.DataTypeEnum.adDate :  
    guid == typeof(decimal).GUID.ToString() ?  ADOX.DataTypeEnum.adDecimal : 
    guid == typeof(double).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
    guid == typeof(Int16).GUID.ToString() ?  ADOX.DataTypeEnum.adSmallInt :  
    guid == typeof(Int32).GUID.ToString() ?  ADOX.DataTypeEnum.adInteger : 
    guid == typeof(Int64).GUID.ToString() ?  ADOX.DataTypeEnum.adBigInt : 
    guid == typeof(SByte).GUID.ToString() ?  ADOX.DataTypeEnum.adTinyInt :  
    guid == typeof(Single).GUID.ToString() ?  ADOX.DataTypeEnum.adSingle :
    guid == typeof(string).GUID.ToString() ?  ADOX.DataTypeEnum.adVarChar : 
    guid == typeof(TimeSpan).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
    guid == typeof(UInt16).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedSmallInt : 
    guid == typeof(UInt32).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedInt : 
    guid == typeof(UInt64).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedBigInt :  
    ADOX.DataTypeEnum.adBinary;
   }
 }
}

 

[PROKA]

I am using vb.net and I am having really big problems translating your code ((((

 

[PROKA]

ok, It creates the table, but not its structure though ( I mean those columns etc )

hope you dont get upset I am using VB.NET ( which sux ) and not C#, and you will still help me Im like a pathetic twin brother but still, I am ur brother

 

[Joe Mamma]

you can compile a c# project, yes???

let me make a library project that you can reference. . . give me a second to debug it to see why it doesnt work as expected.

 

[PROKA]

OK ! Thank you very very very much. I have a little wound on my neck, but with your help I hope I will survive !

 

[Joe Mamma]

debugged. . . note the changes -
Strings will be defined as memos, hope thats ok?!?
attached cs lib project you can reference.
usage:

ADOXJetXML.DatasetToJet.CopyDatasetSchemaToJetDB(productDS1, "foobar.mdb");

NB - Deletes the table if it already exists!!!!

using System;
using ADOX;
using ADODB;
using System.Data;
using System.Data.OleDb;
namespace ADOXJetXML
{
 /// <summary>
 /// Summary description for DatasetToJet.
 /// </summary>
 public class DatasetToJet
 {
  static public void CopyDatasetSchemaToJetDB(DataSet ds, string jetFileName)
  {
   string connstr = string.Format(@"Data Source=""{0}"";Jet OLEDB:Engine Type=5;"+
     @"Provider=""Microsoft.Jet.OLEDB.4.0"";User ID=Admin;",  jetFileName);
   Catalog cat = new CatalogClass();
   if (!System.IO.File.Exists(jetFileName))
    cat.Create(connstr);
   else
   {
    ADODB.Connection conn = new ConnectionClass();
    conn.Open(connstr, "Admin", "",-1);
    cat.ActiveConnection = conn; 
   }
   
   foreach(DataTable table in ds.Tables)
   {
    try
    {
     cat.Tables.Delete(table.TableName);
    }
    catch{}
    ADOX.Table adoxTab = CopyDataTable(table, cat);
    cat.Tables.Append(adoxTab);
   }
  }
  static private ADOX.Table CopyDataTable(DataTable table, Catalog cat)
  {
   ADOX.Table adoxTable = new ADOX.TableClass();
   adoxTable.Name = table.TableName;
   adoxTable.ParentCatalog = cat;
   foreach(System.Data.DataColumn col in table.Columns)
   {
    ADOX.Column adoxCol = new ADOX.ColumnClass();
    adoxCol.ParentCatalog = cat;
    adoxCol.Name = col.ColumnName;
    adoxCol.Type = TranslateDataTypeToADOXDataType(col.DataType);
    if (col.MaxLength >=0 )
     adoxCol.DefinedSize = col.MaxLength;
    adoxTable.Columns.Append(adoxCol, adoxCol.Type, adoxCol.DefinedSize);
   }
   return adoxTable;
  }
 
  static private ADOX.DataTypeEnum TranslateDataTypeToADOXDataType(Type type)
  {
   string guid = type.GUID.ToString();
   ADOX.DataTypeEnum adoxType = 
    guid == typeof(bool).GUID.ToString() ? ADOX.DataTypeEnum.adBoolean : 
    guid == typeof(byte).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedTinyInt: 
    guid == typeof(char).GUID.ToString() ? ADOX.DataTypeEnum.adChar : 
    guid == typeof(DateTime).GUID.ToString() ?  ADOX.DataTypeEnum.adDate :  
    guid == typeof(decimal).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
    guid == typeof(double).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
    guid == typeof(Int16).GUID.ToString() ?  ADOX.DataTypeEnum.adSmallInt :  
    guid == typeof(Int32).GUID.ToString() ?  ADOX.DataTypeEnum.adInteger : 
    guid == typeof(Int64).GUID.ToString() ?  ADOX.DataTypeEnum.adBigInt : 
    guid == typeof(SByte).GUID.ToString() ?  ADOX.DataTypeEnum.adTinyInt :  
    guid == typeof(Single).GUID.ToString() ?  ADOX.DataTypeEnum.adSingle :
    guid == typeof(string).GUID.ToString() ?  ADOX.DataTypeEnum.adLongVarWChar : 
    guid == typeof(TimeSpan).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
    guid == typeof(UInt16).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedSmallInt : 
    guid == typeof(UInt32).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedInt : 
    guid == typeof(UInt64).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedBigInt :  
    ADOX.DataTypeEnum.adBinary;
   return adoxType;
   }
 }
}

 

[Joe Mamma]

be sure to get the one I just edited as I removed console I/O I inadvertantly left in for debugging.

 

[PROKA]

Thanks a lot, it worked creating the mdb file correctly, Now I have some trouble transferring the data. I am using "Insert into ... " statements, but I dont know if the data type is text to use value or integer to use the value without the
enfin ...
thank u !

 

[Joe Mamma]

God, I love this stuff!!!!
You may run into some problems with datatype conversions, let me know exactly the problem and it should be very easy to fix.

replace the contents of DataSetToJet.cs with this code.
Addied method MoveData whic is called in the loop through the datatables immediately after attaching the generated ADOX.Table to the catalog.

review the code in MoveData as it calls a method that factories an ADODB.Command

note: since the ADODB libraries were already referenced, I am using adodb.command object as the cmd.Execute allows for easy parameterization.

using System;
using ADOX;
using ADODB;
using System.Data;
using System.Data.OleDb;
namespace ADOXJetXML
{
 public class DatasetToJet
 {
  static public void CopyDatasetSchemaToJetDB(DataSet ds, string jetFileName)
  {
   string connstr = string.Format(@"Data Source=""{0}"";Jet OLEDB:Engine Type=5;"+
     @"Provider=""Microsoft.Jet.OLEDB.4.0"";User ID=Admin;",  jetFileName);
   Catalog cat = new CatalogClass();
   if (!System.IO.File.Exists(jetFileName))
    cat.Create(connstr);
   else
   {
    ADODB.Connection conn = new ConnectionClass();
    conn.Open(connstr, "Admin", "",-1);
    cat.ActiveConnection = conn; 
   }
   
   foreach(DataTable table in ds.Tables)
   {
    try
    {
     cat.Tables.Delete(table.TableName);
    }
    catch{}
    ADOX.Table adoxTab = CopyDataTable(table, cat);
    cat.Tables.Append(adoxTab);
// 
// NEW METHOD CALL
//
    MoveData(adoxTab, table);
   }
  }
 
  static private ADOX.Table CopyDataTable(DataTable table, Catalog cat)
  {
   ADOX.Table adoxTable = new ADOX.TableClass();
   adoxTable.Name = table.TableName;
   adoxTable.ParentCatalog = cat;
   foreach(System.Data.DataColumn col in table.Columns)
   {
    ADOX.Column adoxCol = new ADOX.ColumnClass();
    adoxCol.ParentCatalog = cat;
    adoxCol.Name = col.ColumnName;
    adoxCol.Type = TranslateDataTypeToADOXDataType(col.DataType);
    adoxCol.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
    if (col.MaxLength >=0 )
     adoxCol.DefinedSize = col.MaxLength;
    adoxTable.Columns.Append(adoxCol, adoxCol.Type, adoxCol.DefinedSize);
   }
   return adoxTable;
  }
 
  static private ADOX.DataTypeEnum TranslateDataTypeToADOXDataType(Type type)
  {
   string guid = type.GUID.ToString();
   ADOX.DataTypeEnum adoxType = 
    guid == typeof(bool).GUID.ToString() ? ADOX.DataTypeEnum.adBoolean : 
    guid == typeof(byte).GUID.ToString() ? ADOX.DataTypeEnum.adUnsignedTinyInt: 
    guid == typeof(char).GUID.ToString() ? ADOX.DataTypeEnum.adChar : 
    guid == typeof(DateTime).GUID.ToString() ?  ADOX.DataTypeEnum.adDate :  
    guid == typeof(decimal).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
    guid == typeof(double).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
    guid == typeof(Int16).GUID.ToString() ?  ADOX.DataTypeEnum.adSmallInt :  
    guid == typeof(Int32).GUID.ToString() ?  ADOX.DataTypeEnum.adInteger : 
    guid == typeof(Int64).GUID.ToString() ?  ADOX.DataTypeEnum.adBigInt : 
    guid == typeof(SByte).GUID.ToString() ?  ADOX.DataTypeEnum.adTinyInt :  
    guid == typeof(Single).GUID.ToString() ?  ADOX.DataTypeEnum.adSingle :
    guid == typeof(string).GUID.ToString() ?  ADOX.DataTypeEnum.adLongVarWChar : 
    guid == typeof(TimeSpan).GUID.ToString() ?  ADOX.DataTypeEnum.adDouble : 
    guid == typeof(UInt16).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedSmallInt : 
    guid == typeof(UInt32).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedInt : 
    guid == typeof(UInt64).GUID.ToString() ?  ADOX.DataTypeEnum.adUnsignedBigInt :  
    ADOX.DataTypeEnum.adVarBinary;
   return adoxType;
  }
 
// 
// NEW METHOD
//
  private static ADODB.Command ADOXTableInsertCommand(ADOX.Table adoxTab)
  {
   ADODB.Command result  = new ADODB.CommandClass();
   object conn =  adoxTab.ParentCatalog.ActiveConnection;
   result.ActiveConnection = (ConnectionClass) conn; 
   result.CommandText = string.Format("INSERT INTO {0} ({1}) values({2}) " , adoxTab.Name, "{0}", "{1}");
   string colNames = string.Empty;
   string colVals = string.Empty;
   for (int i = 0; i < adoxTab.Columns.Count; i++)
   {
    ADOX.Column adoxCol = adoxTab.Columns[i];
    string name = adoxCol.Name;
    ADOX.DataTypeEnum type = adoxCol.Type;
    switch ( type )
    { 
     case ADOX.DataTypeEnum.adVarBinary: break;
     default:
      colNames += ( colNames != string.Empty ? "," : "") + name;
      colVals += (colVals != string.Empty ? "," : "") + "?" ;
      break;
    }
   }
   result.CommandText = string.Format(result.CommandText, colNames, colVals);
   return result;
  }
 
// 
// NEW METHOD
//
  private static void MoveData(ADOX.Table adoxTab, DataTable aTable)
  {
   object i;
   ADODB.Command cmd = ADOXTableInsertCommand(adoxTab);
   foreach(DataRow row in aTable.Rows)
   {
    object arry = row.ItemArray;
    cmd.Execute(out i, ref arry, 1);
   }
  }
 }
}

 

[JAYUL]

I am getting follwing error

"System.Runtime.InteropServices.COMException (0x80040E14): Syntax error in INSERT INTO statement.
at ADODB.CommandClass.Execute(Object& RecordsAffected, Object& Parameters, Int32 Options)
at ADOXJetXML.DatasetToJet.MoveData(Table adoxTab, DataTable aTable)
at ADOXJetXML.DatasetToJet.CopyDatasetSchemaToJetDB(DataSet ds, String jetFileName)
at LMS.bookbuilty.DirectPrint_Ext(String prtName, Boolean DosPrint) in D:\LMS 22 Jan\LMS\bookbuilty.vb:line 3675"

 

[pmuruaga]

Hi there: I am doing this and I get the following message:
System.Runtime.InteropServices.COMException -> DataTypes doesnt match in criteria expresion or something like that, I have my visual studio in spanish.
What could be the problem. It is creating a file and if I open it it have the table and the structure but no records.
Thanks a lot.

 

[pmuruaga]

This is the stack trace:
at ADODB.CommandClass.Execute(Object& RecordsAffected, Object& Parameters, Int32 Options)
at PruebaDataSetToMDBAccess.DatasetToJet.MoveData(Table adoxTab, DataTable aTable) in C:\Documents and Settings\Pablo Muruaga\Mis documentos\Visual Studio 2008\Projects\PruebaDataSetToMDBAccess\PruebaDataSetToMDBAccess\Util.cs:line 122
at PruebaDataSetToMDBAccess.DatasetToJet.CopyDatasetSchemaToJetDB(DataSet ds, String jetFileName) in C:\Documents and Settings\Pablo Muruaga\Mis documentos\Visual Studio 2008\Projects\PruebaDataSetToMDBAccess\PruebaDataSetToMDBAccess\Util.cs:line 36
at PruebaDataSetToMDBAccess._Default.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\Pablo Muruaga\Mis documentos\Visual Studio 2008\Projects\PruebaDataSetToMDBAccess\PruebaDataSetToMDBAccess\Default.aspx.cs:line 89
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

 

[xonimiro]

Hi there: I am doing this and I get the following message:
System.Runtime.InteropServices.COMException -> DataTypes doesnt match in criteria expresion or something like that, I have my visual studio in spanish.
What could be the problem. It is creating a file and if I open it it have the table and the structure but no records.
Thanks a lot.

I got the same problem. It was that the order of the columns in the command creation and moving the data was not the same so types mismatched. I changed:

private static void MoveData(ADOX.Table adoxTab, DataTable aTable)
{
object i;
ADODB.Command cmd = ADOXTableInsertCommand(adoxTab);
foreach (DataRow row in aTable.Rows)
{
object[] array = new object[aTable.Columns.Count];
for(int c = 0; c < aTable.Columns.Count; c++)
array[c] = row[(string)columnas[c]];

object arry = array;
cmd.Execute(out i, ref arry, 1);
}
}

columnas is an ArrayList in the same order as when creating the command.

Another problem I found is that the first time you create the MDB the move data function fails (something like cannot convert COM object to ADODB.Connection). It is because the connection opened is not ADODB.Connection to solve that I changed:

if (!System.IO.File.Exists(jetFileName))
{
cat.Create(connstr);
}
else
{
ADODB.Connection conn = new ConnectionClass();
conn.Open(connstr, "Admin", "", -1);
cat.ActiveConnection = conn;
}

to

if (!System.IO.File.Exists(jetFileName))
cat.Create(connstr);
ADODB.Connection conn = new ConnectionClass();
conn.Open(connstr, "Admin", "", -1);
cat.ActiveConnection = conn;


Hope that helps.

Toni, Alicante (Spain)