Store sql output in a vba variable

Question

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:

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.

Wolfgang Kais · Accepted Answer · 2018-08-16 22:16:02Z

2

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."

edited Aug 16, 2018 at 22:16

answered Aug 16, 2018 at 21:53

Wolfgang Kais

4,1002 gold badges13 silver badges19 bronze badges

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

2 Comments

janf Over a year ago

thanks! Another question, will I be able to access each entry of the PortInfo variable? The next goal is to have each entry in a dynamic checkbox of a userform. and I was thinking of doing it as

For i = 1 To LastRow     Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)     chkBox.Caption = *here goes what I haven't figured out*     chkBox.Left = 5     chkBox.Top = 5 + ((i - 1) * 20) Next i

Wolfgang Kais Over a year ago

Well... no. And for sure you will not create a checkbox for every column, will you? I suggest to save the respective values in an array (if you want to save the values for later reference), or create the dynamic checkbox list right from within the loop.

Collectives™ on Stack Overflow

Store sql output in a vba variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related