dump dataset into Access Database

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

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??

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

   Try
      ' Do something here
   Catch ex As Exception
      MessageBox.Show(ex.ToString())
   End Try

Bob

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?)

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()
        Try
            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())
            srOutput.Close()
            fsOutput.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
        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
        Try
            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())
            conn.Open()
            sql_select = "select * from [" + filetable + "]"

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


            obj_oledb_da.Fill(ds, "TextFiledata")

            conn.Close()

      ' 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
                objTable.Columns.Append(dc.ColumnName.ToString)
            End If
        Next

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

        'Append the newly created table to the Tables Collection
        Cat.Tables.Append(objTable)

        ' clean up objects
        objKey = Nothing
        objTable = Nothing
        Cat = Nothing
        Cn.Close()
        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
             dsAccess.Tables("temp_table").Rows.Add(dr.ItemArray)
         Next
 
         Try
             accessAdapter.Update(dsAccess, "TextFiledata")   ''' ERROR OCCURS HERE. YET WHEN CHECKING THE ACCESS DB IT NOW CONTAINS THE DATA.
         Catch ex As Exception
             MessageBox.Show(ex.Message)
        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)
        Next

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.

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.

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?

Ok found the solution

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

Thanks.