EXCEL - VBA-SQL-doubts in query using CAST

Hello, First of all I apologize to the ADM if there is any error in the formatting of the question, I am a novice here lol.

I am having a doubt in query search using MAX and cast

Objective: return the largest value in the order column by forcing it to be an integer, where in the LINPRD column is"A"

Expected Result: 5

Error occurring in line of code = RS.Open sql, DB

Follow my routine

Sub Pesquisa_SQL_CAST ()
 
Dim DB As New ADODB.Connection 'Variável de Conexão ou Caminho para o banco de dados
Dim RS As New ADODB.Recordset 'Variável para abrir conexão
Dim sql As String
Dim Final_loop As Integer

With DB
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Data Source") = ThisWorkbook.FullName
        .Properties("Extended Properties") = "Excel 12.0 Xml; HDR=YES; IMEX=1"
        .Mode = adModeRead
        .CursorLocation = adUseClient
        .Open
End With

sql = "SELECT MAX(ORDEM) FROM [Planilha3$A1:B6] WHERE [LINPRD] = 'A' AND CAST(ORDEM AS INTEGER) = '%'"

RS.Open sql, DB

Final_loop = RS(0)

End Sub

The table on the Plan3$A1: B6 is simply like this;

I also tried to use Cast like this;

sql = "SELECT MAX(CAST(ORDEM AS INTEGER)) FROM [Planilha3$A1:B6] WHERE [LINPRD] = 'A' AND CAST(ORDEM AS INTEGER) = '%'"

But it also results in error.

I'm just successful in the search, not using Cast, this way;

sql = "SELECT MAX(ORDEM) FROM [Planilha3$A1:B6] WHERE [LINPRD] = 'A'"

But I would like to use Cast, to force the search as integer, since, there is the possibility that some cell in the order column is typed as TEXT.

Taking advantage for 2 more questions rsrs.;

1st - how to reference a named formatted table or just a named region in from

2nd - cast and DISTINCT can even be used in SQL search by VBA, or should I use some corresponding respectively.

Obs.: "MICROSOFT ACTIVEX DATA OBJECTS 6.1 LIBRARY" is enabled in the "references-VBAProject"

Thank you very much, everyone who can help.

------------//------------------------//------------------------//------------------------//------------------------//------------

staff for those who have the same problem I managed to have a satisfactory solution;

Logic = I do a descending order by converting the order column into integer of the Cint command(order), and I also bring in integer

Follows;

sql = "SELECT CInt(ORDEM) FROM [Planilha3$A1:B6] WHERE [LINPRD] = 'A' ORDER BY CInt(ORDEM) DESC"

The routine was like this way;

Sub Pesquisa_SQL_CAST ()
 
Dim DB As New ADODB.Connection 'Variável de Conexão ou Caminho para o banco de dados
Dim RS As New ADODB.Recordset 'Variável para abrir conexão
Dim sql As String
Dim Final_loop As Integer

With DB
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Data Source") = ThisWorkbook.FullName
        .Properties("Extended Properties") = "Excel 12.0 Xml; HDR=YES; IMEX=1"
        .Mode = adModeRead
        .CursorLocation = adUseClient
        .Open
End With

sql = "SELECT CInt(ORDEM) FROM [Planilha3$A1:B6] WHERE [LINPRD] = 'A' ORDER BY CInt(ORDEM) DESC"

RS.Open sql, DB

Final_loop = RS(0)

End Sub

I couldn'T use CAST I had to adapt using CINT, (which can be CDBL or any other)

If anyone has a better or more practical solution, please share.

I still couldn't reference a named formatted table or just a named region in FROM, anyone who can help would be excellent.

Thank you very much.