[AccessD] Table does not open to first record

Doesn't order by just sort the records. Would I not still want an index
on the table for speed of processing and searching? Why does access ask
for a sort order when creating an index if it is not used in data
retrieval? Help me understand. Thanks.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
Sent: Monday, August 22, 2005 11:43 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Table does not open to first record

 An index won't guarantee the order in which the rows are returned from 
a table unless it is applied.
otherwise the table will be just like a shuffled deck of cards.
To apply an index use OrderBy  and dbOpenDynaset rather than dbOpenTable
If you don't specify the second parameter in Openrecordset , by default 
Access looks at the table
and uses dbOpenTable, if a local Table or dbOpenDynaSet , if a query or 
linked backend table.

So you would want to apply a  dynamic query to establish indexed order 
like so.
Or you can create your own named query in place of strSQL.

dim strSQL as String
strSQL = "SELECT * FROM [tbl Produced HCPV% One Pattern] OrderBy 
MyDateFieldname",
debug.print strSQL ' just to check you got it right

Set myds = MyDb.OpenRecordset(strSQL, dbOpenDynaset)



Kaup, Chester wrote:

>I use the following code to open a table (myds) which is indexed on the
>date field. When I pull a record in code it gets record 127. I tried a
>movefirst command and the table still starts on record 127. Any ideas
>what is happening appreciated.
>
> 
>
>Dim MyDb As Database, myds As Recordset
>
>Set MyDb = CurrentDb()
>
>Set myds = MyDb.OpenRecordset("tbl Produced HCPV% One Pattern",
>dbOpenTable)
>
> 
>
>myds.MoveFirst
>
>myds1.AddNew
>
>    myds1.Fields(0) = myds.Fields(0)    'Date
>
>    myds1.Fields(1) = myds.Fields(1)    'Pattern name
>
>    myds1.Fields(2) = myds.Fields(2)    'Cum total injection
>
>    myds1.Fields(3) = myds.Fields(3)    'Cum based HCPV%
>
>    myds1.Fields(4) = myds.Fields(4)    'Derivative based HCPV%
>
>myds1.Update
>
> 
>
>Chester Kaup
>
>Engineering Technician
>
>Kinder Morgan CO2 Company, LLP
>
>Office (432) 688-3797
>
>FAX (432) 688-3799
>
> 
>
> 
>
>No trees were killed in the sending of this message. However a large
>number of electrons were terribly inconvenienced.
>
> 
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com