VBA Snippets: Select Records from Microsoft Dynamics ODBC Connection

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

Read original post VBA Snippets: Select Records from Microsoft Dynamics ODBC Connection at azurecurve|Ramblings of a Dynamics GP Consultant