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

Question

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).

For speed, it's all relative. Does it take 10 secs, 30 secs, 1 min? And how is your connection set up? Does query run as long in Access? What are its size, indices, structure? Also, Date is a reserved Access word, consider escaping with brackets: [Date]. — Parfait
– Parfait, Commented Feb 11, 2016 at 13:49

Parfait · Accepted Answer · 2016-02-11 22:04:08Z

1

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

edited Feb 11, 2016 at 22:04

answered Feb 11, 2016 at 13:45

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nelus Over a year ago

AccessRS.RecordCount returns a value of '-1'. That means ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Any way to get around this? msdn.microsoft.com/en-us/library/ms676701(v=vs.85).aspx

Parfait Over a year ago

Whoops, missed that you are running ADO execute instead of ADO open recordset method. The former's recordset is slightly limited being a read-only, forward-only cursor. I updated for the latter which has RecordCount available.

Nelus Over a year ago

Works perfectly! Thanks again. I just had to add the CN to the RS.Open string: AccessRS.Open strSql, AccessCN

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related