Results 1 to 8 of 8
  1. #1
    Tukayoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5

    ADO SQL Data to Form Combobox


    I have been trying to find a way to get this done but have exhausted my research on the issue. Is there a way with doing an ADODB connection to a SQL server to take data that is in the table in the server and input that data into a combobox on a form? I have been able to create the connection and get data from the server in the from of a recordset and output that to my immediate window via debug.print. I just need to know if that is the right direction and if so then how do I get the data from the recordset into the cbo in my form. right now my connection is being done in a module. I am also having issues then calling the sub in my form events. any input would be greatly appreciated.

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Normally you'd link to the table, and the combo's row source would be that table or a query/SQL based on it. Is that not feasible here? I have seen code looping a recordset and using AddItem to add selections to a combo, but in my experience it isn't used much. I've had a couple of apps which ran over a WAN where I copied data from a SQL table to a local table to use for combo selections, but again not a common need.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Tukayoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5
    Thanks for the input. I am looking to try and keep everything as back end as I can with out the opportunity for the user to see or do anything with the tables, i.e. linked tables. If I use a local table that is populated from the recordset, wouldn't I need to then have a table for every recordset that I would want to pull?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I used that method for "lookup" tables: lists of drivers, customers, etc. Transaction data I used either pass through queries or ADO commands to execute stored procedures or append/delete/update SQL.

    If you only give users the runtime version of Access, they have no access to the tables.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Tukayoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5
    Ok thanks. so there is no way to bring data directly from SQL then into a combobox via VBA? I will have to check into the things that you have recommended and see how they will work for me.

    Thanks again.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It could have a pass through query as the row source. Others may have other ideas. It's not something I've had to do alot.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Tukayoo View Post
    Thanks for the input. I am looking to try and keep everything as back end as I can with out the opportunity for the user to see or do anything with the tables, i.e. linked tables. If I use a local table that is populated from the recordset, wouldn't I need to then have a table for every recordset that I would want to pull?
    You can hide Access Objects pane from users, restricting all direct access to tables, forms (for editing), procedures, etc.. You also can limit available Access Menu options, or you can hide Access own menu from users, and show application-specific menu you designed yourself instead.

    From the level of security you need depends how complex this will be. Simplest way is to hide what you need, but this allows for smart user to bypass settings. Most complex ways need to define user rights in Access - but Access security is quite cumbersome (as much as I know, there is no way to manage it through domain groups).

  8. #8
    ongke0711 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2016
    Posts
    1
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 12-15-2017, 06:14 PM
  2. Combobox selections on Form are updating data
    By b_rye_chan in forum Forms
    Replies: 4
    Last Post: 07-28-2016, 03:37 PM
  3. Replies: 8
    Last Post: 04-12-2014, 01:29 PM
  4. Change data in subform from form ComboBox
    By ShostyFan in forum Programming
    Replies: 1
    Last Post: 02-08-2013, 05:18 PM
  5. Replies: 12
    Last Post: 11-23-2012, 11:41 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums