dump dataset into Access Database

Posted on 2006-05-05
Last Modified: 2008-09-12
I want to insert data from a dataset into an empty Access database/table
I do as follows but the data never makes it to Access,
even though debugging shows that the dsAccess dataset has had the the data put in it from the TableInDotNet dataset
I don't understand why.  thanks.

        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\someAccess.mdb"
        Dim accessConn As New OleDbConnection(strConn)
        Dim accessAdapter As New OleDbDataAdapter("SELECT * FROM TableAccess", accessConn)
        Dim builder As New OleDbCommandBuilder(accessAdapter)

        accessAdapter.Fill(dsAccess, "TableAccess")

'insert rows from .net dataset into access dataset
        Dim dr As DataRow
        For Each dr In ds.Tables("TableInDotNet").Rows

        accessAdapter.Update(dsAccess, "TableAccess")
Question by:vbstroehle
    LVL 96

    Accepted Solution

    This line changes all the row states from Added to Unmodified so nothing happens:



    Author Comment


    Thanks Bob, Your explanation shows that AcceptChages was masking the real problem.
    The real poblem being that  the folllowing  code from knowledgebase article 317867. It creates an access database but won't let me update it becuase the lock on the file (*.ldb) is not released until the process completes. How can i create the database and then populate it within the same application.

    Imports ADOX

    Module Module1

        Sub Main()

            Dim cat As Catalog = New Catalog()

            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=D:\AccessDB\NewMDB.mdb;" & _
                        "Jet OLEDB:Engine Type=5")

            Console.WriteLine("Database Created Successfully")

            cat = Nothing

        End Sub

    End Module

    I have tried running the above in its own procedure as a seperate thread ie

    Private Delegate Function BuildAccessDatabaseDelegate() As Boolean

            Dim AsyncInvoker As New BuildAccessDatabaseDelegate(AddressOf BuildAccessDatabase)
            Dim AsyncState As IAsyncResult
            AsyncState = AsyncInvoker.BeginInvoke(Nothing, Nothing)
            ResultA = AsyncInvoker.EndInvoke(AsyncState)

    but the lock still remains on the database unit the process finishes??????
    Any ideas

    Author Comment

    Found a clue but still get and error
    if i close the connection above using
    CType(Cat.ActiveConnection, ADODB.Connection).Close()
    the ldb file can be deleted.

    but the command below still gives me an error:

    accessAdapter.Update(dsAccess, "TableAccess")

    error is:
    "A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in"

    The error occurs but the access database is populated correctly.

    I may need a primary key in the table??
    LVL 96

    Expert Comment

    by:Bob Learned
    1) I love that generic error
    2) Do you have a Try...Catch block.  Usually without it you won't get an explicit message
    3) Example

          ' Do something here
       Catch ex As Exception
       End Try

    LVL 41

    Expert Comment

    Generally speaking, "first chance exceptions" aren't errors...  They are just an indicatation (during a debug session) that the an exception was handled "behind the scenes".   This is sometimes an expected occurance and can be safely ignored.

    On the other hand, if the exception "bubbles up" to your application, then it's a *real* exception that you'll have to deal with.

    The example you posted won't create a missing table in Microsoft Access.   From reading your note, I assume you want to "dump" data from an existing DataSet into an empty table (but the table has to already exist?)

    Author Comment

    Let me show you more of the code maybe there is something i have missed.
    What i am doing is reading from a text file using the text file driver , placing this info in to a dataset, creating a dataset of an empty table in access then copying the data from the dataset with the fileinfo into the access dataset, thus updating access. In CAPS below is where the error occurs even though the data does make it into the access database.

    First i create a schema file
        Private Sub writeSchema()
                Dim fsOutput As FileStream = New FileStream(folderpath + "\schema.ini", FileMode.Create, FileAccess.Write)
                Dim srOutput As StreamWriter = New StreamWriter(fsOutput)
                Dim s1, s2, s3, s4, s5, s6, s7, s8, s9, s10 As String
                s1 = "[" + strCSVFile + "]"
                s2 = "ColNameHeader=True"
                s3 = "Format=CSVDelimited"
                s4 = "MaxScanRows=25"
                s5 = "CharacterSet=OEM"
                s6 = "Col1=SCHOOL_NUM Text "
                s7 = "Col2=SCHOOL_NAME Text "
                s8 = "Col3=bad_tax Text"
                s9 = "Col4=pesc Text "
                s10 = "Col5=PrimaryKey_Field Text "
                srOutput.WriteLine(s1.ToString() + ControlChars.Lf + s2.ToString() + ControlChars.Lf + s3.ToString() + ControlChars.Lf + s4.ToString() + ControlChars.Lf + s5.ToString() + ControlChars.Lf + s6.ToString() + ControlChars.Lf + s7.ToString() + ControlChars.Lf + s8.ToString() + ControlChars.Lf + s9.ToString() + ControlChars.Lf + s10.ToString())
            Catch ex As Exception
            End Try
        End Sub

    Next i use the schema file to extract data from a csv file into a dataset.

        Public Function ConnectCSV(ByVal filetable As String) As DataSet
            Dim ds As DataSet = New DataSet
                Dim strConnString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + folderpath.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
                Dim sql_select As String
                Dim conn As System.Data.Odbc.OdbcConnection

                'Create connection to CSV file
                conn = New System.Data.Odbc.OdbcConnection(strConnString.Trim())
                sql_select = "select * from [" + filetable + "]"

                Dim obj_oledb_da = New System.Data.Odbc.OdbcDataAdapter(sql_select, conn)

                obj_oledb_da.Fill(ds, "TextFiledata")


          ' i thougth i needed a primary key when importing into access so i am populating
          ' a column with unique numbers
                Dim dt As DataTable
                dt = ds.Tables(0)
                Dim i As Integer
                Dim j As Integer = 0
                For i = 0 To dt.Rows.Count - 1
                    ds.Tables(0).Rows(i).Item("PrimaryKey_Field") = CType(j, String)
                    j += 1
                Next i

          return ds
          end function

    Next i build the access database tables columns dynamically from the dataset just created above
    The database creation code is show a few comments ago.
    One of the fields is made a primary key

       Sub buildAccessDatabase
            Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, objTable As ADOX.Table, objKey As ADOX.Key

            Cn = New ADODB.Connection
            Cat = New ADOX.Catalog
            objTable = New ADOX.Table
            objKey = New ADOX.Key

            'Open the connection
            Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\someaccessdb.mdb")
            Cat.ActiveConnection = Cn
            objTable.Name = "test_table"

            'get all columns from ds and create them in access
            Dim dr As DataRow
            Dim dt As DataTable
            Dim dc As DataColumn
            For Each dc In ds.Tables("TextFiledata").Columns
                If dc.ColumnName.ToString <> "PrimaryKey_Field" Then
                End If

           'create a primary key (jsut in case on is needed in access for data insert)
            objTable.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, "PrimaryKey_Field")

            'Append the newly created table to the Tables Collection

            ' clean up objects
            objKey = Nothing
            objTable = Nothing
            Cat = Nothing
            Cn = Nothing
         end sub

    Next i create a dataset of the empty table just created in Access, which i plan to populate

        Sub populateaccess(ByVal ds As DataSet)
             Dim objConn As OleDbConnection
             Dim da As OleDbDataAdapter
             Dim cmd As OleDbCommandBuilder
             Dim strConn As String
             Dim dsAccess As New DataSet
             strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =c:\someaccessdb.mdb"
             Dim accessConn As New OleDbConnection(strConn)
             Dim accessAdapter As New OleDbDataAdapter("SELECT * FROM temp_table", accessConn)
             Dim builder As New OleDbCommandBuilder(accessAdapter)
             accessAdapter.Fill(dsAccess, "temp_table")
             For Each dr In ds.Tables("TextFiledata").Rows
                 accessAdapter.Update(dsAccess, "TextFiledata")   ''' ERROR OCCURS HERE. YET WHEN CHECKING THE ACCESS DB IT NOW CONTAINS THE DATA.
             Catch ex As Exception
            End Try
           end sub

    i am using the following code to check the colums and column datatypes in both dataset to ensure they match. no  problems there

            Dim dr As DataRow
            Dim dc As DataColumn
            For Each dc In dsAccess.Tables("Trawler").Columns
                Console.WriteLine(dc.ColumnName.ToString & ";" & dc.DataType.ToString)

    I would ignore the error if i could but it seems hard core as it doesn't get to the catch.
    LVL 41

    Expert Comment

    Let's make sure we're talking about the same thing....

    When you run the program in the debug mode, you get a message at the bottom of the screen, but the program does not stop?   And, in the  example above, you do NOT get a message in the MessageBox.Show(ex.Message)?

    If so, then you're ok... and can ignore that message.

    Author Comment


    in debug mode i get an error on that line. The line of code is highlighted in green. On the error message box there is an option to break, continue and ignore. Continue does nothing but return you back to the error, and ignore is greyed out
    The error does not allow the code to run to the MessageBox.Show(ex.Message) line. All you can do is hit the stop execution button.
    LVL 41

    Assisted Solution

    Hummm... thats' strange.

    Let's try this...   Run the application outside Visual Studio.   Just use Windows Explorer to navigate to the location where the *.exe file is and double-click on it.   Does *that* cause an exception?

    Author Comment

    Yes it now showed the errror.. Why didnt' i think of that.
    ONe of the values being imported was a null and adox defaluts the required field to Yes, so it fails.
    A bit of research is showing me that this is a well known problelm. I would like to be able to get around it..The question is how?

    Author Comment

    Ok found the solution

                    With objTable.Columns(dc.ColumnName.ToString)
                        .Attributes = .Attributes Or ColumnAttributesEnum.adColNullable
                    End With


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
    More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now