none
Unexpected behaviour with ADO querys on Excel (Complex question) RRS feed

  • Question

  • Hello

    I'm facing an unexpected error when querying different tables with OLEDB + ADODB on Excel VBA. Sorry if some functions/variables are in Spanish. Ive translated most of it.

    To stablish the connection, I've created a connection class, with a connect sub as resumed below.

    Option Explicit
    Dim cn As ADODB.Connection
    Dim rs As ADODB.recordset
    
    
    Private Sub Connect()
        Dim strFile  As String
        Dim strCon As String
        strFile = ThisWorkbook.FullName
        strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0 Macro;HDR=Yes"";"
        Set cn = CreateObject("ADODB.Connection")
        Set rs = CreateObject("ADODB.Recordset")
    
        cn.Open strCon`enter code here`
        [...]
    End Sub

    I also have a couple of methods to query the excel objects : First one to run the select query

    Public Function ExecSQL(Sql As String) As ADODB.recordset
        rs.Open Sql, cn, adOpenStatic
        Set ExecSQL = rs
    End Function

    and Second one to get table address (I convert ranges to tables for clarity, so I get the table adress to query). Sqls are like this: "Select fields from " + GetTableAddress(tableName)

    Public Function GetTableAddress(tableName as String) As String
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Dim Direccion As String
    Direccion = ""
    For Each oSh In ThisWorkbook.Worksheets
        For Each oLo In oSh.ListObjects
            If oLo.Name = tableName Then
                Direccion = Replace(oSh.ListObjects(tableName).Range.AddressLocal, "$", "")
                Direccion = oSh.Name + "$" + Direccion
            End If
        Next
    Next
    GetTableAddress = "[" + Direccion + "]"
    End Function


    But when I fill the tables, I do it from a database connection on Excel. So I have a database connection class that queries a SQL Server (In this scenario) :

    Main methods (also resumed as example) here:

    Public Sub Conecta(Servidor As String, Login As String, Pass As String, Libreria As String, ConSegura As Boolean)
        Dim rs As ADODB.recordset
        Dim Ini As Single 
    
        sError = ""
        Server = Servidor
        Set Conexion = New ADODB.Connection
        Conexion.ConnectionString = strMontarCad(Login, Pass, Server, Libreria, ConSegura)
        Conexion.CommandTimeout = 0 
        Conexion.ConnectionTimeout = 0 
        Ini = Timer 
        Conexion.Open
        ExecuteQuery "Set transaction isolation level read uncommitted"
        ExecuteQuery("Select GetDate()")(0) 
        Exit Sub
    End Sub

    So to run the queries on the database class I use the following methods Depending if I need the returning recordset...

    Public Function ExecuteQuery(Sql As String) As ADODB.recordset    
        sError = ""
        Set InnerRS = New ADODB.recordset
        InnerRS.CacheSize = 30
        InnerRS.Open Sql, Conexion, adOpenForwardOnly, adLockBatchOptimistic, adAsyncFetch
        Set ExecuteQuery = InnerRS
        Exit Function
    End Function


    And this second one when I have temp tables so I can get the final SQL as recordset. This function has the difference. As I have a separator. That splits the sqls so I can run create temporary tables, and some other sqls, and at the end query the result from the same connection. SQLs run. I get the data, but. When I query the tables later with OLEDB as above, it fails.

    Public Function ExecuteQueryLectura(Sql As String) As ADODB.recordset
        If InStr(Sql, QuerySeparator) > 0 Then
            Dim Trozo() As String
            Dim Parcial As Integer
            Trozo = Split(Sql, QuerySeparator)
            For Parcial = LBound(Trozo) To UBound(Trozo) - 1
                ExecuteSQL Trozo(Parcial)
            Next
            sError = ""
            Set InnerRS = New ADODB.recordset
            InnerRS.CacheSize = 30
            InnerRS.Open Trozo(UBound(Trozo)), Conexion, CursorTypeEnum.adOpenStatic, adLockReadOnly, adAsyncFetch
        Else
            sError = ""
            Set InnerRS = New ADODB.recordset
            InnerRS.CacheSize = 30
            InnerRS.Open Sql, Conexion, CursorTypeEnum.adOpenStatic, adLockReadOnly, adAsyncFetch
        End If
        ExecuteQueryLectura = InnerRS
        Exit Function
    End Function
    

    So when I need to fill a table, I do it this way:

    Private Function FillTabla(Pagina As Worksheet, Fila As Long, Columna As Long, Conexion As ClsConexionSQLServer, OBjSQL As ClsSQLs, PageName As String) As Long
        Dim rs As ADODB.recordset
        Dim LastCol As Long
        Dim cols As Long
        Set rs = Conexion.ExecuteQueryLectura(OBjSQL.TextoSQL)
        For cols = Columna To rs.Fields.Count + Columna - 1
            Pagina.Cells(Fila, cols).value = rs.Fields(cols - Columna).Name
        Next
        Pagina.Cells(Fila + 1, Columna).CopyFromRecordset rs.Clone
        DoEvents
        FormatAsTable Fila, Columna, Pagina, PageName & OBjSQL.Sigla
        LastCol = Columna + rs.Fields.Count
        DoEvents
        FillTabla = PosicionColumna
    End Function

    So I run several sqls against SQL Server, then I feed up Excel tables with the recordset. Excel that I read again with sql to fill more tables.

    The point here, is that it works fine when the SQL has no separator. I fill the table from SQL Server. And I can query the Excel table with a simple select. But when the sql has separator, and I fill the table from SQL Server with a previous run SQL (To create a temp table, as example) It doesn't. It cannot find the Table data. (And the sheet is filled with that data). Seems I missed something there, but cannot find out what.

    Error reads (Another example)

    The Microsoft Access database engine could not find the object 'Presup$A3:E83945'. Make sure the object exists and that you spell its name and the path name correctly. If 'Presup$A3:E83945' is not a local object, check your network connection or contact the server administrator

    Of course sheet exists and data is there. Visible. But somewhat it cannot find it. I've tried, Unlisting the Listobjects, opening and closing the excel connection... With no luck. There is something when splitting the SQL on SQL Server that prevents from loading correctly the data later.

    Any ideas?

    Anyway, thanks.

    Wednesday, February 26, 2020 1:58 PM