Charlotte Foust
cfoust at infostatsystems.com
Mon Sep 15 10:03:18 CDT 2003
Hen,
Something like SELECT * FROM MyTable WHERE 1=2 returns an empty
recordset immediately in my experience. I have used it for years to
force forms to load right away with no records. This is the same as
WHERE False, although I prefer the explicit syntax. Exactly what is it
that you feel is wrong with that approach? It certainly works and is
the only reliable method I've found for quickly loading a form with an
empty recordset.
Charlotte Foust
-----Original Message-----
From: Henry Simpson [mailto:hsimpson88 at hotmail.com]
Sent: Monday, September 15, 2003 6:03 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] listbox with rowsource type (ala MS)
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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com