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 |
VBA >