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
266 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

911 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

20 Experts available now in Live!

Get 1:1 Help Now