How to set controlling index before doing SEEK in Access Basic 
Author Message
 How to set controlling index before doing SEEK in Access Basic

How do we set the controlling index before doing a seek in Access
Basic?  Every example I can find shows creating a new index . . . and
our indexes are in linked databases.  Besides, I dont want to create a
new index, I want to use an existing index.

The help on TableDef Object says you can Examine the field and index
structure of any local, linked, or external table in a database.  The
help on Index Object says When accessing a table-type Recordset
object, you specify the order of records using the objects Index
property.  Set this property to the Name property seting of an existing
Index object in the Indexes collection.

The help onIndex Property says Setting the Index property changes the
order of records returned from the database. . . and Examine the
Indexes collection of a TableDef object to determine what Index objects
are available to table-type Recordset objects created from that TableDef
object.

I did these things.  The indexes collection for our Invoices table shows
[item 1], [item 2], and [item 3].
The name property of [item 1] is Order

We tried using some code which is very similar to the MS Access example,
and we get a message saying
Operation is not supported for this type of object.

rst.Index = "Order"

Why?  And why are there no examples of  selecting an existing index as
opposed to creating a new one?

This makes no sense.  It should not be a major ordeal to select which
index order your data will be referenced by.  It seems to me that this
would be a common question.  Surely this is something nearly every
Access programmer runs into . . . Any suggestions?



Sun, 04 Mar 2001 03:00:00 GMT  
 How to set controlling index before doing SEEK in Access Basic
Hi Katherine,
I would guess because you are using linked tables, if you are creating a
recordset object from a linked table you will get a dynaset not a tabletype
recordset, you can't use indexes with a dynaset recordset.

You can open a table type recordset from a linked table but you have to open
it from the database it resides in.

The following code will give a tabletype recordset from a linked table.

Function OpenTableRst(TableName As String) As Recordset
  Dim strConnect As String
  Dim strName As String
  Dim loDb As Database
  Dim loTab As TableDef
  Dim lorst As Recordset
  Set loDb = CurrentDb
  Set loTab = loDb.TableDefs(TableName)
  strConnect = loTab.Connect
  strName = loTab.SourceTableName
  Set loTab = Nothing
  If Len(strConnect) > 0 Then
    strConnect = Mid(strConnect, InStr(strConnect, "=") + 1)
    Set loDb = OpenDatabase(strConnect)
    Set lorst = loDb.OpenRecordset(strName)
  End If
  Set OpenTableRst = lorst
End Function

'This tests the above code
Function TestOT_RST()
  Dim lorst As Recordset
  Set lorst = OpenTableRst("Table1")
  MsgBox (lorst.Type = dbOpenTable)
End Function

Quote:

>How do we set the controlling index before doing a seek in Access
>Basic?  Every example I can find shows creating a new index . . . and
>our indexes are in linked databases.  Besides, I dont want to create a
>new index, I want to use an existing index.

>The help on TableDef Object says you can Examine the field and index
>structure of any local, linked, or external table in a database.  The
>help on Index Object says When accessing a table-type Recordset
>object, you specify the order of records using the objects Index
>property.  Set this property to the Name property seting of an existing
>Index object in the Indexes collection.

>The help onIndex Property says Setting the Index property changes the
>order of records returned from the database. . . and Examine the
>Indexes collection of a TableDef object to determine what Index objects
>are available to table-type Recordset objects created from that TableDef
>object.

>I did these things.  The indexes collection for our Invoices table shows
>[item 1], [item 2], and [item 3].
>The name property of [item 1] is Order

>We tried using some code which is very similar to the MS Access example,
>and we get a message saying
>Operation is not supported for this type of object.

>rst.Index = "Order"

>Why?  And why are there no examples of  selecting an existing index as
>opposed to creating a new one?

>This makes no sense.  It should not be a major ordeal to select which
>index order your data will be referenced by.  It seems to me that this
>would be a common question.  Surely this is something nearly every
>Access programmer runs into . . . Any suggestions?



Mon, 05 Mar 2001 03:00:00 GMT  
 How to set controlling index before doing SEEK in Access Basic
How do we set the controlling index before doing a seek in Access
Basic?  Every example I can find shows creating a new index . . . and
our indexes are in linked databases.  Besides, I dont want to create a
new index, I want to use an existing index.

The help on TableDef Object says you can Examine the field and index
structure of any local, linked, or external table in a database.  The
help on Index Object says When accessing a table-type Recordset
object, you specify the order of records using the objects Index
property.  Set this property to the Name property seting of an existing
Index object in the Indexes collection.

The help onIndex Property says Setting the Index property changes the
order of records returned from the database. . . and Examine the
Indexes collection of a TableDef object to determine what Index objects
are available to table-type Recordset objects created from that TableDef
object.

I did these things.  The indexes collection for our Invoices table shows
[item 1], [item 2], and [item 3].
The name property of [item 1] is Order

We tried using some code which is very similar to the MS Access example,
and we get a message saying
Operation is not supported for this type of object.

rst.Index = "Order"

Why?  And why are there no examples of  selecting an existing index as
opposed to creating a new one?

This makes no sense.  It should not be a major ordeal to select which
index order your data will be referenced by.  It seems to me that this
would be a common question.  Surely this is something nearly every
Access programmer runs into . . . Any suggestions?



Tue, 06 Mar 2001 03:00:00 GMT  
 How to set controlling index before doing SEEK in Access Basic

Quote:

>How do we set the controlling index before doing a seek in Access
>Basic?  Every example I can find shows creating a new index . . . and
>our indexes are in linked databases.  Besides, I dont want to create a
>new index, I want to use an existing index.

<big snip>

The last two words of the second sentence are your problem, linked
databases.  Seek and linked tables does not work, you will have to use
FindFirst or something similar.

Adrian



Tue, 06 Mar 2001 03:00:00 GMT  
 How to set controlling index before doing SEEK in Access Basic
In Katherine's case this doesn't work because the table is a linked table (and so is the index).
When you change
        Set myDB = CurrentDB()
into
        Set myDB = OpenDatabase("LinkedDB")
then it works!



Quote:


> >How do we set the controlling index before doing a seek in Access
> >Basic?

> Sub SeekThis(TableName, IndexToUse, WhatToFind)
> Dim myDB As Database
> Dim myRst As Recordset
> Set myDB = CurrentDb()
> Set myRst = myDB.OpenRecordset(TableName, dbOpenTable)

> With myRst
>   .Index = IndexToUse
>   .Seek "=", WhatToFind
>   .Close
> End With
> myDB.Close



Wed, 07 Mar 2001 03:00:00 GMT  
 How to set controlling index before doing SEEK in Access Basic
Adrian, you can make it work, but you can't use CurrentDb. Instead you have to set a database objectvariable to
the linked database:

        Set myDB = OpenDatabase("LinkedDB")


Quote:


> >How do we set the controlling index before doing a seek in Access
> >Basic?  Every example I can find shows creating a new index . . . and
> >our indexes are in linked databases.  Besides, I don't want to create a
> >new index, I want to use an existing index.

> <big snip>

> The last two words of the second sentence are your problem, linked
> databases.  Seek and linked tables does not work, you will have to use
> FindFirst or something similar.

> Adrian



Wed, 07 Mar 2001 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Doing SEEK with partial index

2. Why I can't use index and seek in all Access database

3. how to set up control's tabindex,and control array's index

4. Problem with setting INDEX on FoxPro table in Access 97

5. Finding/Seeking/setting the current record when using a data control

6. Quick way to set Control Array indexes?

7. Setting tab index for controls on SSTab

8. Quick way to set Control Array indexes?

9. Using access indexes in visual basic.

10. Access/Visual Basic Indexes

11. Visual Basic Data Control and Changing DB Indexes

12. using .index and .seek function

 

 
Powered by phpBB® Forum Software