none
Copy DataSet into MS Access table RRS feed

  • Question

  • I am connecting to a webservice that returns a fully populated DataSet.  I simply want to take this fully populated DataSet and dump it into an Access database table.  The DataSet and table are identical in structure.  If my destination was SQL Server (rather than Access), I think I could use SqlBulkCopy, however there does not appear to be an equivalent in the OleDB object.  This seems exceedingly simple and is driving me crazy!

     

    ---

     

    Dim svcEverDreamData As New EverDreamDownload.DataRetrieval

    Dim dsEverDreamData As New DataSet()

     

    'DataSet is populated

    dsEverDreamData = svcEverDreamData.getDataSet("AssetContact", strIWSLogin, strIWSPassword, strIWSUsageKey)

     

    'Testing dataset...data is properly displayed in the DataGrid so I know the DataSet is populated correctly

    myDataGrid.DataSource = dsEverDreamData.Tables(0)

    myDataGrid.Refresh()

     

    'Trying to dump the data by creating a "dummy" DataAdapter that points to the AccessDB (dbConn is a pre-configured 'OleDBConnection)

    Dim daAccess As New OleDbDataAdapter("SELECT * FROM AssetContact", dbConn)

     

    dbConn.Open()

     

    daAccess.Update(dsEverDreamData, "AssetContact")

     

    dbConn.Close()

     

     

    Wednesday, January 16, 2008 4:12 PM

All replies

  • There is no equivalent to the SQLBulkCopy class for Access. However you can "update" the contents of a DataSet

    into a destination table using another DataAdapter. The key here is that you must set the AcceptChangesDuringFill property to False for the source DataAdapter (from which your DataSet was generated) so that the RowState of each row is "Added". In addition, the schema for the DataSet and the destination table should be the same:

     

    Code Block

    Dim DataAdapterSource As New System.Data.OleDb.OleDbDataAdapter

    Dim DataAdapterDestination As New System.Data.OleDb.OleDbDataAdapter

     

    Dim OleDbConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test Files\db1 XP.mdb;Jet OLEDB:Engine Type=5;")

     

    Dim OleDbDataset As New DataSet

     

    OleDbConnection.Open()

     

    DataAdapterSource.AcceptChangesDuringFill = False

     

    DataAdapterSource.SelectCommand = New System.Data.OleDb.OleDbCommand("SELECT Col1, Col2, Col3, Col4 FROM SourceTable", OleDbConnection)

    DataAdapterSource.Fill(OleDbDataset, "SourceTable")

     

    DataAdapterDestination.InsertCommand = New System.Data.OleDb.OleDbCommand("INSERT INTO DestinationTable (Col1, Col2, Col3, Col4) VALUES (?,?,?,?)", OleDbConnection)

    DataAdapterDestination.InsertCommand.Parameters.Add("@Col1", OleDb.OleDbType.Integer, 0, "Col1")

    DataAdapterDestination.InsertCommand.Parameters.Add("@Col2", OleDb.OleDbType.Integer, 0, "Col2")

    DataAdapterDestination.InsertCommand.Parameters.Add("@Col3", OleDb.OleDbType.VarWChar, 50, "Col3")

    DataAdapterDestination.InsertCommand.Parameters.Add("@Col4", OleDb.OleDbType.Date, 0, "Col4")

     

    DataAdapterDestination.UpdateCommand = New System.Data.OleDb.OleDbCommand("UPDATE DestinationTable SET Col1 = ?, Col2 = ?, Set Col3 = ?, Col4 = ?", OleDbConnection)

    DataAdapterDestination.UpdateCommand.Parameters.Add("@Col1", OleDb.OleDbType.Integer, 0, "Col1")

    DataAdapterDestination.UpdateCommand.Parameters.Add("@Col2", OleDb.OleDbType.Integer, 0, "Col2")

    DataAdapterDestination.UpdateCommand.Parameters.Add("@Col3", OleDb.OleDbType.VarWChar, 50, "Col3")

    DataAdapterDestination.UpdateCommand.Parameters.Add("@Col4", OleDb.OleDbType.Date, 0, "Col4")

     

    DataAdapterDestination.Update(OleDbDataset, "SourceTable")

     

    OleDbConnection.Close()

     

     

    Thursday, January 17, 2008 10:13 PM
  • My issue is that the webservice only returns the final DataSet.  So, in essence, I do not ever use a DataAdapterSource and am therefore unable to set the AcceptChangesDuringFill property when the DataSet is populated.

     

    In the end I am just looping through the DataSet.Table(0) and inserting into my destination table row by row.  It's not very elegant, but it works.  I'm just surprised there is no way to pull a DataSet that is already populated into a MS Access table without touching the DataAdapter that was used to create the DataSet.

     

    Thanks for your response.

     

    Friday, January 18, 2008 1:24 AM
  • No, unfortunately there is no direct export mechanism. You can export to XML but that doesn't really get you any closer to importing it into a Microsoft Access database.

     

    Friday, January 18, 2008 1:05 PM
  • Is the data that you're exporting to an Access database coming from another database source?

     

    Wednesday, November 12, 2008 4:07 PM