Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1036
  • Last Modified:

dump dataset into Access Database

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")
  • 6
  • 3
  • 2
2 Solutions
Bob LearnedCommented:
This line changes all the row states from Added to Unmodified so nothing happens:


vbstroehleAuthor Commented:

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
vbstroehleAuthor Commented:
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 system.data.dll"

The error occurs but the access database is populated correctly.

I may need a primary key in the table??
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Bob LearnedCommented:
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

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?)
vbstroehleAuthor Commented:
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.
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.
vbstroehleAuthor Commented:

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.
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?
vbstroehleAuthor Commented:
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?
vbstroehleAuthor Commented:
Ok found the solution

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


Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now