Disconnected recordsets with VBA Queries

Why use the disconnected recordset method?

When working with multiple recordsets in VBA it is important to be mindful of server activity. If the server is already running close to max capacity, leaving VBA recordsets open can negatively impact the performance. However, by disconnecting the connection to the recordset we can run multiple recordset queries and keep as little stress as possible on the server.

Disconnected Recordset in VBA using ADO and GetRows

This is a way I've found useful. The general idea is never keeping the connection open any longer than you have to. 

	
		Sub RunQuery()

		'	You can declare as many arrays as you need

		Dim RS1 As Variant
		Dim ParameterValues As String
		ParameterValues = "You can change this as needed"

		RS1 = GetDiscRecordset(ParameterValues)

		For c = LBound(RS1, 1) To UBound(RS1, 1)

			For r = LBound(RS1, 2) To UBound(RS1, 2)

				'	Iterate through the recordset
				Debug.Print RS1(c, r)


			Next r

		Next c
	End Sub

The GetDiscRecordset function is similar to your execute function but we are returning a Disconnected recordset.

	Function GetDiscRecordset(ParameterValues As String) As Variant
		Dim Qry As String

		Qry = "Select * From SourceTable Where " & _
			"[?PlaceHolder for Parameters?]" 

		Qry = Replace(Qry, "[?PlaceHolder for Parameters?]", " & _
				ParameterValues)

		Dim Conn As ADODB.connection

		Set Conn = New ADODB.connection

		Dim Rst As ADODB.Recordset

		'	Modify as needed
		Conn.ConnectionString = "Connection String" 
		Conn.Open

		Set Rst = New ADODB.connection

		Set Rst.ActiveConnection = Conn

		'	Retrieve data
		Rst.CursorLocation = adUseClient

		Rst.LockType = adLockBatchOptimistic

		Rst.CursorType = adOpenStatic

		Rst.Open Qry, , , , adCmdText 

		'	NOW DISCONNECT RECORDSET HERE!
		Set Rst.ActiveConnection = Nothing

		Rst.MoveFirst
		'	Pass the recordset back
		GetDiscRecordset = Rst.GetRows
	End Function