VBA code to create an ADO recordset in an Access database project file

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