(Solved) – Retrieve all of query containing semi-colons – VBA

(solved)-–-retrieve-all-of-query-containing-semi-colons-–-vba

I’m trying to pull data from SQL via an ADODB recordset in VBA. I’m struggling to get results from each part of a SQL query when it contains semi-colons. Wondering if there’s any way to do this without splitting my query into separate queries (to remove the semi-colon issue) and using separate recordsets for each.

See below for a simple example. When I run it, F2=1, G2=Failed – I want F2=1, G2=2.

' Sub to test using semi-colons in SQL queries
Sub getDataSimple0(server As String, database As String)

  ' Initialise variables
  Dim con As ADODB.Connection
  Dim rs As ADODB.Recordset

  Set con = New ADODB.Connection
  Set rs = New ADODB.Recordset

  ' Open Connection using Windows Authentication
  con.ConnectionString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Trusted_connection=Yes;Integrated Security=SSPI;Persist Security Info=True;"
  con.Open

  ' Open recordset
  rs.Open "SELECT 1; SELECT 2", con

  ' Add data to worksheet
  Range("F2").CopyFromRecordset rs

  rs.NextRecordset
  If rs.State > adStateClosed Then
    Range("G2").CopyFromRecordset rs
  Else
    Range("G2").Value = "Failed"
  End If

  ' Close connection
  con.Close

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *