Solved

slow to save record in Access 2003 from a visual basic applicaton that worked well with Access 97

Posted on 2007-11-21
10
269 Views
Last Modified: 2013-11-28
We run a database system that uses a visual basic front-end application (on Win XP pc's) with an Access database on a mapped drive on a server.  There are about 5 concurrent users.  It has been working happily for many years with the database being in Access 97 format.
Recently we updated the application and took the opportunity to convert the database to Access 2003.  There were some necessary changes to the vb code to accommodate communication with the newer Access format.
The updated system works - but whenever we save a new record there is now a delay of about 20 - 40 seconds!  Previously there was virtually no delay.  Can you give us any clues as to why these awful delays may be occurring, and what we can do to avoid them?  I don't think we can go back to Access 97!
0
Comment
Question by:rashields
  • 5
  • 4
10 Comments
 
LVL 11

Expert Comment

by:AkisC
ID: 20331352
Indeed there are issues that make Access2003 slower than Access97
Take a look at this and make the necessary corrections
http://www.granite.ab.ca/access/performancefaq.htm
0
 
LVL 11

Expert Comment

by:AkisC
ID: 20331362
Just keep in mind that...
Factors such as Name AutoCorrect, Subdatasheets and long network path names cause severe degradation
0
 

Author Comment

by:rashields
ID: 20333367
Thank you, AkisC, for these pointers.  I have been going through the performance FAQ point by point but sadly  do not seem to have found the solution yet.  I really thought that the subdatasheet Name property change to [None] would fix things - but it hasn't!
Let me give you a few more clues:
Our main backend database is 181 MB.  It has about 30 tables.  The largest table has 180,000 records.  It has been compacted, of course.  It has a reasonably short name (nucmed.mdb) and is just one folder down from the mapped root.
We also have a rather smaller test database with the same name in a parallel folder.  It's 80 MB and the largest table has 130,000 records.  So it's not that small!
We don't get the performance problem at all when we direct our vb frontend to this test database!  It works just fine!
When we direct the same vb application to the main backend database we get these awful delays - of between 5 and 40 seconds - when we save a new record.
So what can be causing this change in performance?  Both the test and the main database were upgraded from Access 97 to Access 2003.
We're using Jet 4.0, by the way, but I have no idea what SP or where to look for that.  Please advise if this may be relevant.
The problem. BTW, seems to be independent of server technology; we have recently changed over from Novell to MS Windows 2003 server but the effect I have described is exactly the same under both servers.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 11

Expert Comment

by:AkisC
ID: 20333418
Can you post the code that saves the new record?
Also visual basic  front-end application is VB6 -or VB.NET?
0
 

Author Comment

by:rashields
ID: 20335490
Please see snippet below.  This is the code that saves ("updates") a new "report", which is basically a small block of text.  This saving process is now particularly slow whereas it was instantaneous when we had the Access 97 database.
This is VB6.
Hope this helps!
Private Sub cbUpdate_Click()
Dim i As Integer
Dim rsActive As ADODB.Recordset
    
    Set rsActive = New ADODB.Recordset
    
    If cmbReporter(0).ListIndex = -1 Then 'CR 14/12/06 SM
        Warn "At least one reporter needs to be entered"
        cmbReporter(0).SetFocus
        Exit Sub
    Else
        If cmbReporter(0).ListIndex <> -1 Then
            mReporter1 = cmbReporter(0).ItemData(cmbReporter(0).ListIndex)
        Else
            mReporter1 = 0
        End If
        If cmbReporter(1).ListIndex <> -1 Then
            mReporter2 = cmbReporter(1).ItemData(cmbReporter(1).ListIndex)
        Else
            mReporter2 = 0
        End If
        If cmbReporter(2).ListIndex <> -1 Then
            mReporter3 = cmbReporter(2).ItemData(cmbReporter(2).ListIndex)
        Else
            mReporter3 = 0
        End If
    End If
    
    If mReportID = 0 Then
        
        'get new number for report ID
        Do
            mReportID = NewReportNo
            rsReport.Open "SELECT [Report ID] FROM [Report] WHERE [Report ID] = " & mReportID, gCn, adOpenKeyset, adLockOptimistic
            If rsReport.RecordCount = 0 Then
                rsReport.Close
                Exit Do
            Else
                rsReport.Close
            End If
        Loop
        'save report ID to referral table
        gCn.Execute "UPDATE [Referral Table] SET [Patient Status] = 11, [Change Date] = #" & Format(Date, "dd mmm yyyy") & "#, [Change By] = " & gUserID & ", [Report ID] = " & mReportID & " WHERE [Referral ID] = " & mRequest
 
        'Check if record in active patient list
        rsActive.Open "SELECT * FROM [Active Patient List] WHERE [Referral ID] = " & mRequest, gCn, adOpenKeyset, adLockOptimistic
        If rsActive.RecordCount = 0 Then
            rsActive.AddNew
            rsActive("Referral ID") = mRequest
            rsActive("Addition Date") = Date
            rsActive.Update
        End If
        rsActive.Close
        Set rsActive = Nothing
        'save report in report table
        'rsReport.Open "SELECT * FROM Report WHERE [Report ID] = 0", gCn, adOpenKeyset, adLockOptimistic
        rsReport.Open "SELECT * FROM Report", gCn, adOpenKeyset, adLockOptimistic 'CR 14/12/06 SM
        rsReport.AddNew
        rsReport("Report ID") = mReportID
        rsReport("Date Entered") = Date
        rsReport("Report Text") = rtfReport.TextRTF
        rsReport.Update
        rsReport.Close
        mUpdated = True
        'Need to reflect new Report ID on this and parent form
        lblStatus(2).Caption = mReportID
'        gCn.Execute "UPDATE [Referral Table] SET [Patient Status] = 11 WHERE [Referral Id] = " & mRequest
        If mRequest <> 0 Then   'CR 14/12/06 SM
            SaveReporters
        End If
        For i = 0 To Forms.Count - 1
            If Forms(i).Tag = "R" & Trim(Str(mRequest)) Then
                Forms(i).ShowRequest
            End If
        Next
        gRefreshActive = True
    Else
        MsgBox "Overwrite existing report"
        rsReport.Open "SELECT * FROM Report WHERE [Report ID] = " & mReportID, gCn, adOpenKeyset, adLockOptimistic
        rsReport("Report Text") = rtfReport.TextRTF
        rsReport.Update
        rsReport.Close
        mUpdated = True
        If mRequest <> 0 Then   'CR 14/12/06 SM
            SaveReporters
        End If
        gCn.Execute "UPDATE [Referral Table] SET [Change Date] = #" & Format(Date, "dd mmm yyyy") & "#, [Change By] = " & gUserID & " WHERE [Referral ID] = " & mRequest
    End If
    cbEdit.Enabled = True
    cbUpdate.Enabled = False
    cbCancel.Enabled = False
    frReport.Enabled = False
    cbPrint.Enabled = True
    If rtfReport.GetLineFromChar(Len(rtfReport.Text)) > 18 Then
        fsbReport.Visible = True
    End If
End Sub

Open in new window

0
 
LVL 11

Expert Comment

by:AkisC
ID: 20336254
You have 2 time consumers in your code
1rst Line 31-41
'get new number for report ID
        Do
            mReportID = NewReportNo
            rsReport.Open "SELECT [Report ID] FROM [Report] WHERE [Report ID] = " & mReportID, gCn, adOpenKeyset, adLockOptimistic
            If rsReport.RecordCount = 0 Then
                rsReport.Close
                Exit Do
            Else
                rsReport.Close
            End If
        Loop
2nd Line 57 (big time consumer)
rsReport.Open "SELECT * FROM Report", gCn, adOpenKeyset, adLockOptimistic 'CR 14/12/06 SM

Try the code bellow to see if there is an improvment in timing
replace lines 29-88

    If mReportID = 0 Then
'// I suppose that the field [Report ID] is an autonumber
'// What I'd recomend is that you set the Default Value of [Date Entered] field to =Date() in the [Report] tbl ***
       
        'save report in report table AND get new number for report ID
        rsReport.Open "SELECT * FROM Report WHERE [Report ID] = ID", gCn, adOpenKeyset, adLockOptimistic
        rsReport.AddNew
        rsReport("Report Text") = rtfReport.TextRTF
'rsReport("Date Entered") = Date  'un-rem this line id you did not set the Default value as above ***
        rsReport.Update
        mReportID = rsReport("Report ID")
        rsReport.Close

        'save report ID to referral table
        gCn.Execute "UPDATE [Referral Table] SET [Patient Status] = 11, [Change Date] = #" & Format(Date, "dd mmm yyyy") & "#, [Change By] = " & gUserID & ", [Report ID] = " & mReportID & " WHERE [Referral ID] = " & mRequest
 
        'Check if record in active patient list
        rsActive.Open "SELECT * FROM [Active Patient List] WHERE [Referral ID] = " & mRequest, gCn, adOpenKeyset, adLockOptimistic
        If rsActive.RecordCount = 0 Then
            rsActive.AddNew
            rsActive("Referral ID") = mRequest
            rsActive("Addition Date") = Date
            rsActive.Update
        End If
        rsActive.Close
        Set rsActive = Nothing
        '
        mUpdated = True
        'Need to reflect new Report ID on this and parent form
        lblStatus(2).Caption = mReportID
'        gCn.Execute "UPDATE [Referral Table] SET [Patient Status] = 11 WHERE [Referral Id] = " & mRequest
        If mRequest <> 0 Then   'CR 14/12/06 SM
            SaveReporters
        End If
        For i = 0 To Forms.Count - 1
            If Forms(i).Tag = "R" & Trim(Str(mRequest)) Then
                Forms(i).ShowRequest
            End If
        Next
        gRefreshActive = True
    Else
        MsgBox "Overwrite existing report"
        rsReport.Open "SELECT * FROM Report WHERE [Report ID] = " & mReportID, gCn, adOpenKeyset, adLockOptimistic
        rsReport("Report Text") = rtfReport.TextRTF
        rsReport.Update
        rsReport.Close
        mUpdated = True
        If mRequest <> 0 Then   'CR 14/12/06 SM
            SaveReporters
        End If
        gCn.Execute "UPDATE [Referral Table] SET [Change Date] = #" & Format(Date, "dd mmm yyyy") & "#, [Change By] = " & gUserID & " WHERE [Referral ID] = " & mRequest
    End If
0
 
LVL 11

Accepted Solution

by:
AkisC earned 500 total points
ID: 20372911
Hi rashields
Are you still on the Internet? Alive and 100% operational? :-)
Did you find your solution?

Tell me if I can be of any more help
Have fun coding...
0
 

Author Comment

by:rashields
ID: 20373202
Hi,
Yes indeed - many thanks for your work on this and for your continuing interest!
We haven't yet been able to try your code changes, so I can't say yet whether it's a solution.  
One thing that's bothering me is that this is just one example of slowness, and if we adopt such coding changes we should have to apply similar chnages to many parts of the system.  We didn't have this problem at all when we were using Access 97 and we still don't have the problem at all with the test database in Access 2003.  So what I was hoping for was some fix that could be applied to the database or the Jet engine or the vb interface that would restore the performance of the original vb coding.
0
 

Author Closing Comment

by:rashields
ID: 31410428
We adopted a different SQL technique in the end.
Thanks anyway for your interest.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

830 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