Category: VBA

I am tending to encourage clients to use SmartConnct from eOne Solutions for integrating data into Microsoft Dynamics GP, but I do still have quote a few clients using Integration Manager.

SmartConnect supports the use of custom eConnect nodes which I have created for a few clients, either manually or through using Node Builder (also from eOne).

You can accomplish the same result through Integration Manager by extending the integration using VBA. I had a client a while ago who were using the Direct Debits & Refunds module. This means that each transaction needs to have a DD Transaction Code code stamped on it, which Integration Manager doesn’t do. However, with a little VBA, this can be accomplished.

In the Before Integration script we instantiate the ODBC connection:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
' BEFORE INTEGRATION
Dim oCon

Set oCon = CreateObject("ADODB.Connection")

oCon.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
GPConnection.Open(oCon)

SetVariable "gblCon", oCon

Continue reading “VBA To Insert Next Microsoft Dynamics GP DD Transaction Code”

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

This post is part of the series on VBA Snippets.

There is an ADO connection available to VBA within Microsoft Dynamics GP which you can use, but there are some steps you need to follow to use it.

The first step is to declare the variable which will hold the connection.

Private madoConn AS ADODB.Connection

Then you need to create the connection which this example does using a Connect subroutine:

Private Sub Connect()
	If madoConn.State <> adStateOpen Then 
		Set madoConn = UserInfoGet.CreateADOConnection
		madoConn.DefaultDatabase = UserInfoGet.IntercompanyID
	End If
End Sub

It checks if the connection is already open and, if not, uses the UserInfoGet object which holds the connection detail exposed in Dynamics GP; I am also using the same object to set the default database property.

Once connected you can use the connection to execute SQL queries; I’ll show some examples of this in later posts.

When you’re finished with the connection, you can close and destroy the connection:

Private Sub Disconnect()
    If madoConn.State = adStateOpen Then madoConn.Close
    Set madoConn = Nothing
End Sub