VBA Using SELECT to retrieve data from Access Database-CodePudding

I have issues with a simple "SELECT" query that is coded as the following in Excel using VBA -

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

It works fine. But then what I'm struggling with is using the information provided to me within the CommodityInfo variable. I can see in my Locals window that the information is there to be accessed I just wouldn't know how to use it to output the value.

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

Count can be used by simply stating CommodityInfo.Count, I tried doing something like ComVal = CommodityInfo(1).Value but I get the error 'Item cannot be found in the collection corresponding to the requested name or ordinal'.

Any help would be appreciated.

CodePudding user response：

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

Or append Execute command with field index reference.

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)

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)