Solved

Recommended References for App

Posted on 2001-07-19
13
224 Views
Last Modified: 2008-03-06
Hi,

I have a split application with a shared Access 2K database on an NT 4.0 Server with an Access 2K front end app which users load on local Windows 98 workstations to access the database.

There is one form with about 6 tabs and about 15 list boxes displaying data from all over the database.

Moving between records on the test system is fine (<1 second), but when I load it on the 98 systems it takes about 20+ seconds for the form to load and at least 6 seconds to move to a new record.

I am wondering if this is in any way related to the references that are loaded.  I am currently using:
 - Visual Basic for Applications
 - Microsoft Access 9.0 Oject Library
 - Microsoft DAO 3.6 Object Library
 - Microsoft ActiveX Data Ojects 2.1 Library
 - OLE Automation

In that order.  I don't really know what most of those mean, so I don't know if they are in the ideal order or if they are all necessary.

What is recommended?

Thanks

Ben
0
Comment
Question by:drben
  • 7
  • 4
  • 2
13 Comments
 
LVL 12

Expert Comment

by:Paurths
ID: 6299133
hi drben,

it is most likely the network traffic.
The queries (or sql-statements of the listboxes and form(s)) request all the data from 1 (or more) table(s) from the server and then when the entire table is pulled over the network to the workstation the query is performed in the front end filtering the data.

Are u doing a recalc or requery of the listboxes when u move to a new record?

cheers
Ricky
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6299153
In addition to Ricky's comments, things are slowed down even more when multiple users are concurrently using the same table/database, as opposed to when only one user is accessing the database from the network.
0
 
LVL 1

Author Comment

by:drben
ID: 6299166
Yeah, pretty extensive!  The form is a People form and I need to display a lot of information about them, such as their addresses, phone numbers, relatives, etc. which are all linked tables so I display them in list boxes.

My code follows.  I need to display all the data and I need it to be applicable to the record of the person.

Private Sub Form_Current()
    Me.lstAvailableTypes.Requery
    Me.lstTypes.Requery
    Me.lstNotValid.Requery
   
    Me.lstPhoneNumbers = Null
    Me.cmbNewNumber = Null
    Me.txtExtension = Null
   
    Me.lstPhoneNumbers.Requery
   
    Me.lstRelatives.RowSource = fncGetRelList()
    Me.lstRelatives = Null
    Me.txtRelativeSentence = "(Select Relative) is the (Select Relative Type) of " & Me.PER_FirstName & " " & Me.PER_LastName
    Me.cmbRelativeName = Null
    Me.cmbRelativeType = Null
    Me.chkEmergencyContact = False
    Me.chkEmergencyContactLabel.Caption = "Relative (above) is the Emergency Contact for " & Me.PER_FirstName & " " & Me.PER_LastName
    Me.chkRevEmergencyContact = False
    Me.chkRevEmergencyContactLabel.Caption = Me.PER_FirstName & " " & Me.PER_LastName & " is the Emergency Contact for the Relative (above) "
   
   
    Me.lstLinkedHouseholds = Null
    Me.txtADDR_ID = Null
    Call fncMembers
    Me.sfrAddresses.Visible = False


'   Make sure cmbJumpTo matches record...
'///MODIFY BELOW
    Me.cmbJumpTo = Me.PER_ID
'   Make sure General Page is disabled

    If Me.pgeGeneral.Enabled = True Then
        cmdModify_Click
    End If
'   Me.pgeGeneral.Enabled = False


'   Reset cmdModify's caption
'///
    Me.cmdModify.Caption = "Make Changes to Person"
'   Hide Anything you need to hide...
'///ADD BELOW
    Me.cmdAddNew.Visible = True
    Me.cmdCancel.Visible = False

'///Address stuff
'removed 6/6/01 while reworking addresses
   
    Me.lstLinkedHouseholds.Requery
    Me.lstMembersOfHousehold.Requery


'    Me.PER_linkto_Current_ADDR.Requery
'    Me.PER_linkto_Current_ADDR.Enabled = False

End Sub

Public Function fncGetRelList()
    Dim strRelSQL As String, intMe As Integer, strRowSource As String
    strRowSource = "'REL_ID';'Relative';'Relationship';'Emerg. Cont';RelativeID"

'don't do on add new
If Not IsNull(Me.PER_ID) Then
    intMe = Me.PER_ID
   
    strRelSQL = "SELECT tblRelatives.*, tblRelativeTypes.*, PER1.PER_ID, PER1.PER_FirstName, PER1.PER_LastName, PER1.PER_linkto_GEN, PER2.PER_ID, PER2.PER_FirstName, PER2.PER_LastName, PER2.PER_linkto_GEN, tblRelativeTypes.RELTYPE_Name AS Forward, ReverseMale.RELTYPE_Name AS RevMale, ReverseFemale.RELTYPE_Name AS RevFemale FROM tblRelativeTypes AS ReverseFemale INNER JOIN (tblRelativeTypes AS ReverseMale INNER JOIN (((tblRelativeTypes INNER JOIN tblRelatives ON tblRelativeTypes.RELTYPE_ID = tblRelatives.REL_linkto_RELTYPE) INNER JOIN tblPersonnel AS PER1 ON tblRelatives.REL_linkto_PER1 = PER1.PER_ID) INNER JOIN tblPersonnel AS PER2 ON tblRelatives.REL_linkto_PER2 = PER2.PER_ID) ON ReverseMale.RELTYPE_ID = tblRelativeTypes.RELTYPE_linkto_MALE) ON ReverseFemale.RELTYPE_ID = tblRelativeTypes.RELTYPE_linkto_FEM WHERE (((tblRelatives.REL_linkto_PER1)=" & intMe & ")) OR (((tblRelatives.REL_linkto_PER2)=" & intMe & ")) ORDER BY tblRelativeTypes.RELTYPE_Order"
    Dim rstRelatives As ADODB.Recordset
    Set rstRelatives = New ADODB.Recordset
    rstRelatives.ActiveConnection = CurrentProject.Connection
    rstRelatives.LockType = adLockOptimistic
    rstRelatives.CursorType = adOpenKeyset
    rstRelatives.Source = strRelSQL
    rstRelatives.Open
   
    Do While Not rstRelatives.EOF
   
        If rstRelatives("REL_linkto_PER1") = intMe Then
            'If intMe is in the first the first column, do this
            strRowSource = strRowSource & ";" & rstRelatives("REL_ID") & ";" & rstRelatives("PER2.PER_FirstName") & " " & rstRelatives("PER2.PER_LastName") & ";" & rstRelatives("Forward") & ";" & IIf(rstRelatives("REL_RevEmerContact"), "Yes", "") & ";" & rstRelatives("PER2.PER_ID")
        Else
            strRowSource = strRowSource & ";" & rstRelatives("REL_ID") & ";" & rstRelatives("PER1.PER_FirstName") & " " & rstRelatives("PER1.PER_LastName") & ";" & IIf(rstRelatives("PER1.PER_linkto_GEN") = 1, rstRelatives("RevMale"), rstRelatives("RevFemale")) & ";" & IIf(rstRelatives("REL_EmerContact"), "Yes", "") & ";" & rstRelatives("PER1.PER_ID")
        End If
   
    rstRelatives.MoveNext
    Loop
   
End If
   
    fncGetRelList = strRowSource
End Function

Private Function fncMembers()
    Me.lstMembersOfHousehold.Requery
    If Me.lstMembersOfHousehold.ListCount = 0 Then
        Me.labOtherMembers.Visible = False
    Else
        Me.labOtherMembers.Visible = True
    End If

End Function
0
 
LVL 1

Author Comment

by:drben
ID: 6299171
Dennis,

At the moment, there is only one user and not a ton of network traffic (10/100).

I need this app to be fast.  Is there a better way to do this (are subforms faster?)

Thanks for the input so far!

Ben
0
 
LVL 1

Author Comment

by:drben
ID: 6299176
Yeah, pretty extensive!  The form is a People form and I need to display a lot of information about them, such as their addresses, phone numbers, relatives, etc. which are all linked tables so I display them in list boxes.

My code follows.  I need to display all the data and I need it to be applicable to the record of the person.

Private Sub Form_Current()
    Me.lstAvailableTypes.Requery
    Me.lstTypes.Requery
    Me.lstNotValid.Requery
   
    Me.lstPhoneNumbers = Null
    Me.cmbNewNumber = Null
    Me.txtExtension = Null
   
    Me.lstPhoneNumbers.Requery
   
    Me.lstRelatives.RowSource = fncGetRelList()
    Me.lstRelatives = Null
    Me.txtRelativeSentence = "(Select Relative) is the (Select Relative Type) of " & Me.PER_FirstName & " " & Me.PER_LastName
    Me.cmbRelativeName = Null
    Me.cmbRelativeType = Null
    Me.chkEmergencyContact = False
    Me.chkEmergencyContactLabel.Caption = "Relative (above) is the Emergency Contact for " & Me.PER_FirstName & " " & Me.PER_LastName
    Me.chkRevEmergencyContact = False
    Me.chkRevEmergencyContactLabel.Caption = Me.PER_FirstName & " " & Me.PER_LastName & " is the Emergency Contact for the Relative (above) "
   
   
    Me.lstLinkedHouseholds = Null
    Me.txtADDR_ID = Null
    Call fncMembers
    Me.sfrAddresses.Visible = False


'   Make sure cmbJumpTo matches record...
'///MODIFY BELOW
    Me.cmbJumpTo = Me.PER_ID
'   Make sure General Page is disabled

    If Me.pgeGeneral.Enabled = True Then
        cmdModify_Click
    End If
'   Me.pgeGeneral.Enabled = False


'   Reset cmdModify's caption
'///
    Me.cmdModify.Caption = "Make Changes to Person"
'   Hide Anything you need to hide...
'///ADD BELOW
    Me.cmdAddNew.Visible = True
    Me.cmdCancel.Visible = False

'///Address stuff
'removed 6/6/01 while reworking addresses
   
    Me.lstLinkedHouseholds.Requery
    Me.lstMembersOfHousehold.Requery


'    Me.PER_linkto_Current_ADDR.Requery
'    Me.PER_linkto_Current_ADDR.Enabled = False

End Sub

Public Function fncGetRelList()
    Dim strRelSQL As String, intMe As Integer, strRowSource As String
    strRowSource = "'REL_ID';'Relative';'Relationship';'Emerg. Cont';RelativeID"

'don't do on add new
If Not IsNull(Me.PER_ID) Then
    intMe = Me.PER_ID
   
    strRelSQL = "SELECT tblRelatives.*, tblRelativeTypes.*, PER1.PER_ID, PER1.PER_FirstName, PER1.PER_LastName, PER1.PER_linkto_GEN, PER2.PER_ID, PER2.PER_FirstName, PER2.PER_LastName, PER2.PER_linkto_GEN, tblRelativeTypes.RELTYPE_Name AS Forward, ReverseMale.RELTYPE_Name AS RevMale, ReverseFemale.RELTYPE_Name AS RevFemale FROM tblRelativeTypes AS ReverseFemale INNER JOIN (tblRelativeTypes AS ReverseMale INNER JOIN (((tblRelativeTypes INNER JOIN tblRelatives ON tblRelativeTypes.RELTYPE_ID = tblRelatives.REL_linkto_RELTYPE) INNER JOIN tblPersonnel AS PER1 ON tblRelatives.REL_linkto_PER1 = PER1.PER_ID) INNER JOIN tblPersonnel AS PER2 ON tblRelatives.REL_linkto_PER2 = PER2.PER_ID) ON ReverseMale.RELTYPE_ID = tblRelativeTypes.RELTYPE_linkto_MALE) ON ReverseFemale.RELTYPE_ID = tblRelativeTypes.RELTYPE_linkto_FEM WHERE (((tblRelatives.REL_linkto_PER1)=" & intMe & ")) OR (((tblRelatives.REL_linkto_PER2)=" & intMe & ")) ORDER BY tblRelativeTypes.RELTYPE_Order"
    Dim rstRelatives As ADODB.Recordset
    Set rstRelatives = New ADODB.Recordset
    rstRelatives.ActiveConnection = CurrentProject.Connection
    rstRelatives.LockType = adLockOptimistic
    rstRelatives.CursorType = adOpenKeyset
    rstRelatives.Source = strRelSQL
    rstRelatives.Open
   
    Do While Not rstRelatives.EOF
   
        If rstRelatives("REL_linkto_PER1") = intMe Then
            'If intMe is in the first the first column, do this
            strRowSource = strRowSource & ";" & rstRelatives("REL_ID") & ";" & rstRelatives("PER2.PER_FirstName") & " " & rstRelatives("PER2.PER_LastName") & ";" & rstRelatives("Forward") & ";" & IIf(rstRelatives("REL_RevEmerContact"), "Yes", "") & ";" & rstRelatives("PER2.PER_ID")
        Else
            strRowSource = strRowSource & ";" & rstRelatives("REL_ID") & ";" & rstRelatives("PER1.PER_FirstName") & " " & rstRelatives("PER1.PER_LastName") & ";" & IIf(rstRelatives("PER1.PER_linkto_GEN") = 1, rstRelatives("RevMale"), rstRelatives("RevFemale")) & ";" & IIf(rstRelatives("REL_EmerContact"), "Yes", "") & ";" & rstRelatives("PER1.PER_ID")
        End If
   
    rstRelatives.MoveNext
    Loop
   
End If
   
    fncGetRelList = strRowSource
End Function

Private Function fncMembers()
    Me.lstMembersOfHousehold.Requery
    If Me.lstMembersOfHousehold.ListCount = 0 Then
        Me.labOtherMembers.Visible = False
    Else
        Me.labOtherMembers.Visible = True
    End If

End Function
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6299193
One off-the-cuff suggestion is to make sure you have properly indexed your various tables. Creating indexes for fields used to join tables together also help improve efficiency.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 12

Accepted Solution

by:
Paurths earned 150 total points
ID: 6299202
wow...

the 'requeries' are surely jamming your form!
If the listboxes are all on seperate tabs, then u might wanna split up your code, and in the onChange-event of the tab-element do the requery for the listboxes on that particular tab.

second, the function 'fncGetRelList' is performed each time u go to a new record. It might be that is necesarry, but mind that u open a connection each time, then loop through the recordset (if these are thousands, trouble...)

next is the function 'fncMembers', also called each time moving to a new record.

Last but not least , the IIF-statements in the sql's is a killer.

0
 
LVL 12

Expert Comment

by:Paurths
ID: 6299220
drben,

is this in Access 97 or access 2k ?

did u decompile before using the db?
(first make a backup of it!!!)

http://www.granite.ab.ca/access/decompile.htm

might make things a little better (but wont solve the entire problem)


decompile
compact
compile
0
 
LVL 1

Author Comment

by:drben
ID: 6299312
Thanks guys.  

Paurths,

I'll try the decompile option (after I back it up :)

Also I wanted to mention that moving between the design view and the form view takes up to 30 seconds, even on the production server (pretty fast computer (PII/600/128)

Is that related?  Is the form somehow corrupted?  I imported it into a fresh DB, but same problem.

Ben
0
 
LVL 1

Author Comment

by:drben
ID: 6299392
I tried the decompile and compile and don't really see any difference.  No indication that the decompile actually worked (no message, no change in file size or file date/time propertied) ???

Ben
0
 
LVL 1

Author Comment

by:drben
ID: 6299405
Are you suggesting seeing which tab is selected, and then only requiry that tab's list boxes on Current, then if they switch to a different list box, requery the others then?

That's a pretty neat idea!

Ben
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6300167
hi drben,

< no message, no change in size ..> --> i assume u are using access 2K then (i also had the decompile on A2K, without result, but on Access97 it did wonders for me...)
But like i said before, the decompile action wont probably make your problem vanish.

About the tab-object. Yes , depending on what tab-page is selected requery the listboxes for that particular tab-page.


example of the tab-object (name is 'MyTab')


Private Sub MyTab_Change()
    Select Case MyTab.Value
        Case 0 'first page
            MsgBox "u selected first tab ... " & Me.MyTab.Pages(MyTab.Value).Name
            'do the requery here for the listboxes on this tab-page
        Case 1 'first page
            MsgBox "u selected second tab ... " & Me.MyTab.Pages(MyTab.Value).Name
            'do the requery here for the listboxes on this tab-page
        Case 2 'first page
            MsgBox "u selected third tab ... " & Me.MyTab.Pages(MyTab.Value).Name
            'do the requery here for the listboxes on this tab-page
    End Select
           
End Sub

cheers
Ricky
0
 
LVL 1

Author Comment

by:drben
ID: 6300402
Paurths,

I've changed the form to ONLY requery the list boxes that are visible. Super fast!  Thanks.

As far as the references are concerned, I still haven't heard what they are used for or which ones are recommended.

Ben
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 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

16 Experts available now in Live!

Get 1:1 Help Now