Solved

Access database update slow

Posted on 2004-08-03
23
254 Views
Last Modified: 2008-01-09
I am updating an access database via VB.  Immediately after the update I reload a tree based on results of the update.  Values that should not be in the tree appear.  If I refresh the tree they disappear or if I use a timer to slow the operations down they'll load properly.  The problem with the timer is it slows the whole project down even if there were only a small number of updates and since it's processor dependant I'm afraid it will be very slow on older computers.  When I ran at full speed the database would be locked before the updates completed and that would cause a crash.  I guess the question is how do I make sure that updates are complete before the program moves to the next function.  Is there a return value that can be captured when the update completes?   Thanks in advance.
0
Comment
Question by:Moed
  • 12
  • 11
23 Comments
 
LVL 1

Expert Comment

by:RichWallace
ID: 11707697
I would recommend using transactional updates and inserts in your queries.  I ran into the same issue in an app I built and using transactional operations cured it right up.

If using VB.NET:

        Dim sConn As New OleDbConnection(sDBConn)
        Dim oDBTrans As OleDbTransaction

        Try
            sCBREConn.Open()
            oDBTrans = sCBREConn.BeginTransaction

            Dim cmdEntry As New OleDbCommand(sbQry.ToString, sConn)

            cmdEntry.Transaction = oDBTrans
            cmdEntry.ExecuteNonQuery()
            oDBTrans.Commit()
        Catch
            MsgBox(Err.Description, MsgBoxStyle.Critical, "Error:")
        End Try

HTH
-Rich
0
 
LVL 1

Expert Comment

by:RichWallace
ID: 11707738
EDIT:  Didn't really clean my code too well;

        Dim sConn As New OleDbConnection(sDBConn)
        Dim oDBTrans As OleDbTransaction

        Try
            sConn.Open()
            oDBTrans = sCBREConn.BeginTransaction

            Dim cmdEntry As New OleDbCommand(sbQry.ToString, sConn)

            cmdEntry.Transaction = oDBTrans
            cmdEntry.ExecuteNonQuery()
            oDBTrans.Commit()
        Catch
            MsgBox(Err.Description, MsgBoxStyle.Critical, "Error:")
        End Try
0
 
LVL 2

Author Comment

by:Moed
ID: 11708110
I'm really new to programming so forgive me if my questions are stupid.  I'm using vb 6, is this usable with vb6.  If so I'll probably have some other question.
0
 
LVL 1

Expert Comment

by:RichWallace
ID: 11708192
shouldn't be too difficult, let me throw something together in VB6 and I'll let you know.
0
 
LVL 1

Expert Comment

by:RichWallace
ID: 11708425
Here's a basic run at it....

Where adoConn is your ADODB connection object and sQry is your query string.  If you're not using ADO, please let me know how you're connecting or a portion of your current code and we'll get it done.

    With adoConn
        .BeginTrans
        .Execute (sQry)
        .CommitTrans
    End With

-Rich
0
 
LVL 2

Author Comment

by:Moed
ID: 11708631
I am using ado.  It's all that works with the infragistics grid.  

Here is the connection info.
  If cnMidwest.State = adStateClosed Then
        With cnMidwest
            .CursorLocation = ADODB.adUseClient
             .Provider = " MSDataShape.1;Persist Security Info=False; Data Source= " & gvDBMIDWEST & "  ;Data Provider=Microsoft.Jet.OLEDB.4.0 "
             .Open
             m_CString = .ConnectionString
        End With
    End If
   
    Exit Sub
   
    Here is the recordset retrieval

Set grdPurchasing.DataSource = Nothing
    Set m_rsGridload = New ADODB.Recordset
 
    If Not m_rsGridload Is Nothing Then
        If m_rsGridload.State = ADODB.adStateOpen Then
            m_rsGridload.Close
        End If
        Set m_rsGridload = Nothing
    End If
         
    sSQL = ("SHAPE {SELECT DISTINCT ContractItems.txtVendorName, POID.POID, Vendors.txtContactPerson," _
        & " Vendors.txtPhoneNumber, datOrderDate, POID.Closed FROM POID, ContractItems, Vendors, Items " _
        & "WHERE POID.POID = ContractItems.POID AND ContractItems.txtVendorID = Vendors.txtVendorID " _
        & "AND ContractItems.txtItemNumber = Items.txtItemNumber AND (Vendors.blnSubContractor = 0) " _
        & "AND (Items.blnlabor = 0) AND (POID.Closed = 0)}  AS cmdCurPO " _
        & "APPEND (( SHAPE {SELECT DISTINCT ContractItems.txtContractNumber, Customers.txtLastName, " _
        & "ContractItems.txtItemDescription, ContractItems.lngQuantity, ContractItems.txtItemStatus, " _
        & "ContractItems.curItemCost, ContractItems.txtSpecialOrder, datReceivedDate, ContractItems.POID, ContractItems.ContractItemsID, blnItemNotPrinted, lngLeadTime, datDeliveryDate, datOrderDate " _
        & "FROM ContractItems, Items, ContractInformation, Customers, Vendors " _
        & "WHERE ContractItems.txtItemNumber = Items.txtItemNumber  " _
        & "AND ContractItems.txtContractNumber = ContractInformation.txtContractNumber " _
        & "AND ContractInformation.txtCustomerID = Customers.txtCustomerID  " _
        & "AND ContractItems.txtVendorID = Vendors.txtVendorID " _
        & "AND (Items.blnNormalStock = 0) AND (Vendors.blnSubContractor = 0)}  AS cmdPODetails " _
        & "APPEND ({SELECT datDeliveryDate, datReceivedDate, ContractItemsID, datOrderDate " _
        & "FROM ContractItems}  AS cmdItemDetail RELATE 'ContractItemsID' TO 'ContractItemsID') " _
        & "AS cmdItemDetail) AS cmdPODetails RELATE 'POID' TO 'POID') AS cmdPODetails ")
   
    With m_rsGridload
        .CursorLocation = ADODB.adUseClient
        .Open sSQL _
          , m_CString _
          , ADODB.adOpenStatic _
          , ADODB.adLockBatchOptimistic _
          , ADODB.adCmdText
    End With

0
 
LVL 1

Expert Comment

by:RichWallace
ID: 11710306
OK, forgive me, but I don't see the update routine.  The use of the transactional method would be used as you perform the update//insert.

Maybe this comes close to what you're encountering...
In the app I was seeing my problem, I would perform an insert of an employee to an employee table, then I'd re-populate a drop down list, and although the INSERT was successful, and I can see the record in the database, the new employee would not show up in the list, unless I reloaded the form and re-initialized the drop down list.

I then added the BeginTrans and CommitTrans methods encapsulating my INSERT/UPDATE statements and after the statement was complete and I repopulate the drop down, the data was accurate.

The joy of transactional updates and inserts means that you guarantee the procedure.  It's an all or nothing operation so it can be very powerful, yet dangerous as well.  If you use BeginTrans and attempt an INSERT/UPDATE, you are essentially notifying the connection that the next operations(s) are critical where if it fails, the entire transaction fails.  In other words...if you have multiple UPDATEs that need to occur and they are dependent upon the previous UPDATE, you would run them all in a transaction, so if the last UPDATE fails for any reason, the previous UPDATEs you ran are to be rolled back as the data is no longer valid.

Here's an example of this...(not tested, so please don't rip me apart :) )

adoConn is my ADODB.Connection object

Private Sub UpdateDB()
 
  On Error GoTo rollbackTrans:

  Dim strUpdate1 As String
  Dim strUpdate2 As String
  Dim strUpdate3 As String
 
  Set strUpdate1 = <Valid Update Statement>
  Set strUpdate2 = <Valid Update Statement>
  Set strUpdate3 = <Valid Update Statement>

  'Open the connection and perform a transactional update routine
  adoConn.Open
  adoConn.BeginTrans
  adoConn.Execute(strUpdate1)
  adoConn.Execute(strUpdate2)
  adoConn.Execute(strUpdate3)
  adoConn.CommitTrans
  adoConn.Close

rollbackTrans:
  adoConn.RollbackTrans  'Rolls back any updates that have occurred upon failure; if strUpdate3 fails, the previous 2 strUpdatex are rolled back.
  adoConn.Close
  MsgBox("Blah, Blah, Error blah")
  End Sub

End Sub
0
 
LVL 2

Author Comment

by:Moed
ID: 11710595
The code below is supposed to set the connection up for batch updates.  The grid I use just requires a single line to update.  "grid1.update" does the trick.  However what you're describing above is exactly the problem I'm having.

With m_rsGridload
        .CursorLocation = ADODB.adUseClient
        .Open sSQL _
          , m_CString _
          , ADODB.adOpenStatic _
          , ADODB.adLockBatchOptimistic _
          , ADODB.adCmdText
    End With
Does the code you sent work ok with access?  The next question is can you help me implement it.  Like I said earlier I've only been programing for six weeks and it looks pretty complicated.
0
 
LVL 1

Expert Comment

by:RichWallace
ID: 11711367
The code I sent should run fine with Access, ADO is very flexible when it comes to data sources.  I have used it with Access before but all of my current projects are using .NET so I use the .NET data components, but technically, using ADO in VB6 will run fine.

I can try to help, I never let my grids manage my updates, but I have no problem jumping in and helping.
0
 
LVL 2

Author Comment

by:Moed
ID: 11711835
I'll be back in the office on Thursday and we'll give it a whirl.  Thanks a million
0
 
LVL 2

Author Comment

by:Moed
ID: 11725139
ok, I have a test environment setup and I have copied your code into a sub and changed the connection name to one already setup.  some questions  1.  How to setup the update string(s) and where to execute them and why would I need multiple update strings.   I don't think I need the set command in vb?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:RichWallace
ID: 11726648
In VB6, you'd build the query string in a string concatenation fashion:

Dim strUpdate As String
Set strUpdate = "UPDATE table SET " & _
Set strUpdate = strUpdate & "Field1 = '" & Value1 & "', " & _
Set strUpdate = strUpdate & "Field2 = '" & Value2 & "'"
...

Once you have your query set up, modify your connection object to support transactions:
adoConn.Open
adoConn.BeginTrans

Now execute the query:
adoConn.Execute(strUpdate)

If the update was successful, close the transaction and clean up:
adoConn.CommitTrans
adoConn.Close
adoConn = Nothing

Don't forget your error handling :)


For your questions...
1)  You probably don't need multiple updates, I was using 3 as an example of transactional dependent updates, but if you have one query, you should be fine with the code above.
2)  Get rid of the command object, since you're using the connection object exclusively, you no longer need it.

HTH
-Rich

0
 
LVL 2

Author Comment

by:Moed
ID: 11726722
On item 2 what do you mean command object?
0
 
LVL 1

Expert Comment

by:RichWallace
ID: 11726726
Sorry, I read yor last post incorrectly.  Ignore that.
0
 
LVL 2

Author Comment

by:Moed
ID: 11726845
How do I write the query so it knows to update a changed value in the grid.  
0
 
LVL 1

Expert Comment

by:RichWallace
ID: 11727993
I'm not sure I know what you mean by that.  

Do you mean how do you extract the changed value from the grid to make the update query or how to update the query to show the changed value in the database after the update?
0
 
LVL 2

Author Comment

by:Moed
ID: 11728112
When I change a value in the grid how do I select that value in the update sql.   I guess what I don't understand is if I say update this record set does ADO automatically know which values have changed or do I have to specify each individual piece of data I want to update.  

For example in this grid only two fields out of about 20  can change.   If I write an update statement that says"  update table1.field1, table2.field1" will that update all the changes for each field through out the whole grid.  I really don't know how the update statment works.
0
 
LVL 1

Expert Comment

by:RichWallace
ID: 11728993
I'm at a loss, I rarely used ADO in the way it appears your app does and I've also been using .NET for the past few years.  If anybody else is watching this thread, please advise.
0
 
LVL 2

Author Comment

by:Moed
ID: 11729176
Rich;
Your not at a loss I just don't know enough to ask intelligent questions.   I am making progress.  If you'll just monitor the thread for a little while I'll keep you posted.  Your code really helped I just needed to dig in.  Almost there!!!
0
 
LVL 2

Author Comment

by:Moed
ID: 11729622
I read that only one table can be updated in each sql statement.  Is that correct?   That would explain the multiple update strings?  If thats not true could you post an example.  I tried multiple tables and it doesn't work with my syntax.  
When I try to update a boolean field I get a data type mismatch error.  

The field is defined as boolean and the variable is defined as boolean.  Here is the code:
strUpdate2 = (" UPDATE POID SET POID.Closed = '" & m_Closed & " ' ")
0
 
LVL 1

Expert Comment

by:RichWallace
ID: 11731087
You can do multiple table updates using a JOIN.

UPDATE
  tableOne, tableTwo
SET
  tableOne.Field1,
  tableOne.Field2,
  tableTwo.Field1
FROM tableOne
  INNER JOIN tableTwo ON tableOne.ID = tableTwo.tblTwoID
WHERE tableOne.ID = X

You won't need the single apostrophe's in your query.  You can just do ("UPDATE POID SET POID.Closed = " & m_Closed)
0
 
LVL 2

Author Comment

by:Moed
ID: 11731235
RichWallace;

Thanks for hanging in there.  If you don't mind I won't close this question for a couple of days so I can talk to you a little more.  I'm making slow but steady progress. My first attempt updated all the records in the table :(  When you're nearly 50 it's a little slower going than you young guys. Especially when I've only been at it for a couple of months.  I keep upping the points on this question as we go.  Thanks again.  Stay tuned please.
0
 
LVL 1

Accepted Solution

by:
RichWallace earned 400 total points
ID: 11731272
No problem, make sure you have a where clause on your UPDATE command to ensure you're only updating those records you want.  You may need to add some AND lines to narrow it down in your tables.  Such as:

UPDATE
  tableOne, tableTwo
SET
  tableOne.Field1,
  tableOne.Field2,
  tableTwo.Field1
FROM tableOne
  INNER JOIN tableTwo ON tableOne.ID = tableTwo.tblTwoID
WHERE tableOne.ID = X
AND tableTwo.OtherID = X
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

10 Experts available now in Live!

Get 1:1 Help Now