Henry Simpson
hsimpson88 at hotmail.com
Mon Sep 15 09:03:05 CDT 2003
Gustav:
One of the things I was trying to say was that using a field list row source
does not submit the query SQL to the BE for retreival so there is no
difference whether or not a where clause is present. Another point is that
it is only necessary to add an impossible restraint to a single table if the
SQL is submitted because Jet should process that index first and finding
nothing to join on, disregard the other table indexes. I'm not satisfied
that using three 'Where Null' restraints wouldn't cause all three indexes to
be pulled before the FE started processing the joing.
My main point was that I have seen the 'Where False' construct advocated
several times in the past on this list and that had led me to believe that
someone here had at some time read or determined that the FE SQL parser had
enough intelligence to determine that it was unnecessary to submit the
request for data to a BE file when this condition was encountered.
Otherwise, how does it make sense? If there are records with 0 in some
field, will it return those? If there are yes/no fields with false
records, would it return those? Apparently not, so what does it actually do
with this magical condition? Let's just put an end to promoting 'Where
False' as a means to retreiving a limited recordset.
Hen
>From: Gustav Brock <gustav at cactus.dk>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>CC: Sad Der <accessd666 at yahoo.com>
>Subject: Re: [AccessD] listbox with rowsource type (ala MS)
>Date: Mon, 15 Sep 2003 09:17:46 +0200
>
>Hi Sander
>
>Henry is right (Subject: Where False, was listbox with rowsource
>type).
>I did a test but with small tables only.
>
>However, assuming each table has a normal autonumber primary key,
>modify the query like this:
>
> SELECT
> tblOne.*,
> tblTwo.*,
> tblThree.*
> FROM
> tblOne,
> tblTwo,
> tblThree
> WHERE
> tblOne.ID Is Null And
> tblTwo.ID Is Null And
> tblThree.ID Is Null;
>
>That should reveal the field names in a split second.
>
>/gustav
>
>
> > Hi Sander
>
> > Maybe I'm missing something, but why all this trouble to get a field
> > list?
>
> > 1. Create a select query like this:
>
> > SELECT
> > tblOne.*,
> > tblTwo.*,
> > tblThree.*
> > FROM
> > tblOne,
> > tblTwo,
> > tblThree
> > WHERE
> > False;
>
> > and save it as, say, qdyFieldList.
> > This will retrieve all fields from the tables but no records.
>
> > 2. In your form, adjust the combobox to have FieldList as
> > RowSourceType and specify qdyFieldList as the RowSource.
>
> > This should load in a fraction of a section.
>
> > /gustav
>
>
> >>>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
_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail