Jim DeMarco
Jdemarco at hshhp.org
Fri Sep 12 12:21:23 CDT 2003
Good suggestions Henry but I just want to point out that even though it's not done here the recordset could pull in no contents by using SQL "SELECT * FROM myTable WHERE FALSE" instead of the table name. Something like that might help performance too.
Jim DeMarco
-----Original Message-----
From: Henry Simpson [mailto:hsimpson88 at hotmail.com]
Sent: Friday, September 12, 2003 1:07 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] listbox with rowsource type (ala MS)
I would not open a recordset to get the fields collection. The recordset you are opening would pull the entire table contents to the client. You can
iterate the fields collection from the linked table def object directly
without pulling a single record.
I also don't like the manner in which your exit/error handler runs here. This function is called many times as separate calls and called again as the
list is repainted and on each exit the routine tries to destroy oMeetTable =
Nothing. Only in the first call to the initialize should any objects be
created and destroyed and it is sensible to close the object before setting the variable to nothing. In all subsequent calls your error handler is
called because there is no object to destroy as it was only created in the first call.
I would suggest an alternative that calls a separate sub procedure that
returns a static variant containing the array to your function and only
create/destroy object variables (in this case tabledef and possibly field variables) in that sub procedure.
Have you considered the possibility of using a list for each table and
setting the row souce type property to 'field list'?
Hen
>From: Sad Der <accessd666 at yahoo.com>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: Acces User Group <accessd at databaseadvisors.com>
>Subject: [AccessD] listbox with rowsource type (ala MS)
>Date: Fri, 12 Sep 2003 03:01:17 -0700 (PDT)
>
>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
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
***********************************************************************************
"This electronic message is intended to be for the use only of the named recipient, and may contain information from Hudson Health Plan (HHP) that is confidential or privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited. If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message. Thank You".
***********************************************************************************