VBA‎ > ‎

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...        
    End If
    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
    End If
    Set objRS4 = Nothing