CODEWITHSUNDEEP
databasevbaexcelms-access
Gedeon
Gedeon

Reputation: 44

How to write an Excel function which returns a value from an ACCESS database?

I have a database in access 2013

Date | Data

01/06/2015 | 1

02/06/2015 | 2

And I want to write a function that returns values from DB. 

=GETDATA("FORDATE")

Is this possible ? I have tried this but it doesn't want to work 

Public Function GetData(id As Date) As String

Set oConnection = New ADODB.Connection
Dim oRecordset As ADOR.Recordset

oConnection.Open "Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\user\Desktop\CAINV_DB.accdb;" & "Trusted_Connection=yes;"
Set oRecordset = oConnection.Execute("select " & RDATE & " from CB_EXCHANGE where RDATE = " & id)
If oRecordset.EOF Then
    GetData = "n/a"
Else
    GetData = oRecordset(1)
End If

End Function

Upvotes: 1

Views: 949

Answers (2)

Dorian
Dorian

Reputation: 445

So you need to use this code (insert your database path):

Public Function getData(whatDate As Date) As Variant

Dim DB As Database
Dim RS As Recordset

Set DB = DBEngine.OpenDatabase("C:\temp\Desktop\Test.mdb")
Set RS = DB.OpenRecordset("SELECT USD FROM CB_EXCHANGE WHERE RDate = #" & Format(whatDate, "m\/d\/yyyy") & "#", dbOpenDynaset) ' The date format must be like this

If RS.RecordCount > 0 Then
    RS.MoveFirst
    getData = RS!USD ' of course you must enter the correct column name
End If

End Function

Upvotes: 2

Dorian
Dorian

Reputation: 445

first you Need to reference the "Microsoft DAO 3.6 Object Library". I don't know how the menu is named in english VBA Editors. In the german Version it's at "Extras > Verweise..."

Then you Need code like this:

Public Function getData(Data as Integer) As Variant
Dim DB As Database
Dim RS As Recordset

Set DB = DBEngine.OpenDatabase("C:\yourPath\yourDatabase.accdb")
Set RS = DB.OpenRecordset("SELECT foo FROM bar WHERE Data = " & Data, dbOpenDynaset)

If RS.RecordCount > 0 Then
    RS.MoveFirst
    getData = RS!Date
End If

End Function

edit: Sorry, i didn't see you need it in the opposite direction (input a date and get the data) so the function has to be like this:

Public Function getData(whatDate as Date) As Variant
Dim DB As Database
Dim RS As Recordset

Set DB = DBEngine.OpenDatabase("C:\yourPath\yourDatabase.accdb")
Set RS = DB.OpenRecordset("SELECT Data FROM CB_EXCHANGE WHERE Date = #" & Format(Date, "m\/d\/yyyy") & "#", dbOpenDynaset)

If RS.RecordCount > 0 Then
    RS.MoveFirst
    getData = RS!Data
End If

End Function

Upvotes: 1

Related Questions