Henry Simpson
hsimpson88 at hotmail.com
Fri Sep 12 13:19:50 CDT 2003
Any steps taken to minimize the size of the recordset will help and your
suggestion will probably result in nearly exactly the same performance
improvement as the suggestion I made. I doubt that modifying the exit/error
routine will result in a detectable performance difference. However...
I'm not sure, but might that not still involve opening another request from
the remote file? My understanding is that once you have a reference
returned by CurrentDb, as is used in the example, you have refreshed the
database collections making the field collection available locally and this
would save the LAN wait time plus avoid the creation of a recordset object
which is a pretty chunky obect as it has more properties and methods than a
tabledef object even if it contains no records. I doubt that it is good
practice to open a recordset to get at a tabledef's fields collection since
it is directly accessible from the tabledef without opening a recordset
based on that table.
Hen
>From: "Jim DeMarco" <Jdemarco at hshhp.org>
>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 13:21:23 -0400
>
>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
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus