Search code examples
excelvbams-access

Using SELECT to retrieve data from Access Database

I have a "SELECT" query in Excel using VBA.

CommodityInfo = ObjAccess.CurrentProject.Connection.Execute( _
"SELECT TOP 1 Commodity FROM [" & CustomerName & "]")

It works. I'm struggling with using the information in the CommodityInfo variable. I can see in my Locals window that the information is there. How do I use it to output the value?
Item 1

Item 1 is clearly displayed so its being accessed. How do I pull out the value from item 1 and use it as I would a string?
enter image description here

Count can be used by stating CommodityInfo.Count.
I tried ComVal = CommodityInfo(1).Value but I get the error

Item cannot be found in the collection corresponding to the requested name or ordinal.

Solution

  • As noted in comments, your code is opening a recordset object, not pulling a single value.

    Since field index is zero based, use 0 for first field instead of 1: ComVal = CommodityInfo(0) to pull single field value from recordset to simple variable.

    Or append Execute command with field index reference to extract single field value directly to simple variable.

    CommodityInfo = ObjAccess.CurrentProject.Connection.Execute( _
               "SELECT TOP 1 Commodity FROM [" & CustomerName & "]")(0)

    Here is alternative to pull a single value instead of opening a recordset:
    CommodityInfo = ObjAccess.DLookup("Commodity", "[" & CustomerName & "]").
    In my test, it ran faster.

    It is possible to pull data from Access without instantiating an Access application object variable. I found this code ran fastest.

    Dim cn As New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='your access database filepath'"
CommodityInfo = cn.Execute("SELECT TOP 1 Commodity FROM [" & CustomerName & "]")(0)

    Or

    Dim cn As New ADODB.Connection
cn.Open "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName & ";HDR=Yes';"
CommodityInfo = cn.Execute("SELECT TOP 1 Commodity FROM [" & CustomerName & "]" IN 'your database filepath'")(0)