none
How to use index in an Access DataBase with VB.NET RRS feed

  • Question

  •  

    Hi all,

    I have a database Access(MyDataBase.MDB) with 1 table(MyTable) and 2 fields

    Code type Text and Name type Text.

    I have 1 Index "Code" linked to the field "Code" and 1 index "Name" to the field "Name".

    I want to change index to access records ordered by "Code" or "Name" but in spite of all my possible piece of research I don’t found anything about. It seems that indexes on table are no more used.

    I wait myself that if I choose .index="Code" the database displays the first record ordered by Code, otherwise if I choose .index="Name" the database displays the first record in alphabetical order.

    There is somebody that may help me?

    Thank you in advance.

    John

     

     

    There it is my code (example)

    +++ Begin Code

    Imports System.Data

    Imports System.Data.OleDb

    Public Class Form1

    Dim cN As ADODB.Connection, rS As ADODB.Recordset

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim strCn As String = "C:\MyDataBase.mdb"

    cN = New ADODB.Connection

    cN.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strCn & ";Persist Security Info=False;")

    rS = New ADODB.Recordset

    With rS

    .ActiveConnection = cN

    .CursorLocation = ADODB.CursorLocationEnum.adUseServer

    .CursorType = ADODB.CursorTypeEnum.adOpenKeyset

    .LockType = ADODB.LockTypeEnum.adLockPessimistic

    .Index = "NOME" ‘ or .Index = "CODE"

    .Open("MyTable", cN, , , )

    .MoveFirst()

    MsgBox(rS("NOME").Value)

    End With

    rS.Close()

    cN.Close()

    End

    End Sub

    End Class

    +++ End Code

     

     

    Friday, October 5, 2007 10:24 PM

Answers

  • Hi,

     

    It has been a while since I used ADO but thought I could help....

     

    Think what is happening is with the index you control how records are accessed not how they are physically ordered. So although the index has been set correctly the MoveFirst() method still parses the records in the original order. I have no proof that this is the case but the information I'm reading suggests this is true.

     

    The basic idea behind the Index property is that you can use a table's index to control the order in which records in a table are displayed. You can then use the Recordset's Seek method to search for a record based on its index value or values.  ** basically I think it allows you to search records in order **

     

    Just wondering if perhaps the .Sort property is a better option, or even using a SELECT FROM.... ORDER BY statement to open the Recordset in a pre-sorted order.

    Monday, October 8, 2007 3:59 PM

All replies

  • Hi,

     

    It has been a while since I used ADO but thought I could help....

     

    Think what is happening is with the index you control how records are accessed not how they are physically ordered. So although the index has been set correctly the MoveFirst() method still parses the records in the original order. I have no proof that this is the case but the information I'm reading suggests this is true.

     

    The basic idea behind the Index property is that you can use a table's index to control the order in which records in a table are displayed. You can then use the Recordset's Seek method to search for a record based on its index value or values.  ** basically I think it allows you to search records in order **

     

    Just wondering if perhaps the .Sort property is a better option, or even using a SELECT FROM.... ORDER BY statement to open the Recordset in a pre-sorted order.

    Monday, October 8, 2007 3:59 PM
  • Yes, the index is only used in conjunction with seek.

     

    Better bet is to use an order by clause with Access and let the Jet database engine sort the records which will be more efficient.

     

    You can choose the ordering like below (4 examples of permutations)

     

    select * from Table1 order by Nome, Code

    select * from Table1 order by Nome

    select * from Table1 order by Code

    select * from Table1 order by Code, Nome

     

     

     

    Monday, October 8, 2007 5:02 PM
  • Hi All,

    Thanks for answers but my problem is:

    I must read only one record a time and, for security I open a connection for the limited time that needs to read data so

    I open a connection

    I read data

    I close a connection

    After that how can I move to next or previous record ?

    If I choose "Button NEXT" I must move to the next record following the declared index (code or alphabetic).

    How can I do that?

    In the old DAO I used MoveNext or MovePrevious etc. but now with ADO.NET it seems that those methods are no longer used, or, better I’m not able to use.

    I examined a lot of examples and the old Recordset now is changed with a DataSet that seems like a super Recordset but I think that it’s not worth the effort for reading only a single record.

    I’ll be very grateful if some guru (as You) can advise me where can I find a VB.NET example about my problem to scroll records in an Access DataBase.

    Many thank to all.

    John

    Wednesday, October 10, 2007 10:50 AM
  • I'm not sure why you need to read only one row at a time and then close the connection but there should be a way to perform this operation each time to fetch the next row. What we would need to know is what column or columns (perhaps a primary key?) determines the order of the data in the table?

     

    Wednesday, October 10, 2007 12:56 PM
  • I think you want to do the classic ASP.NET paging techique of:

     

    select top 1 * from table where keyField(s) > previouskeyFieldValue(s) order by keyField(s)

     

    The first fetch you say:

     

    select top 1 * from table order by keyField(s)

     

    Then save value for key(s).

     

    Then send and subsequent fetches say:

     

    select top 1 * from table where keyField(s) > previouskeyFieldValue(s) order by keyField(s)

     

    NOW the tricky part, does MSAccess support TOP clause?  I tested Access 2007 and it does.

     

    Now to write the code example!

    Thursday, October 11, 2007 9:46 PM