Hi Tukayoo,
You can use ADODB library to do as your request.
(English is not my native language os please try to catch my idea )
After using ADODB connection to connect SQL Server and get back the Recordset by object ADODB Command. This Recordset will be stored in memory and you can assign it to Combobox
Code is something like this: example
- Combobox name: cboProductsList
- Table in Sql Server: tblProducts
- SQL Server name: QUOCBAO-PC. You change to yours.
Code:
Option Explicit
Dim rst As ADODB.Recordset
Private Sub cmdPopulateCboRec_Click()
On Error GoTo EH
Call GetRecordset
Dim s As String
Dim i As Long
With rst
.MoveFirst
Do Until .EOF
s = ""
For i = 0 To rst.Fields.Count - 1
s = s & IIf(i = 0, "", ";") & rst.Fields(i).Value
Next i
Debug.Print s
Me.cboProductsList.RowSourceType = "Value List"
Me.cboProductsList.ColumnCount = rst.Fields.Count
Me.cboProductsList.AddItem s
.MoveNext
Loop
End With
Me.cboProductsList.Requery
EH_Exit:
Exit Sub
EH:
MsgBox "Error number: " & Err.Number & vbCrLf & "Error description: " & Err.Description
Resume EH_Exit
End Sub
Function GetRecordset()
Dim strConn As String
Dim cmd As ADODB.Command
Set rst = New ADODB.Recordset
strConn = "PROVIDER=SQLOLEDB;SERVER=QUOCBAO-PC;DATABASE=Test;" _
& "TRUSTED_CONNECTION=YES"
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = strConn
.CommandText = "SELECT * FROM tblProducts"
End With
With rst
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd
End With
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
End Function
Hope this help.