CODEWITHSUNDEEP
sqlexcelvbscriptado
Yogesh
Yogesh

Reputation: 134

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

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

Upvotes: 0

Views: 2635

Answers (1)

Parfait
Parfait

Reputation: 107652

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.

Upvotes: 1

Related Questions