VBScript Excel ADO connection: Get value using column alias in SQL query

Question

In following code, I can get the count from excel using

objTempRecordset.Fields.Item(0).Value

However, I want to use column name alias in SQL. i.e.

sSQL = "Select Count(*) AS RecCount FROM [NELimits$] A WHERE A.Type = 'A' AND A.ID = " &Chr(39) & "R001" & Chr(39)

and I want to get the result using:

objTempRecordset.Fields.Item("RecCount").Value

I also tried objTempRecordset.Fields.Item("_Count(*)_").Value but no luck

Can someone please let me know how to use column name alias in this case?

Note: Excel has 2 columns ID: with values such as "R001", "R002" Type: with values such as "A","B","C"

Sample code:

sSQL = "Select Count(*) FROM [NELimits$] A WHERE A.Type = 'A' AND A.ID = " &Chr(39) & "R003" & Chr(39)
Sqlquery = sSQL 
sFilePath = "C:\Temp\DataSheet.xlsx"

Dim objTempConnection : Set objTempConnection = CreateObject("ADODB.Connection")
Dim objTempRecordSet : Set objTempRecordSet = CreateObject("ADODB.Recordset")

Dim strPath
'Define constants for objTempRecordset
Const adOpenStatic=3
Const adLockOptimistic=3
Const adLockPessimistic=2
Const adCmdText = &H001

'Open connection 
objTempConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="& sFilePath &";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

objTempRecordset.ActiveConnection = objTempConnection
objTempRecordset.CursorType = adOpenStatic
objTempRecordset.LockType = adLockOptimistic 
objTempRecordset.Open Sqlquery 

If objTempRecordset.EOF Or objTempRecordset.BOF Then
    msgbox "no record"
End If

msgbox "Record Count: "&objTempRecordset.RecordCount

msgbox "Value:" & objTempRecordset.Fields.Item(0).Value

Just noticed that objTempRecordset.Fields.Item(0).Name returns Expr1000 and objTempRecordset.Fields.Item("Expr1000").Value Returns correct value However, I'm not sure what Expr1000 means and how it is assigned. Not sure if it will change for different excel sheets, data, etc. hence need a way to define alias for columns so that I can retrieve data based on alias — Yogesh
– Yogesh, Commented Dec 5, 2016 at 3:00
Resolved: ..."SELECT column_name As [alias_name] FROM..." in SQL solved the issue. Thanks to this post: stackoverflow.com/questions/37466225/… — Yogesh
– Yogesh, Commented Dec 5, 2016 at 3:14
Are you sure objTempRecordset.Fields.Item("RecCount").Value didn't work as it should have if you used the first SQL query (not second one inside code block)? In first, you assigned a column alias RecCount but not in second which the ACE SQL engine assigns as the generic Expr1000. — Parfait
– Parfait, Commented Dec 5, 2016 at 3:21
That is right... objTempRecordset.Fields.Item("RecCount").Value did not work because I had not used "Select Count() As [ RowCount ] FROM..." I had only used "Select Count() As RowCount FROM..." in the SQL query i.e. Alias should be mentioned between two square brackets in order to use it. — Yogesh
– Yogesh, Commented Dec 5, 2016 at 4:46

Parfait · Accepted Answer · 2016-12-06 01:00:57Z

With the ACE SQL engine (used here in querying workbook), original field names, column aliases, or table names with spaces, special characters (non-alphanumeric), or reserved words need to be wrapped in square brackets or backticks to properly escape them.

Spaces

sSQL = "Select Count(*) AS [Rec Count] FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"
sSQL = "Select Count(*) AS `Rec Count` FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"

Special Characters (e.g., hyphen and pound/hashtag sign)

sSQL = "Select Count(*) AS [Rec-Count] FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"
sSQL = "Select Count(*) AS `Rec-Count` FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"

sSQL = "Select Count(*) AS [Rec#] FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"
sSQL = "Select Count(*) AS `Rec#` FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"

Reserved words (e.g., Count)

sSQL = "Select Count(*) AS [Count] FROM [NELimits$] A" _
           & "  WHERE A.Type = 'A' AND A.ID = 'R003'"
sSQL = "Select Count(*) AS `Count` FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"

Otherwise, any field name or column alias is legitimate in query and can be read in record set in following formats:

objTempRecordset.Fields.Item(0).Value               ' BY INDEX IN ITEM '
objTempRecordset.Fields.Item("Rec Count").Value     ' BY NAME IN ITEM '
objTempRecordset.Fields("Rec Count").Value          ' BY NAME IN FIELD COLLECTION '
objTempRecordset![Rec Count].Value                  ' BY NAME (EXCLAMATION POINT QUALIFIER) '

Furthermore, missing column aliases are handled in a special manner with ACE:

Missing Alias on Query Expression (e.g., Count function aggregation)

sSQL = "Select Count(*) FROM [NELimits$] A" _ 
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"

Missing Alias on Duplicate Field

sSQL = "Select ID, ID FROM [NELimits$] A" _
           & " WHERE A.Type = 'A' AND A.ID = 'R003'"

For above two missing aliases, the ACE engine creates a column alias usually starting at Expr1 (inside MS Access -the usual interface to the ACE Engine) or Expr1000 for ODBC connections and incrementing for all other unnamed expressions or unnamed duplicate field references.

Collectives™ on Stack Overflow

VBScript Excel ADO connection: Get value using column alias in SQL query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related