.fill takes ages to load when updating access database

[UKnod]

I am running a database with about 200,000 records, so not too big. It runs fine except when I try to update one of the tables. Here is the code:

      Private Sub Add_History()

        Dim cb5 As New OleDb.OleDbCommandBuilder(da5)
        Dim dsNewRow5 As DataRow

        Dim sql5 As String
        sql5 = "SELECT * FROM tblHistory order by ID"

        dsNewRow5 = ds5.Tables("History").NewRow()

        ds5.Tables("History").Rows.Add(dsNewRow5)

        da5.Update(ds5, "History")

        da5.Dispose()
        ds5.Clear()
        da5 = New OleDb.OleDbDataAdapter(sql5, con)
        da5.Fill(ds5, "History")

        da5.Update(ds5, "History")

        New_hist()
errr:
    End Sub

      Private Sub New_hist()

        Dim cb5 As New OleDb.OleDbCommandBuilder(da5)
        Dim x
        On Error GoTo errr

        ds5.Tables("History").Rows(Hist_Rows - 1).Item(6) = contactID
        ds5.Tables("History").Rows(Hist_Rows - 1).Item(1) = DateTime.Now
        ds5.Tables("History").Rows(Hist_Rows - 1).Item(3) = username

da5.Update(ds5, "History")

    End Sub

Its the da5.Fill(ds5, "History") that takes about 10 seconds to fill. If there are no new updates, ie I dont use

            dsNewRow5 = ds5.Tables("History").NewRow()

            ds5.Tables("History").Rows.Add(dsNewRow5)

            da5.Update(ds5, "History")

then it fills instantly. It seems that when you want to add a record the fill takes forever.

Anyone got any ideas how I can speed this process up.

[TnTinMN]

UKnod,

That code is just plain nasty! :(

It appears that you want to add a new record to the History table in the database. You have created a dataset (ds5) elsewhere in your code.

Does your code need to access the dataset's History table elsewhere, or do you create it just for the purpose of adding a new row?

If you do not need to maintain records in memory, you could do something like this:

      ' Modify the following line to reflect your connection string
      Dim cn As New OleDbConnection(My.Settings.PlayConnectionString)

      'create command to use
      Dim cmd As New OleDbCommand("History", cn)
      cmd.CommandType = CommandType.TableDirect

      ' create a dataadapter and commandbuilder for it

      Dim da As New OleDbDataAdapter(cmd)
      Dim cb As New OleDbCommandBuilder(da)

      ' create a temporary table to hold History's schema
      Dim dt As New DataTable

      ' Fill the schema so that we can get the row template NewRow
      da.FillSchema(dt, SchemaType.Source)

      Dim r As DataRow = dt.NewRow

      r(6) = contactID
      r(1) = Now()
      r(3) = username

      ' process the new row
      da.Update(New DataRow() {r})

      ' cleanup
      cb.Dispose()
      da.Dispose()
      cmd.Dispose()
      cn.Dispose()

This code could be shorter if you would post the History table's schema (field definition listing) and note the keyed fields. Knowing the schema would allow creating an insert command and the datatable code could be eliminated.

[UKnod]

Hi
Thanks for this, however. I am a VB6 man at heart and it is so easy to do this on VB6. I understand that my code may be nasty, but it is the only way I can make it work by saving the data to disc rather than in memnory so as others can see the changes, this is why it is nasty!
That being said I dont need the history in memory so your solution will be fine if I new how you did it, it is a little over my head. Forgive me for being a little slow but how do I declare the oledbconnection and make the connection to the DB.
Thisis how I am doing it at the moment:

dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source =" & app_path & "\AddressBookLayout.mdb;"
        password = "Jet OLEDB:Database Password=12345678;"
        con2.ConnectionString = dbProvider & dbSource & password

Cheers.

[UKnod]

Oh and the field definitions are 7 fields the first being an auto number.
ID
Datee
Type
Userr
Regarding
Attachment
ContactID

[UKnod]

OK I changed your code to this:

        Dim cn As New OleDb.OleDbConnection(dbProvider & dbSource & password)
        'create command to use
        Dim cmd As New OleDb.OleDbCommand("tblHistory", cn)
        cmd.CommandType = CommandType.TableDirect
        ' create a dataadapter and commandbuilder for it
        Dim da As New OleDb.OleDbDataAdapter(cmd)
        Dim cb As New OleDb.OleDbCommandBuilder(da)

Which seems to get rid of all the errors and makes a little more sense to me and runs. However it doesn't save any data in the table. Have I done the wrong thing.

[TnTinMN]

No. You did not do anything wrong.

A line of code got deleted when I was editing it while posting. :(

Add dt.Rows.Add(r) somewhere before the statement: da.Update(New DataRow() {r})

I am a VB6 man at heart and it is so easy to do this on VB6

Then you are probably more comfortable with ADO. Use it then. I'm a bit rusty with ADO, but I believe that this should do the trick once you add the adodb reference. It worked on my quick test.

Remember to to make the needed changes to the connection string (in cn.open) and TableName (in the rs.Open).

     ' Add Project .Net Reference to adodb.  see:  http://support.microsoft.com/kb/318559

      Dim cn As New ADODB.Connection
      Dim rs As New ADODB.Recordset

      cn.Open(My.Settings.PlayConnectionString)

      rs.Open(Source:="History", ActiveConnection:=cn, CursorType:=ADODB.CursorTypeEnum.adOpenDynamic, LockType:=ADODB.LockTypeEnum.adLockOptimistic)
      rs.AddNew()
      rs.Fields.Item("Datee").Value = Now
      rs.Fields.Item("ContactID").Value = contactID
      rs.Fields.Item("Userr").Value = username
      rs.Save()
      cn.Close()

[UKnod]

Hi
Yes that worked a treat, but look at that ADO code, the same as VB6, I never new you could do that, its great, so why do it the other way, ie ole I guess whatever that is. Is there any advantage of using one over the other? Because if not then I shal go back to using the old method. ALso can you have ADO and OLE in the same programme, like for example using OLE for the customer contact records but ADO for the history etc.

[TnTinMN]

…so why do it the other way, ie ole I guess whatever that is

I’ll try to explain this to the best of my ability. I may get some of the facts and terminology mixed up though. I never was one for all the fancy computer science terminology.

ADO,Net is the basis for all .Net database interfacing. OleDB is layer above ADO that provides addition tools to manipulate the data and interface with the other .Net data classes such as a DataSet.

See: Overview of ADO.NET

. Is there any advantage of using one over the other?

The choice comes down to selecting the correct tool for the given task and whether or not you know how to use that tool.

For a case like appending a record to datatable, you could go either route. You could also have used OLEDBCommand with a SQL “Insert” statement to do the same thing directly as that convoluted code shown above that was crafted to prevent the need for you to generate the “Insert” SQL yourself (i.e. same destination, but a different path).

The biggest advantage is when you use the full design functionality of Visual Studio for creating a “Data Access Layer” in the graphical environment. Many people will create a dataset on the fly in their code just to make work with selected abstraction layer like OleDB or SQLClient without ever realizing that there is so much more that they can accomplish in they just learn how to use the tools VS provides to them to create a “Typed DataSet”. The “Typed DataSet” designer allows you layout your needed Tables, constraints, queries, etc. It will then generate a class with the method to manipulate your data.

See: Tutorial 1: Creating a Data Access Layer

ALso can you have ADO and OLE in the same programme, like for example using OLE for the customer contact records but ADO for the history etc.

Mix and match to your heart’s content. As I said before, select the correct tool for the job.

-----------------------------------------

Please close this thread out if you don't have any other questions.

Thanks.