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