CODEWITHSUNDEEP
sql-serverexcelvba
janf
janf

Reputation: 81

Store sql output in a vba variable

I have an sql query which I want to store in a vba variable and then get the number of rows of the output.

I have the following:

Dim PortInfo As Variant
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim lStr As String

Dim LastRow     As Long

  strServer = "DB-01"
    ConnectionString = "Provider=SQLOLEDB;Data Source=" & strServer & ";" & _
                  "Integrated Security=SSPI;"
    cnn.Open ConnectionString

lStr = "SELECT [PORTINFOID],[PORTNAME] FROM [a].[dbo].[portinfo]"

rst.Open lStr, cnn

 If Not rst.EOF And Not rst.BOF Then
   For i = 0 To rst.Fields.Count - 1
      PortInfo = PortInfo & rst.Fields(i).name & vbTab
           Next
       Do Until rst.EOF

      For i = 0 To rst.Fields.Count - 1
      PortInfo = PortInfo & rst.Fields(i) & vbTab
           Next
             rst.MoveNext
    Loop
 End If
 rst.Close
MsgBox PortInfo
    LastRow = ???

With the above I get the following:

enter image description here

Firstly, I am not sure if the PortInfo variable should be Variant. And if it should be, how can I get the output to be in a column/row way so that I can get the number of rows of the resulting table.

Upvotes: 0

Views: 1515

Answers (1)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

Some suggestions:

  • The PortInfo variable can by of type String.
  • The strServer variable is obsolete when using the server name in the connection string.
  • The database can also be part of the connection string.
  • When using vbTab as column separator, use vbCrLf as line separator.
  • To count the rows, just increment the LastRow variable for each record.
  • Do not use New when declaring a variable, because these can't be cleared.
  • Clear the object variables in the end.
  • Use Option Explicit in each Module to force yourself to declare all variables (like i).

For example, the code could look like this:

Dim PortInfo As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ConnectionString As String
Dim lStr As String
Dim LastRow As Long
Dim i As Integer

ConnectionString = "Provider=SQLOLEDB;Data Source=DB-01;" & _
    "Initial Catalog=a;Integrated Security=SSPI;"
lStr = "SELECT [PORTINFOID],[PORTNAME] FROM [dbo].[portinfo]"

Set cnn = New ADODB.Connection
cnn.Open ConnectionString

Set rst = New ADODB.Recordset
rst.Open lStr, cnn

With rst
    If Not .EOF And Not .BOF Then
        For i = 0 To .Fields.Count - 1
            PortInfo = PortInfo & .Fields(i).Name
            If i < .Fields.Count - 1 Then
                PortInfo = PortInfo & vbTab
            Else
                PortInfo = PortInfo & vbCrLf
            End If
        Next
    End If

    Do Until .EOF
        LastRow = LastRow + 1
        For i = 0 To .Fields.Count - 1
            PortInfo = PortInfo & .Fields(i)
            If i < .Fields.Count - 1 Then
                PortInfo = PortInfo & vbTab
            Else
                PortInfo = PortInfo & vbCrLf
            End If
        Next
        rst.MoveNext
    Loop

End With

rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing

MsgBox PortInfo
MsgBox "This have been " & LastRow & " records.", vbInformation, "I'm done."

Upvotes: 2

Related Questions