Using VBA to query a VIEW from SQL Server

Question

I am trying to create a VBA script that will pull the results from a View (SELECT * FROM view_name) from the RecordSet.Source property, but when attempted, my CloseConnection error handler keeps getting caught. I can get results from a table using a simple query like SELECT * FROM tbl_name with no issues.

Below is the code I am using. Note: my Const variable has the Provider and Database information removed.

I guess it really comes down to is it even possible to get results from a View like I would from a table?

Option Explicit

Const ConStrMSSQL As String = _
"Provider=provider_name;Database=database_name;Trusted_Connection=yes;"

Sub test()

    Dim formConnect As ADODB.connection
    Dim formData As ADODB.recordSet
    Dim formField As ADODB.Field

    Set formConnect = New ADODB.connection
    Set formData = New ADODB.recordSet

    formConnect.ConnectionString = ConStrMSSQL

    formConnect.Open

    On Error GoTo CloseConnection

    With formData
        .ActiveConnection = formConnect
        .Source = "SELECT * FROM v_data_extract_658"
        .LockType = adLockReadOnly
        .CursorType = adOpenForwardOnly
        .Open
    End With

    On Error GoTo CloseRecordset


    Sheets("test").Range("A1").Select

    For Each formField In formData.Fields
        ActiveCell.Value = formField.Name
        ActiveCell.Offset(0, 1).Select
    Next formField

    Sheets("test").Range("A2").CopyFromRecordset formData

    On Error GoTo 0

    CloseRecordset:
        formData.Close

    CloseConnection:
        formConnect.Close


End Sub

This is the error message:

run-time error 2147467259 (80004005): unknown token received from SQL Server

All things being equal, querying a view should look exactly like querying a table. Comment out the error handler: what is the error message you get? — Tim Williams
– Tim Williams, Commented Apr 23, 2018 at 21:27
I would strongly suggest you get rid of those ON ERROR GOTOs. You need to know about errors. What you have is an anti-pattern I call try/squelch. Handle the error gracefully and tell the user something happened. Just closing the window is a horrible way to "handle" an error. Very frustrating as a user when the program just closes. — Sean Lange
– Sean Lange, Commented Apr 23, 2018 at 21:35
@SeanLange You make good points. Please note that those error handlers were not the final product; more just placeholders. — linktheory
– linktheory, Commented Apr 23, 2018 at 22:28
Yes this should work fine. Be sure however that you have access to all the objects referenced in the view. For Example, the view might reference a Linked Server resource that you don't have access to. I've seen for example queries in DEV built atop objects that sit in STAGE/TEST. — Bill Roberts
– Bill Roberts, Commented Apr 23, 2018 at 22:29
@TimWilliams This is the error message: run-time error 2147467259 (80004005): unknown token received from SQL Server — linktheory
– linktheory, Commented Apr 23, 2018 at 22:29

Bill Roberts · Accepted Answer · 2018-04-26 16:28:21Z

1

I think the big issue here is that you haven't defined a Command Object.

I somewhat put this together "freehand" and for certain, didn't test it but it should get you to where you need to go.

Sub test()

On Error GoTo ErrorHandle:

    Dim formConnect As ADODB.Connection
    Set formConnect = New ADODB.Connection

    formConnect.ConnectionString = ConStrMSSQL

    Dim cmd   As ADODB.Command
    Set cmd = New ADODB.Command

    formConnect.Open

    With cmd
        .ActiveConnection = formConnect
        .CommandType = adCmdText
        .CommandText = "SELECT * FROM v_data_extract_658"
        .CommandTimeout = 30
    End With

    Dim formData   As ADODB.Recordset
    Set formData = New ADODB.Recordset

    formData.Open cmd, , adOpenStatic, adLockReadOnly

    Sheets("test").Range("A1").Select

    Dim formField As ADODB.Field
    For Each formField In formData.Fields
        ActiveCell.value = formField.Name
        ActiveCell.Offset(0, 1).Select
    Next formField

    Range("A2").CopyFromRecordset formData

    On Error GoTo 0

Cleanup:
    If Not formData Is Nothing Then
        If formData.State <> adStateClosed Then formData.Close
        Set formData = Nothing
    End If

    If Not formConnect Is Nothing Then
        If formConnect.State <> adStateClosed Then formConnect.Close
        Set formConnect = Nothing
    End If

    Set cmd = Nothing

    Exit Sub

ErrorHandle:
    MsgBox Err.Description

    'Do whatever else is needed to respond to errors.

    Resume Cleanup

End Sub

edited Apr 26, 2018 at 16:28

answered Apr 23, 2018 at 23:53

Bill Roberts

1,17119 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

linktheory Over a year ago

Just a heads-up,I had to move 'formConnect.Open' under 'formConnect.ConnectionString = ConStrMSSQL' to get the connection started. I did receive an error at this line: 'formData.Open cmd, , adOpenStatic, adLockReadOnly' Error Message: Unknown token received from sql server

Bill Roberts Over a year ago

thx - I've updated my answer to reflect the change you made. I guess at this point I'd explicitly spell out a few of the columns you expect to retrieve, because maybe either a column name or the content of a column might be causing the grief.

linktheory Over a year ago

To confirm, are you saying do this in the CommandText property and completely replace what I have there now?

Bill Roberts Over a year ago

Saying for this "SELECT * FROM v_data_extract_658", explicitly specify a few columns. Pick one or two that you expect to be consistent, like a Primary Key column, or those that have a Foreign Key Constraint. Purpose is try and isolate specifically where the choke point is. If you can get at least a few columns - or just one - to return, you'll have a better idea regards where to look for problems.

Bill Roberts Over a year ago

Which ActiveX Library are you using? I've recently deployed a solution to multiple users, using the following: Microsoft ActiveX Objects 2.7 Library - I would next see if I had better success with other Libraries. My solution returns SQL Views, and also kicks off a Stored Procedure.

|

Stavros Jon · Accepted Answer · 2019-06-06 08:12:11Z

Using Excel & VBA to fetch dta from SLQ Server is quite easy (not always, but these days).

Sub ADOExcelSQLServer()
     ' Carl SQL Server Connection
     '
     ' FOR THIS CODE TO WORK
     ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
     '

    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Server_Name = "EXCEL-PC\SQLEXPRESS" ' Enter your server name here
    Database_Name = "NORTHWND" ' Enter your database name here
    User_ID = "" ' enter your user ID here
    Password = "" ' Enter your password here
    SQLStr = "SELECT * FROM [Customers]" ' Enter your SQL here

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr, Cn, adOpenStatic
     ' Dump to spreadsheet
    For iCols = 0 To rs.Fields.Count - 1
        Worksheets("Sheet1").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
    Next
    With Worksheets("sheet1").Range("a2:z500") ' Enter your sheet name and range here
        '.ClearContents
        .CopyFromRecordset rs
    End With
     '            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

As an aside, you can try this as well (please change to suit your specific setup/configuration)...

Sub Working2()

Dim con As Connection
Dim rst As Recordset
Dim strConn As String

Set con = New Connection
strConn = "EXCEL-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True"

con.Open strConn

'Put a country name in Cell E1
Set rst = con.Execute("Exec dbo.MyOrders '" & ActiveSheet.Range("E1").Text & "'" & ActiveSheet.Range("E2").Text & "'")

'The total count of records is returned to Cell A5
ActiveSheet.Range("A5").CopyFromRecordset rst

rst.Close
con.Close

End Sub

Please see the link below for more details.

https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Excel%20Data%20Export%20to%20SQL%20Server%20Test%20Code

I tried the first option you provided and received the error message via error handling: "[Microsoft][ODBC Server Driver]Query timeout expired" on this line of code: 'rs.Open SQLStr, Cn, adOpenStatic'. I am not sure if this is the reason, but SQLStr is set to pull data from a View.

Collectives™ on Stack Overflow

Using VBA to query a VIEW from SQL Server

2 Answers 2

12 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related