none
Is there an easy way to convert a DataTable to an ADOX.Table. RRS feed

  • Question

  • I have a genealogy program that uses SQL Compact to store data.  It works great.  Now I have to take some of the tables and give to someone else (a non-programmer) in the form of an access database.

    I can get the data I want using a select command which returns a row array or I can get it in the form of DataTable if I use views.   

    After reading the forums I have learned how to create a new access database and add tables to it.  But all the examples I have read do it manually.  Adding column by column which seems like a lot of work.   
    I already have a table with columns is there a way to just copy it to my access database.

    Thank you very much
    Friday, February 6, 2009 11:27 PM

Answers

  • With the Access database, you would need to insert rows one-by-one. There is no bulk updates support in .NET (unless you find some third-party component that does it). For SQL Server you can use SqlBulkCopy .NET class.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Irene09 Tuesday, February 10, 2009 9:20 PM
    Tuesday, February 10, 2009 11:03 AM
    Moderator
  • Unfortunately there isn't much in the way of import/export methods for SQL Server Compact Edition. Since there is no ODBC driver, which would allow to use a SQL statement, you pretty much have to export rows one by one as Val mentioned. There is also a third-party utility called Data Port Wizard from Primeworks that can do this for you.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Irene09 Tuesday, February 10, 2009 9:20 PM
    Tuesday, February 10, 2009 1:28 PM

All replies

  • There is no straight way to do this. You could use ADOX or Access automation to create database, but there is no built-in .NET functionality. Here is sample how to use ADOX to achieve this task

    http://support.microsoft.com/kb/317881


    Val Mazur (MVP) http://www.xporttools.net
    Monday, February 9, 2009 11:01 AM
    Moderator
  • I have already done that part.  So if the table can not be copied, what is the most efficient way to copy the data.  Thank you.
    Tuesday, February 10, 2009 3:10 AM
  • With the Access database, you would need to insert rows one-by-one. There is no bulk updates support in .NET (unless you find some third-party component that does it). For SQL Server you can use SqlBulkCopy .NET class.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Irene09 Tuesday, February 10, 2009 9:20 PM
    Tuesday, February 10, 2009 11:03 AM
    Moderator
  • Unfortunately there isn't much in the way of import/export methods for SQL Server Compact Edition. Since there is no ODBC driver, which would allow to use a SQL statement, you pretty much have to export rows one by one as Val mentioned. There is also a third-party utility called Data Port Wizard from Primeworks that can do this for you.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Irene09 Tuesday, February 10, 2009 9:20 PM
    Tuesday, February 10, 2009 1:28 PM
  • Thank you.
    Tuesday, February 10, 2009 9:22 PM