Question about Excel DNA Interop.DAO

[Alvin Correa]

Hello Govert and the whole group. I did a practice of Excel DNA and the Excel DNA Interop DAO library. And it works but I have a problem with the StockPrice function when accessing the database in Access. My database has a coding field similar to that of the youtube video, with another date field and a field with the sales amount data whose value I want the function to show me but it gives me an error.

Here is the file and the code, anybody can help me?

Imports Microsoft.Office.Interop.Access.Dao
Imports ExcelDna.Integration
Imports System.IO

Public Module StockData

    Dim dbDemoData As Database
    Dim rsTimeSeries As Recordset

    Sub EnsureInitialized()
        If dbDemoData Is Nothing Then
            Dim dbpath As String = "D:\Practicas\Practicas\PracticaInteropDao1\DataEjemplo\DataPrueba.accdb"
            dbDemoData = New DBEngine().OpenDatabase(dbpath)
            rsTimeSeries = dbDemoData.OpenRecordset("PruebaData")
            rsTimeSeries.Index = "ClavePrincipal"
        End If
    End Sub

    ' This would not be needed in VBA - in .NET we must instantiate the root COM object
    Private MyDBEngine As New DBEngine()
    Private MyDatabase As Database

    Const MyDatabasePath As String = "D:\Practicas\Practicas\PracticaInteropDao1\DataEjemplo\DataPrueba.accdb"

    Private Sub EnsureDatabaseIsConnected()
        If MyDatabase Is Nothing Then

            ' We've not set the database yet - try to open or create a new one
            If File.Exists(MyDatabasePath) Then
                MyDatabase = MyDBEngine.OpenDatabase(MyDatabasePath)
            Else
                'Create New DB for testing - this could be OpenDatabase too
                MyDatabase = MyDBEngine.CreateDatabase(MyDatabasePath, Locale:=LanguageConstants.dbLangGeneral)

                'Add the tables Or give an error ....
                ' ...
            End If
        End If
    End Sub

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' These will be UDF functions you can call from a sheet...
    ' Check that the DAO is working correctly
    <ExcelFunction(Description:="Returns the version of the loaded DAO DBEngine")>
    Public Function DBEngineVersion() As Object
        Return MyDBEngine.Version
    End Function

    ' Check that the database was opened
    <ExcelFunction(Description:="Returns the name of the open database")>
    Public Function DatabaseName() As String
        EnsureDatabaseIsConnected()
        Return MyDatabase.Name
    End Function

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' This will be a macro added to the Add-Ins tab on the ribbon
    <ExcelCommand(MenuName:="Excel-DNA DAO Sample", MenuText:="Show Database Name")>
    Public Sub ShowDatabaseName()
        EnsureDatabaseIsConnected()
        MsgBox(MyDatabase.Name, Title:="DAO Sample")
    End Sub

    Function StockPrice(TheCode As String, TheDate As DateTime)
        EnsureInitialized()
        With rsTimeSeries
            .Seek("<=", TheCode, TheDate)
            If Not .NoMatch AndAlso .Fields!Code.Value = TheCode Then
                Return .Fields!Close.Value
            Else
                Return ExcelError.ExcelErrorNA
            End If
        End With
    End Function
End Module

Thanks a lot 

 201501_02.csv

 DataPrueba.accdb

 PruebaExcelAddInn.xlsx

Libre de virus. www.avast.com

[Govert van Drimmelen]

Hi Alvin,

The code seems to be a bit confused:

* You have two database variables that are initialized at different times.

* Your index name in EnsureInitialized does not match the index name of the table in the database

* The field names "Code" and "Close" that are used in the StockPrice function are not columns in the "PruebaData" table.

* To do the Recordset.Seek your table needs to have a structure and index that matches how you want to look things up.

I hope these pointers can help you make a bit of progress.

-Govert

[Alvin Correa]

Thanks a lot Govert, it's helped me very much. But i had to create an autonumber field and join it with the date field to create the index because access dont let me create an index with duplicates values. This is a sales file and you can find records with the same code and the same date... but de function StockPrice works with the third suggestion... Thank you very much. 

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/87d9b0be-55f7-4638-a49a-efd2a4fda9f4n%40googlegroups.com.