We do a lot of VBA programming with Microsoft Access project (.adp) files where Access is a front-end to a SQL Server database.   Here is the standard VBA code to create an ADO recordset to query a table within the existing SQL Server database.

 

 

    Dim objRS As ADODB.Recordset
    Set objRS = CreateObject("ADODB.Recordset")
    Dim strSQL As String
   
    strSQL = " SELECT * from table"

    objRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    If Not (objRS.EOF And objRS.BOF) Then
        Do While Not objRS.EOF 'Start looping through the records
                'do something here with each record...        

            objRS.MoveNext
        Loop
    End If
    objRS.Close
    Set objRS = Nothing

 

 

Dim intSort As Integer
    intSort = Me.SortOrder
    Dim objRS4 As ADODB.Recordset
    Set objRS4 = CreateObject("ADODB.Recordset")
    Dim strSQL4 As String
   
    'DoCmd.SetWarnings False
   
    strSQL4 = " SELECT * from tblplansrooms WHERE PlanID = " & Me.PlanID & " and sortorder >= " & Me.SortOrder & " and RoomID < " & Me.RoomID & " order by sortorder"

    objRS4.Open strSQL4, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    If Not (objRS4.EOF And objRS4.BOF) Then
        Do While Not objRS4.EOF 'Start looping through the records
           
          
            objRS4("sortorder").Value = objRS4("sortorder") + 1
            objRS4.Update
            objRS4.MoveNext
          Loop
    End If
    objRS4.Close
    Set objRS4 = Nothing