Another SQL Server/VBA question

[kwvh]

I have another challenge relating to connecting to an SQL Server database via VBA and creating a recordset.

I need to:
1. Connect to the database via VBA (done)
2. Populate the RowSource of a combo box with a recordset created by querying a table in the SQL Server database

This is how I have started, but looks like I may be going down the wrong path.

Code: Select all

        
 Dim cnn As New ADODB.Connection
 Dim rst2 As New ADODB.Recordset
 Dim strSQL As String
    
On Error GoTo Form_Load_Error
' ==============================
  Dim objCon As ADODB.Connection
  Dim objCat As ADOX.Catalog
  Dim objTbl As ADOX.Table
  Dim objInd As ADOX.Index
  Dim objCol As ADOX.Column
  Dim strCon As String
  Dim ConnectionExists As Boolean

    fCreateConnectionString ' Function to create the connection string

  strCon = Me.txtConnectionString
  
  Set objCon = New ADODB.Connection
  objCon.Open strCon
    

    
  Set objCat = New ADOX.Catalog
  Set objCat.ActiveConnection = objCon
  For Each objTbl In objCat.Tables
    If objTbl.Type = "TABLE" Then
'        If objTbl.Type = "View" Then
            If objTbl.Name = "tblStateNames" Then
            strSQL = "SELECT STATE_txtAbbreviation, STATE_txtState " & _
                         "FROM tblStateNames " & _
                         "ORDER BY STATE_txtAbbreviation;"
             Me.S_txtState.RowSource = strSQL
             GoTo ExitHere            
            End If
        End If
  Next objTbl
    
ExitHere:
 
Form_Load_Exit:
   Exit Sub

Form_Load_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Load of VBA Document Form_frmSiteDynCorp2011"
    GoTo Form_Load_Exit

Any ideas are greatly appreciated.

Ken

[HansV]

If you create a link to the tblStateNames table in your Access database, you can simply use

Code: Select all

Private Sub Form_Load()
    Dim strSQL As String

    On Error GoTo Form_Load_Error
    strSQL = "SELECT STATE_txtAbbreviation, STATE_txtState " & _
        "FROM tblStateNames " & _
        "ORDER BY STATE_txtAbbreviation;"
    Me.S_txtState.RowSource = strSQL
    Exit Sub

Form_Load_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure Form_Load of VBA Document Form_frmSiteDynCorp2011"
End Sub

[kwvh]

Hans,

Thanks for the help. The customer IT doesn't want the Access frontend connected/linked directly to tables, but ask for connecting, updating, or querying and then disconnect. Originally had the table linked, and then bound table to the combobox, but they ask that I change it. Thus my delimma.

Thanks again.

[HansV]

If you set the row source of the combo box to a recordset, you'll keep a connection to the database open. If they don't want linked tables, I presume they don't like open connections either. So I'd change the row source type of the combo box to Value List, and populate it from a recordset:

Code: Select all

Private Sub Form_Load()
  Dim cnn As New ADODB.Connection
  Dim rst2 As New ADODB.Recordset
  Dim strSQL As String
  Dim objCon As ADODB.Connection
  Dim strCon As String
    
  On Error GoTo Form_Load_Error
  ' ==============================

  fCreateConnectionString ' Function to create the connection string
  strCon = Me.txtConnectionString
  objCon.Open strCon
  strSQL = "SELECT STATE_txtAbbreviation, STATE_txtState " & _
    "FROM tblStateNames " & _
    "ORDER BY STATE_txtAbbreviation;"
  rst2.Open strSQL, cnn, adOpenForwardOnly, adLockOptimistic, adCmdText
  Do While Not rst2.EOF
    Me.S_txtState.AddItem rst2!STATE_txtAbbreviation & ";" & rst2!STATE_txtState
    rst2.MoveNext
  Loop

Form_Load_Exit:
  On Error Resume NExt
  rst2.Close
  cnn.Close
  Set rst2 = Nothing
  Set cnn = Nothing
  Exit Sub

Form_Load_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & _
      ") in procedure Form_Load of VBA Document Form_frmSiteDynCorp2011"
  Resume Form_Load_Exit
End Sub

The recordset is closed at the end of the code.

[kwvh]

Hans,

I don't know if anyone has told you recently, but you are an incredible fellow!

That worked GREAT! And has potential in other areas.

Thank you!
Ken

[kwvh]

Hans,

As mentioned previously, that worked very well. Now the request is to add column heads to the dropdown. I added

Code: Select all

Me.cboSite2Edit.AddItem "Abbrev" & ";" & "State Name"

But that adds them as an item in the list, which allows them to select it, which we don't want.

Is there a way to get the headings added via VBA as headings, and not as items?

Thanks in advance for sharing.

[HansV]

Set the Column Heads property to Yes, and make sure that you place the line

Me.cboSite2Edit.AddItem "Abbrev;State Name"

(the &'s aren't necessary here) before the code that adds items from the recordset.

[kwvh]

Excellent! THANKS!