Henry Simpson
hsimpson88 at hotmail.com
Fri Sep 12 22:29:05 CDT 2003
The question specifies that the call back is getting lists of fields from a
'couple of tables' hence the 'xxx' part of the table name should probably be
interpreted as a placeholder for various specific table names which is why I
suggested using multiple lists if using a RowSourceType of field list. In
addition, you may note that the size of the array is redimmed progressively
larger to include fields for a total of three tables. The field list
rowsource is not an option if the fields of several table must be included
in a single list.
Another thing I see that is objectionable is that there are three calls to
CurrentDb in the initialize procedure which causes three calls over a LAN to
the backend file to refresh the database collections and each of these calls
takes time as well. Had a database variable been dimensioned and set in a
single call to CurrentDb, there could have been a minor improvement. Far
bigger is the improvement from limiting the size of the recordsets by
setting a condition that returns no records (numeric pk = -1 or Where
False).
Looking at this sample again, I also note that the recordset variable is set
to nothing twice in the initialize procedure and once in the function's
general exit (which happens many times as the list is created and filled).
I understand the desire to kill the object in an error handler to ensure
that a crash in the initialize procedure doesn't leave any surviving objects
to hang on to resources and there is no problem setting a nothing object to
nothing, however, i believe the best approach is to close a recordset object
before setting it to nothing and attempting to close an object that hasn't
been opened will trigger an error. That would require an on error resume
next in the exit procedure (which is also slower than an error handler
without the resume next). The two alternatives are to close and set to
nothing in the initialize case and in the error handler only and not in the
exit routine or, better yet, call an external procedure that sets a module
level array for the list box or have a function return a static array (not
static in the redim sense, but in the variable lifetime sense, Microsoft
sucks with their careless terminology) and properly create and destroy any
object variable in a procedure that will only run once.
If the ultimate need is to display field names from multiple tables, the
best proposition is to avoid the recordset object and just pull the field
collections of the tabledefs. Whenever I use callbacks that require the
creation and destruction of object variables, I call an external procedure
to initialize a data array in order that I can write the cleanest error
handler that will run where it belongs and give the clearest indication of
the actual source of the error.
I too agree that two minutes is intolerable and that even four seconds is
bad but we do not know how much of the tab change time is attibutable to
this portion of the code. In fact, four seconds could be stunningly fast
depending on the LAN speed and how much data and the number and type of
controls. All we really know is that there is a minute and fifty six
seconds difference between two different network environments and the
question implies that the listbox rowsource is somehow at fault. Obviously
the form tab is LAN sensitive or, all things being equal, there would be no
difference between LANs, so the solution is to cut down on calls from the FE
to the data file. Multiple calls to CurrentDb, opening recordsets, pulling
tables of data to get a field list and even setting them to nothing before
resetting them cause LAN traffic. If the two minutes can be improved on,
then so can the four seconds.
Hen
>From: "Charlotte Foust" <cfoust at infostatsystems.com>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem
>solving"<accessd at databaseadvisors.com>
>Subject: RE: [AccessD] listbox with rowsource type (ala MS)
>Date: Fri, 12 Sep 2003 08:05:44 -0700
>
>IMO 4 seconds is too long to load each tab page as well. Your callback
>routine is repeatedly opening and closing what looks like the same
>recordset or else you pasted the same acLBInitialize case code in 3
>times. A field list as the rowsourcetype would be a faster way to get a
>list of fields for a single table.
>
>Charlotte Foust
>
>-----Original Message-----
>From: Sad Der [mailto:accessd666 at yahoo.com]
>Sent: Friday, September 12, 2003 2:01 AM
>To: Acces User Group
>Subject: [AccessD] listbox with rowsource type (ala MS)
>
>
>Hi group,
>
>i've got a problem. I'm using a tab control. On all
>tabs i've got listboxes wich are filled with field
>names of a couple of tables. These listboxes are
>filled using the rowsource type thingy. This seems to
>be the original MS-way-of-programming.
>
>Below I pasted the code. In the property RowSourceType
>of the listbox I fill in the function name.
>
>When I programmed it it worked fine. The client is on
>the desktop of the user and the BE is on our test
>network. It took about 4 seconds to load each
>tab-page.
>
>Now it's in production (on another network) and if a
>switch between tab pages now takes almost 2 MINUTES!!!
>
>Any ideas how this is possible??
>
>tia
>
>Sander
>Private Function ListMeetVelden(fld As Control, id As
>Variant, row As Variant, col As Variant, code As
>Variant) As Variant
>
> Static strVelden() As String
> Static lngRows As Long
> Dim varRetVal As Variant
> Dim oMeetTable As DAO.Recordset
> Dim i As Integer
>
> On Error GoTo ListMeetVelden_Error
>
> Select Case code
> Case acLBInitialize
> ReDim strVelden(0)
> 'fill array with fields.
> Set oMeetTable = CurrentDb().OpenRecordset(Name:="tblxxxx",
>Type:=dbOpenSnapshot, Options:=dbOpenForwardOnly)
> With oMeetTable
> For i = 0 To .Fields.Count - 1
> strVelden(UBound(strFields)) = "C_" &
>.Fields(i).Name
> ReDim Preserve strVelden(0 To
>UBound(strVelden) + 1)
> Next i
> End With
>
> Set oMeetTable = Nothing
> 'fill array with fields.
> Set oMeetTable = CurrentDb().OpenRecordset(Name:="tblxxx",
>Type:=dbOpenSnapshot, Options:=dbOpenForwardOnly)
> With oMeetTable
> For i = 0 To .Fields.Count - 1
> strVelden(UBound(strFields)) = "D_" &
>.Fields(i).Name
> ReDim Preserve strVelden(0 To
>UBound(strFields) + 1)
> Next i
> End With
>
> Set oMeetTable = Nothing
> 'fill array with fields.
> Set oMeetTable = CurrentDb().OpenRecordset(Name:="tblxxx",
>Type:=dbOpenSnapshot, Options:=dbOpenForwardOnly)
> With oMeetTable
> For i = 0 To .Fields.Count - 1
> strVelden(UBound(strVelden)) = "E_" &
>.Fields(i).Name
> ReDim Preserve strVelden(0 To
>UBound(strVelden) + 1)
> Next i
> End With
>
> ReDim Preserve strVelden(0 To UBound(strVelden)
>- 1)
>
> lngRows = UBound(strVelden) + 1 ' array is
>0-based
> varRetVal = lngRows
> Case acLBOpen
> varRetVal = Timer 'Unique ID for control
> Case acLBGetRowCount
> varRetVal = lngRows
> Case acLBGetColumnCount
> varRetVal = 1
> Case acLBGetColumnWidth
> varRetVal = -1 'Default of -1 uses default
>column width
> Case acLBGetValue
> varRetVal = strVelden(row)
> Case acLBEnd
> Erase strVelden
> End Select
>
> ListMeetVelden = varRetVal
>
>ListMeetVelden_Exit:
> Set oMeetTable = Nothing
> Exit Function
>ListMeetVelden_Error:
> Set oMeetTable = Nothing
> Call g_oGenErr.Throw("xxx.frmxxx", "tblxxxx")
>End Function
>
>
>
>__________________________________
>Do you Yahoo!?
>Yahoo! SiteBuilder - Free, easy-to-use web site design software
>http://sitebuilder.yahoo.com
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail