Integration Manager 10 and the GPConnection Object and Properties
The GPConnection object replaces the instantiation previously made with the RetreiveGlobals DLL, used with previous versions of Microsoft Dynamics GP.
Open method
The Open method allows you to open an ADO connection using the current GP user login information. This method uses the data source that is in use when Microsoft Dynamics GP is open. If you want to use a default company database (TWO or GPDAT) for this method, then you must set the Open value in the connection string before using the Open method.
You will not be able to update the connection string after the Open method is called. There is no close method for this object. Once the connection is returned to the same way the connection object was initially created in the script, that connection object can be closed normally.
Syntax
object.Open({suppress})
Comments
All properties for the Open method will return string values.
Examples
The following example is the Open script. It opens the data connection.
The following is an example of creating the ADO record set.
The following is an example of creating the ADO connection.
The following is an example of executing the update command
The following is an example of closing the ADO Connection.
The following are examples of retrieving the properties exposed by the new GPConnection object.
UserDate property
The UserDate property contains the current user date.
Syntax
object.UserDate
CompanyID property
The CompanyID property contains the intercompany ID (company database ID).
Syntax
object.CompanyID
Example
The following is an example of initializing the connection string to specify a default
database. In this case it is set to the current company. This could be set to a constant
database, such as GPDAT.
UserID property
The UserID property contains the current User ID.
Syntax
object.UserID
UserName property
The UserName property contains the name of the current user.
Syntax
object.UserName
Example
The following is an example of creating a string to update the customer name in the customer master table.
DataSource property
The DataSource property contains the name of the current data source that is being used in Microsoft Dynamics GP.
Syntax
object.DataSource
Until next post!
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Open method
The Open method allows you to open an ADO connection using the current GP user login information. This method uses the data source that is in use when Microsoft Dynamics GP is open. If you want to use a default company database (TWO or GPDAT) for this method, then you must set the Open value in the connection string before using the Open method.
You will not be able to update the connection string after the Open method is called. There is no close method for this object. Once the connection is returned to the same way the connection object was initially created in the script, that connection object can be closed normally.
Syntax
object.Open({suppress})
Comments
All properties for the Open method will return string values.
Examples
The following example is the Open script. It opens the data connection.
set MyCon = CreateObject("ADODB.Connection")
MyCon.Connectionstring = "database=GPDAT"
GPConnection.Open(MyCon)
The following is an example of creating the ADO record set.
set recset = CreateObject("ADODB.Recordset")
The following is an example of creating the ADO connection.
set MyCon = CreateObject("ADODB.Connection")
The following is an example of executing the update command
recset = MyCon.Execute(updatecommand )
The following is an example of closing the ADO Connection.
MyCon.Close
The following are examples of retrieving the properties exposed by the new GPConnection object.
MsgBox GPConnection.GPConnUserDate
MsgBox GPConnection.GPConnInterCompanyID
MsgBox GPConnection.GPConnUserID
MsgBox GPConnection.GPConnUserName
MsgBox GPConnection.GPConnDataSource
UserDate property
The UserDate property contains the current user date.
Syntax
object.UserDate
CompanyID property
The CompanyID property contains the intercompany ID (company database ID).
Syntax
object.CompanyID
Example
The following is an example of initializing the connection string to specify a default
database. In this case it is set to the current company. This could be set to a constant
database, such as GPDAT.
MyCon.Connectionstring = "database=" + GPConnection.GPConnInterCompanyID
UserID property
The UserID property contains the current User ID.
Syntax
object.UserID
UserName property
The UserName property contains the name of the current user.
Syntax
object.UserName
Example
The following is an example of creating a string to update the customer name in the customer master table.
updatecommand = "update RM00101 set [CUSTNAME]='IM Customer' where [CUSTNMBR]='AARONFIT0022'"
DataSource property
The DataSource property contains the name of the current data source that is being used in Microsoft Dynamics GP.
Syntax
object.DataSource
Until next post!
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Comments
CurrentField = pRecordset.Fields(0).Value
But I continue to get errors, "object doesn't support this property or method." I am also having problems with MoveFirst. Can you point me to additional documentation on this new object, thanks.
Thanks for the inquiry! You will want to change your syntax as follows:
CurrentField.Value = pRecordset(0)
Keep in mind that pRecordset should have been previously defined as an ADODB.Recordset object.
Before you can call the MoveFirst method, you will need to make sure the recordset is not empty:
If not(pRecordSet.EOF) and not (pRecordset.BOF) Then
pRecordset.MoveFirst
Else
pRecordset.Close
End If
Keep in mind that this is just an assumption based on the limited information you provided. It may or may not be accurate based on your situation.
Best regards,
MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
I beleive the following sample code was authored by you;
set oCn = CreateObject("ADODB.Connection")
set oCmd = CreateObject("ADODB.Command")
oCn.ConnectionString = "Database=" & GPConnection.GPConnIntercompanyID
oCn.CursorLocation = adUseClient
GPConnection.Open(oCn)
Thanks for your inquiry. I am not sure about the specifics of your environment, so I really cannot assert why you are getting the error message. I tried the code you posted and got no errors (of course, I had to define the constant adUseClient).
I just upgraded GP9 to GP10. I have alot of scripts in my integration manager and I see that IM version no longer uses retrieve.global anymore. can you help me decipher this script to version 10?
Dim userinfo
Dim luserid
Dim lintercompanyid
Dim lsqldatasourcename
Set userinfo = CreateObject("RetrieveGlobals9.retrieveuserinfo")
luserid = userinfo.retrieve_user()
lintercompanyid = userinfo.intercompany_id()
lsqldatasourcename = userinfo.sql_datasourcename()
SetVariable "lintercompanyid", lintercompanyid
Any help is greatly appreciated.
Thanks,
ARov
GPConnection.Open(MyCon)
I have not used SQL in an integration before but I've looked at other code and tried to do it but keep getting errors. can you tell me where I'm going wrong, thanks!
field script:
Dim EMPID
Dim FILECODE
EMPID = SourceFields("_PR_DTL.F2")
FILECODE = SourceFields("_PR_DTL.F4")
sSQL = "if not exists (SELECT * FROM UPR00400 where PAYRCORD='REL AW' and UPR00400.EMPLOYID ='" & EMPID & "' ) SELECT 'HOURLY' ELSE SELECT 'REL AW'"
set MyCon = CreateObject("ADODB.Connection")
MyCon.Connectionstring = "database=" + GPConnection.GPConnInterCompanyID
GPConnection.Open(MyCon)
PAYCODE = MyCon.Execute(sSQL)
Set MyCon = nothing
If FILECODE= "C" then
CurrentField= PAYCODE
ElseIf FILECODE = "W" then
CurrentField= "BUS"
Else
CurrentField= FILECODE
End If