VBA Snippets: Select Records from Microsoft Dynamics ODBC Connection

This post is part of the series on VBA Snippets.

In yesterdays post, I covered adding an ODBC connection to Microsoft Dynamics GP VBA for use n windows or reports. The below is an example of a SQL query using the ODBC connection.

SOPType and SopNUmber (highlighted) are fields from a window added to the VBA.

This example retries a list of fields from the Sales Transaction Amounts Work (SOP10200) table.

Dim objRS As ADODB.RecordSet
Set objRS = New ADODB.RecordSet
Set objRS.ActiveConnection = madoConn
sSQL = "SELECT * FROM SOP10200 WHERE SOPTYPE = " & SOPType & " AND SOPNUMBE = '" & SOPNumber & "'"
objRS.Source = sSQL
objRS.Open

If objRS.State = adStateOpen Then
	If Not (objRS.BOF Or objRS.EOF) Then
		objRS.MoveFirst
		
		Do While Not objRS.EOF
			' your code goes here; reference fields using objRS.fields("fieldname"))
		
			objRS.MoveNext
		Loop
	End If
	objRS.Close
End If
Set objRS = Nothing