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
262 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
Comment Utility
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
Comment Utility
Just keep in mind that...
Factors such as Name AutoCorrect, Subdatasheets and long network path names cause severe degradation
0
 

Author Comment

by:rashields
Comment Utility
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
 
LVL 11

Expert Comment

by:AkisC
Comment Utility
Can you post the code that saves the new record?
Also visual basic  front-end application is VB6 -or VB.NET?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:rashields
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
We adopted a different SQL technique in the end.
Thanks anyway for your interest.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

772 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

11 Experts available now in Live!

Get 1:1 Help Now