CODEWITHSUNDEEP
excelnullvba
Nelus
Nelus

Reputation: 115

Query MS Access DB from Excel VBA - How to return 0 if no value found?

I'm querying an MS Access database from within excel VBA. I need it to return 0 in excel if no record exists in the DB. This is my code:

Function CheckIfDateExistInDB(MyDate As Date) As Date
Dim strSql As String

strSql = "SELECT Date FROM AccessDB WHERE Date = #" & MyDate & "#"

Set AccessRS = AccessCN.Execute(strSql)
CheckIfDateExistInDB = AccessRS.Fields(0)
Set AccessRS = Nothing
End Function

Please help - 1) This code returns "#VALUE!" in excel if the record does not exist in the DB. 2) If the record does not exist, this query takes a LONG time to run. 3) I need the code to return 0 if no record exists (and speed it up).

Upvotes: 0

Views: 1141

Answers (1)

Parfait
Parfait

Reputation: 107642

Consider conditionally wrapping recordset by recordcount property during field retrieval:

Dim AccessCN As Object, AccessRS As Object

Set AccessCN = CreateObject("ADODB.Connection")
Set AccessRS = CreateObject("ADODB.Recordset")

AccessCN.Open "DRIVER=Microsoft Access Driver (*.mdb, *.accdb);" _
                & "DBQ=C:\Path\To\Database\File.accdb;"
AccessRS.Open "SELECT Date FROM AccessDB WHERE Date = #" & MyDate & "#", AccessCN

If AccessRS.RecordCount > 0 Then
     CheckIfDateExistInDB = AccessRS.Fields(0)
Else
     CheckIfDateExistInDB = 0
End If

AccessRS.Close

Set AccessRS = Nothing
Set AccessCN = Nothing

Upvotes: 1

Related Questions