Database / XML / Reporting Topics include: ADO.NET, SQL, XML structures and schemas, Crystal Reports, and other reporting tools

Go Back  Xtreme .NET Talk > .NET > Database / XML / Reporting > Export dataset to Ms Access .mdb file


Reply
 
Thread Tools Display Modes
  #1  
Old 08-10-2005, 08:58 PM
PROKA's Avatar
PROKA PROKA is offline
Junior Contributor

Preferred language:
VB.NET, C#
 
Join Date: Sep 2003
Location: Bucharest
Posts: 249
PROKA is on a distinguished road
Default Export dataset to Ms Access .mdb file



It's 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 don't help me
__________________
Development & Research Department @ Elven Soft
Reply With Quote
  #2  
Old 08-10-2005, 09:10 PM
Joe Mamma's Avatar
Joe Mamma Joe Mamma is offline
Senior Contributor

Preferred language:
C#,Delphi
 
Join Date: Mar 2004
Location: Washington DC
Posts: 1,062
Joe Mamma is on a distinguished road
Default

do you have ms access available for interop or only the jet engine?
__________________
Joe Mamma
Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.
Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Reply With Quote
  #3  
Old 08-10-2005, 09:59 PM
Joe Mamma's Avatar
Joe Mamma Joe Mamma is offline
Senior Contributor

Preferred language:
C#,Delphi
 
Join Date: Mar 2004
Location: Washington DC
Posts: 1,062
Joe Mamma is on a distinguished road
Default

how much of the schema do you need? just the tabes and columns??? or relations, defaults and constraints, too????
__________________
Joe Mamma
Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.
Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Reply With Quote
  #4  
Old 08-10-2005, 09:59 PM
PROKA's Avatar
PROKA PROKA is offline
Junior Contributor

Preferred language:
VB.NET, C#
 
Join Date: Sep 2003
Location: Bucharest
Posts: 249
PROKA is on a distinguished road
Default

actually, there's only one table in the dataset. so pretty much, that one table and the columns
__________________
Development & Research Department @ Elven Soft
Reply With Quote
  #5  
Old 08-10-2005, 11:26 PM
Joe Mamma's Avatar
Joe Mamma Joe Mamma is offline
Senior Contributor

Preferred language:
C#,Delphi
 
Join Date: Mar 2004
Location: Washington DC
Posts: 1,062
Joe Mamma is on a distinguished road
Default

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!
Code:
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;
   }
 }
}
__________________
Joe Mamma
Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.
Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Reply With Quote
  #6  
Old 08-11-2005, 08:34 AM
PROKA's Avatar
PROKA PROKA is offline
Junior Contributor

Preferred language:
VB.NET, C#
 
Join Date: Sep 2003
Location: Bucharest
Posts: 249
PROKA is on a distinguished road
Default

I am using vb.net and I am having really big problems translating your code ((((
__________________
Development & Research Department @ Elven Soft
Reply With Quote
  #7  
Old 08-11-2005, 08:51 AM
PROKA's Avatar
PROKA PROKA is offline
Junior Contributor

Preferred language:
VB.NET, C#
 
Join Date: Sep 2003
Location: Bucharest
Posts: 249
PROKA is on a distinguished road
Default

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

hope you don't get upset I am using VB.NET ( which 'sux' ) and not C#, and you will still help me I'm like a pathetic twin brother but still, I am ur brother
__________________
Development & Research Department @ Elven Soft

Last edited by PROKA; 08-11-2005 at 09:30 AM.
Reply With Quote
  #8  
Old 08-11-2005, 09:46 AM
Joe Mamma's Avatar
Joe Mamma Joe Mamma is offline
Senior Contributor

Preferred language:
C#,Delphi
 
Join Date: Mar 2004
Location: Washington DC
Posts: 1,062
Joe Mamma is on a distinguished road
Default

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 doesn't work as expected.
__________________
Joe Mamma
Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.
Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Reply With Quote
  #9  
Old 08-11-2005, 09:49 AM
PROKA's Avatar
PROKA PROKA is offline
Junior Contributor

Preferred language:
VB.NET, C#
 
Join Date: Sep 2003
Location: Bucharest
Posts: 249
PROKA is on a distinguished road
Default

OK ! Thank you very very very much. I have a little wound on my neck, but with your help I hope I will survive !
__________________
Development & Research Department @ Elven Soft
Reply With Quote
  #10  
Old 08-11-2005, 11:14 AM
Joe Mamma's Avatar
Joe Mamma Joe Mamma is offline
Senior Contributor

Preferred language:
C#,Delphi
 
Join Date: Mar 2004
Location: Washington DC
Posts: 1,062
Joe Mamma is on a distinguished road
Default

debugged. . . note the changes -
Strings will be defined as memos, hope thats ok?!?
attached cs lib project you can reference.
usage:
Code:
ADOXJetXML.DatasetToJet.CopyDatasetSchemaToJetDB(productDS1, "foobar.mdb");


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

Code:
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;
   }
 }
}
Attached Files
File Type: zip ADOXJetXML.zip (3.3 KB, 151 views)
__________________
Joe Mamma
Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.
Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.

Last edited by Joe Mamma; 08-11-2005 at 11:21 AM.
Reply With Quote
  #11  
Old 08-11-2005, 11:22 AM
Joe Mamma's Avatar
Joe Mamma Joe Mamma is offline
Senior Contributor

Preferred language:
C#,Delphi
 
Join Date: Mar 2004
Location: Washington DC
Posts: 1,062
Joe Mamma is on a distinguished road
Default

be sure to get the one I just edited as I removed console I/O I inadvertantly left in for debugging.
__________________
Joe Mamma
Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.
Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Reply With Quote
  #12  
Old 08-11-2005, 01:03 PM
PROKA's Avatar
PROKA PROKA is offline
Junior Contributor

Preferred language:
VB.NET, C#
 
Join Date: Sep 2003
Location: Bucharest
Posts: 249
PROKA is on a distinguished road
Default

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 don't know if the data type is text to use 'value' or integer to use the value without the '
enfin ...
thank u !
__________________
Development & Research Department @ Elven Soft
Reply With Quote
  #13  
Old 08-11-2005, 03:56 PM
Joe Mamma's Avatar
Joe Mamma Joe Mamma is offline
Senior Contributor

Preferred language:
C#,Delphi
 
Join Date: Mar 2004
Location: Washington DC
Posts: 1,062
Joe Mamma is on a distinguished road
Default

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.
Code:
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);
   }
  }
 }
}
Attached Files
File Type: zip ADOXJetXML.zip (3.6 KB, 155 views)
__________________
Joe Mamma
Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.
Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Reply With Quote
  #14  
Old 02-17-2009, 05:56 AM
JAYUL JAYUL is offline
Newcomer
 
Join Date: Feb 2009
Posts: 1
JAYUL is on a distinguished road
Default Re: Export dataset to Ms Access .mdb file

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"
Reply With Quote
  #15  
Old 04-20-2009, 08:38 AM
pmuruaga pmuruaga is offline
Newcomer
 
Join Date: Apr 2009
Posts: 2
pmuruaga is on a distinguished road
Default Re: Export dataset to Ms Access .mdb file

Hi there: I am doing this and I get the following message:
'System.Runtime.InteropServices.COMException -> DataTypes doesn't 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.
Reply With Quote
  #16  
Old 04-20-2009, 08:45 AM
pmuruaga pmuruaga is offline
Newcomer
 
Join Date: Apr 2009
Posts: 2
pmuruaga is on a distinguished road
Default Re: Export dataset to Ms Access .mdb file

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.c s: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.c s: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\Defaul t.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.R aisePostBackEvent(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)
Reply With Quote
  #17  
Old 04-28-2009, 10:23 AM
xonimiro xonimiro is offline
Newcomer
 
Join Date: Apr 2009
Posts: 1
xonimiro is on a distinguished road
Default Re: Export dataset to Ms Access .mdb file

Quote:
Originally Posted by pmuruaga View Post
Hi there: I am doing this and I get the following message:
'System.Runtime.InteropServices.COMException -> DataTypes doesn't 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)
Reply With Quote
  #18  
Old 11-20-2009, 10:50 AM
LeeDeb LeeDeb is offline
Newcomer
 
Join Date: Nov 2009
Posts: 1
LeeDeb is on a distinguished road
Default Re: Export dataset to Ms Access .mdb file

Hello, it is necessary to save all structure of DB in new (tables, keys, relations, defaults and constraints). The resulted code is excellent, but a variant is needed with relations. Help with my problem.
Reply With Quote
  #19  
Old 12-25-2009, 11:10 AM
penoo penoo is offline
Newcomer
 
Join Date: Dec 2009
Posts: 1
penoo is on a distinguished road
Default Re: Export dataset to Ms Access .mdb file

Hi, Toni and Joe Mamma,

Really appreciate your brain sharing. I got to solve the issue that I had to finish by this code provided and corrected by yours.

Toni, I have corrected as below in your code as it returned an error on columnas array. Actually these codes are quite difficult to understand from my brain. Just had the same idea but could solve the problem by your hint.

//array[c] = row[(string)columnas[c]];
array[c] = row[(string)adoxTab.Columns[c].Name];

Hope this also help others looking for the same fucntion.
woojin in shanghai.
Reply With Quote
  #20  
Old 01-19-2011, 08:47 AM
avdeshkataria avdeshkataria is offline
Newcomer
 
Join Date: Jan 2011
Posts: 1
avdeshkataria is on a distinguished road
Default Re: Export dataset to Ms Access .mdb file

Hello Sir,
I used your code for creating mdb database by using dataset. But Its create only Schema of the tables. But I want both table Schema & Data...

Example:
Dataset has 1 Datatable[Temp] with 4 coulmns[A,B,C,D].and Datatable[Temp] having two Rows [(1,2,3,4),(5,6,7,8)].
Now I want mdb database having 1 table with all Data[Both Rows].

I'm waiting for your reply,

Thanks And Regards,
Avdesh
avdeshkataria@gmail.com
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Export a dataset to an Excel file karmah Interoperation / Office Integration 3 03-12-2010 08:30 AM
Access MDB backend compact errors cpopham Database / XML / Reporting 0 06-02-2006 06:01 PM
Deploy a program with mdb db without access installed? patdenim Deployment 4 04-14-2005 06:00 AM
DAtaset Export to excel wbb ASP.NET 2 03-24-2004 09:42 AM
Accessing Access .mdb torg Database / XML / Reporting 2 01-14-2003 04:38 PM

Advertisement:







Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe