Index Required When Querying Table Type Recordset

[dw85745]

Hope this helps someone else as took quite a while to troubleshoot.

--------------------------------

RE: Microsoft Access
Table Query where Table includes a PrimaryKey

Per Microsoft:

If recordset refers to a table-type Recordset (Microsoft Jet workspaces
only), movement follows the current index. You can set the current
index by using the Index property. If you don't set the current index,
the order of returned records is undefined

When querying a table type recordset (MS Jet) which includes a PrimaryKey (Index) the following code will eventually fail under certain conditions as Access looses the pointer to the Index.

With rsSource
   
      .MoveFirst
 
      'Get All Daily Records
      Do Until .EOF
 
           'do something
      Loop
 
   End With

------------------------------------------------------

By referencing the Table Index PRIOR to using the table you are assured
MS Access knows what Index is being used.

With rsSource
   
      .Index = "PrimaryKey"        '<<<CRITICAL
      .MoveFirst
 
      'Get All Daily Records
      Do Until .EOF
 
           'do something
      Loop
 
   End With

[Hack]

Thanks for the research and tip!