[RESOLVED] DAO usage explanation please

[kebo]

Hey all,
I live in the .net world and have a job to convert some VB6 code that uses DAO databases and recordsets.

This is the code...

Set ds2 = db2.OpenRecordset("Schedule")
ds2.Index = "KeyDateEmp"
ds2.Seek ">=", xDate

db2 is an established connection to an Access 95 format database at c:\pathToAccess\myDB.mdf.

The first line I get, but I don't get the index and seek because I don't have a column in the Schedule table named KeyDateEmp. I assumed the index and seek work together to set up the equivalent of an SQL command.

Can some explain how the Index and seek work on and open recordset?

[Eduardo-]

Seek is not to open the recordset, you open it in the Set ds2 = db2.OpenRecordset("Schedule") line.

Seek is used to search for a value and position the recordset in that record.

The recordset must be a Table type one. As you have opened it OpenRecordset("Schedule") it is table type.
You cannot have .OpenRecordset ("SELECT... and then to use Seek.

Seek is similar to FindFirst, but a lot faster because it searchs by an index.

The index must be defined in advance in the database.

Prior to use Seek, you need to have an active index set in the recordset, that is what the line ds2.Index = "KeyDateEmp" does.

In that case "KeyDateEmp" is the index name.
The indexes are defined in the Access database at design time.

After .Seek, you could want to know if the record was found or not, for that you use the .NoMatch property.

Edit: to view (or even change) the indexes in the database, from the screenshot that you posted you have to go to the menu View, and then select Indexes.

[dilettante]

The Index property is set to an index name. An index spans one or more columns of the table, and values to be tested for come following the comparison type passed to the Seek method (a parameter array).

This is similar to Index and Seek for an ADO Recordset.

An index is a separately named item for a given table. Often people use a prefix like "idx" or "pk" when naming them, but just as often they don't.

[IkkeEnGij]

i assume you want the sql that is more or less equivalent to seeking on an index in dao
so:

SELECT * FROM YourTable WHERE YourField >=TheCondition

from the picture i assume that the name of YourTable = 'Schedule'
and to know what field is used by the index 'KeyDateEmp', click on the lightning symbol of the toolbar
from the picture i assume that index 'KeyDateEmp' is an index on the 'sDate' field of the 'Schedule' table
so the sql would become:
SELECT * FROM Schedule WHERE sDate >=#xDate#

note that this is not exactly the same as seeking on in index in a table
to seek on an index in dao ,the recordset has to be a table-type recordset
and if succesfull, the record-pointer is then on the first record that satifies the condition
(in this case where the date is later or the same as xDate)

also note that DAO can still be used in vb.net

[IkkeEnGij]

@ dilettante
i was clearly thinking and writing while you were doing the same

[kebo]

thank you both. I never did much with record set so to me this a bit new as old as it is.

[Elroy]

Wow, I wish I'd seen this one earlier. I use the DAO everyday. Cobbled together, it seems that the prior posts have done a good job of explaining it.

Just to say again, yes, a table can have indexes created for it. Your line...

ds2.Index = "KeyDateEmp"

...is setting the index to be used for a particular open recordset (of table type). You can potentially have many indexes for any particular table. However, things like .Seek, .MoveFirst, .MoveNext, .MoveLast will be working with the currently set index. That's how you can have different sort orders to a single table.

Also, as has been said, any particular index can be "indexed" on one or more fields. And the index name typically is not the same as a field name (although it could be).

And yes, after a .Seek, you'd pretty much always check the boolean .NoMatch for the table. Also, don't forget about the .BOF and .EOF booleans when using .MoveNext and .MovePrevious.

Personally, I love the simplicity of all of this. And, also as stated above, it's all much faster than .FindFirst with a Dynaset type recordset.

Also, you can create the indexes with VB6. In fact, you can build a complete MDB database from scratch using the DAO from VB6.

In fact, here's a procedure I use to add indexes to an existing table. This is specific to my needs, and does assume that the dbTheDatabase is already open as exclusive. I also included a couple other functions just because they were handy:

Private Sub DbAddIndex(sTableName As String, sIndexName As String, sFieldNames() As String, _
                      Optional bPrimary As Boolean = False, _
                      Optional bUnique As Boolean = False)
    ' Make sure database is open, and EXCLUSIVE access is a good idea.
    ' The sFieldNames() must exist, or error.
    Dim idf As DAO.Index
    Dim tdf As DAO.TableDef
    Dim fdf As DAO.Field
    Dim i As Long
    '
    Set tdf = dbTheDatabase.TableDefs(sTableName)
    Set idf = tdf.CreateIndex(sIndexName)
    '
    idf.Primary = bPrimary
    idf.Unique = bUnique
    '
    For i = LBound(sFieldNames) To UBound(sFieldNames)
        Set fdf = idf.CreateField(sFieldNames(i))
        idf.Fields.Append fdf
    Next i
    '
    tdf.Indexes.Append idf
    tdf.Indexes.Refresh
End Sub

Private Sub DbDeleteIndex(sTableName As String, sIndexName As String)
    ' Make sure database is open, and EXCLUSIVE access is a good idea.
    Dim tdf As DAO.TableDef
    '
    Set tdf = dbTheDatabase.TableDefs(sTableName)
    tdf.Indexes.Delete sIndexName
End Sub


Private Function DbIndexExists(sTableName As String, sIndexName As String) As Boolean
    ' The table should exist.  If it doesn't, it returns FALSE.
    Dim s As String
    '
    On Error Resume Next
        s = dbTheDatabase.TableDefs(sTableName).Indexes(sIndexName).Name
        DbIndexExists = (Err = 0)
    On Error GoTo 0
End Function

Good Luck,
Elroy

[Eduardo-]

Don't get too excited, the OP is trying to understand the code just to convert it to .Net.