Results 1 to 3 of 3
  1. #1
    emanon is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    8

    Populate results from SQL Server using Recordset into MS Access

    I am not sure how I can put the results from Record Set into a query or result pane in MS Access. The results are from SQL Server, so that is why I am stuck on this. I would think I need to do something where are x's are.


    Code:
       Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim strConnString As String
         
        strConnString = "Provider=SQLNCLI11;Server=SRV;Database=Staging;Trusted_Connection=yes;"
        
        Set conn = New ADODB.Connection
        conn.Open strConnString
    
    
        Set rs = conn.Execute("Select * from MSAccess_APP_ComplianceDashBoard ")
        
    
        XXXXXXXXXXXXXXXXXX
    
    
        
        rs.Close


  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Agree with vlad - the recordset can only be applied to a form or report - so you will need to know the field names your query will be returning.

    You can create a 'blank' form with say 30 textboxes (no label controls required). Name them txt0, txt1, txt2 etc

    assign the recordset to the form recordset per vlad's link then loop through the recordset fields and assign the fieldname to controls in turn.

    Something like

    Code:
    dim i as integer
    dim fld as ado.field
    set me.recordset=rs
    i=0
    for each fld in me.recordset.fields
        me("txt" & i).controlsource=fld.name
        i=i+1
    next fld
    you might need more code for error handling (e.g. recordset has more than 30 fields), formatting, resizing columns, hiding unused columns etc

    note that the form filter and sort functionality does not work with ADO recordsets. If you need that you need to write your own shortcut menus to handle it.

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

Similar Threads

  1. Replies: 5
    Last Post: 09-18-2019, 05:41 PM
  2. Populate listbox with DAO Recordset
    By andrews in forum Programming
    Replies: 6
    Last Post: 04-18-2019, 02:58 PM
  3. Replies: 2
    Last Post: 03-30-2018, 11:33 AM
  4. Replies: 3
    Last Post: 06-19-2012, 06:01 AM
  5. Populate a Combo Box from a RecordSet
    By dreamnauta in forum Programming
    Replies: 1
    Last Post: 01-27-2012, 03:18 PM

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