0

My ultimate goal is to run sql queries against sql-server and capture the returned data in a spreadsheet. The following code roughly reflects my current set-up and it works. The design allows me to read sql codes from text files and submit it to a sql-server. "Sub ExecuteCRUD" submits a first sql script to prepare data and dumps the result into a temp table. "Function loadRecordset" submits a relatively simple select query and captures the returned data in a recordset, which I then use to populate a spreadsheet.

There are a couple "variables" in my setup that could potentially be relevant for discussion.

  1. My 4 set of Sql Codes

  2. The ConnectionString (Part of my vba code)

  3. The rest of my vba codes

    a. Dbo.ConnectionString = "Provider=MSDASQL;DRIVER=SQL Server;SERVER=myserver;UID=id;PWD=password;DATABASE=database;"

    b. Dbo.ConnectionString = "Provider=SQLOLEDB;Data Source=myserver;Initial Catalog=database;User ID=id;Password=password;"

Initially, all I changed was the connection string. The immediate result was connection string version a works perfectly. Using b version, my setup would fail without any errors from sql-server.

Using Connection string version b, "Sub ExecuteCRUD" (data preparation step) would still work smoothly. I can verify that the temp table is created in tempdb as a result of my first sql script.

"Function loadRecordset" would run through the lines without any errors up to and including "rs.open". (I checked for errors, none whatsoever from the ado connection).

Only subsequent codes, when using the recordset to copy out the data would get an error: "Operation is not allowed when the object is closed."

Through some testing, I narrowed down the issue to the sql codes, sort of.

I have to reiterate here. My initial set of sql codes worked completely fine when using the ODBC provider. Things only went sideways using the OLEDB provider.

Using the OLEDB provider, the "offending" sql code was Use databaseABC. Furthermore, using ado, my setup submits 4 sets of sql codes to the sql server. The first set of sql codes prepares data (creating tables, inserting data, creating index, using while loops to populate data, using recursive ctes, etc). In this first set of sql codes, Use databaseABC was also included, and it would execute successfully. The other 3 set of sql codes submitted were only select queries aimed at obtaining data. When Use databaseABC was included in the select query sql codes, the operation failed without any errors. After I took out the Use databaseABC, everything would run correctly in the OLEDB provider world.

Use databaseABC is not a necessary part of the select queries. Using it saves me the trouble of specifying database names all the time in the join clauses.

At this point, my curiosity is two fold

  1. why Use databaseABC causes failures, specifically only when using OLEDB provider, more specifically only when running select queries.
  2. When the failure occurred, should sql-server or the driver generate any errors? What would be the proper way of checking and verifying that?
Private Dbo As New ADODB.Connection
Private rs As ADODB.Recordset
        
Public Sub ConnectServer()
        If Dbo.State = adStateClosed Then
            Dbo.ConnectionString = "Provider=SQLOLEDB;Data Source=*server*;" _
            & "Initial Catalog=*database*;User ID=*id*;Password=*pwd*;"

            Dbo.Open
        End If
End Sub      

Public Sub ExecuteCRUD(ByVal Sql As String)
        On Error GoTo PANIC
        Dbo.Execute Sql
        Exit Sub
        
PANIC:
        Debug.Print Err.Descript
        Stop
End Sub

Public Function loadRecordset(ByVal Sql As String) As Long
        On Error GoTo PANIC
        Set rs = New ADODB.Recordset
        With rs
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
            .Source = Sql
            Set .ActiveConnection = Dbo
        End With
        rs.Open
        
        loadRecordset = rs.RecordCount
        Exit Function
        
PANIC:
        Debug.Print Err.Description
        loadRecordset = 0
        Stop
End Function
user14565129
  • 1
  • 1
  • Does `loadRecordset` return True when it fails? I'd try commenting out your error handlers just to make sure you're not missing any errors. – Tim Williams Nov 02 '20 at 17:07
  • 1
    Usually connections strings use either `PROVIDER` for OLEDB or `DRIVER` for ODBC, not both. See example SQL Server [connection strings](https://www.connectionstrings.com/sql-server/). Also, since you mention setup fails. please post all errors. – Parfait Nov 02 '20 at 17:14
  • Your connection string looks OK to me, specifically as the `ExecuteCRUD` does work, as you indicate. I've been using `Provider=SQLOLEDB` for decades without any _(driver-related)_ problems. If a problem occurred, it always turned out to my code being buggy. So the error must be with your SQL passed to `loadRecordset()`. BTW: you should make it a habit to pass all parameters `ByVal`, e.g. `loadRecordset(ByVal Sql As String)` to prevent the method to accidental modify the value of a parameter. VBA/VB6 defaults to `ByRef`, if nothing is specified. – Hel O'Ween Nov 02 '20 at 17:59
  • 1
    @Parfait, one can specify both Provider and Driver keywords in the ADO connection string in the case of MSDASQL, which is the "Microsoft OLE DB Provider for ODBC Drivers". This is the default driver when no OLE DB provider is specified and ADO can't use ODBC without it. But it would be best to slip ODBC with SQL Server and jjust use the SQLOLEDB provider as in the SQLOLEDB connection string. – Dan Guzman Nov 02 '20 at 22:32
  • Check all your available installed OLEDB providers with this [PowerShell script](https://stackoverflow.com/questions/57164325/excel-vba-to-sql-server-2016-connection-string-error/57167973#57167973) (remove `IndexOf` for all). For ODBC drivers, use command [`Get-OdbcDriver`](https://learn.microsoft.com/en-us/powershell/module/wdac/get-odbcdriver?view=win10-ps). – Parfait Nov 02 '20 at 23:25

0 Answers0