Automate MYOB AccountRight Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

MYOB AccountRight ADO.NET Provider

Complete read-write access to MYOB AccountRight enables developers to search (Customers, Transactions, Invoices, Sales Receipts, etc.), update items, edit customers, and more, from any .NET application.



Are you looking for a quick and easy way to access MYOB AccountRight data from PowerShell? We show how to use the Cmdlets for MYOB AccountRight and the CData ADO.NET Provider for MYOB AccountRight to connect to MYOB AccountRight data and synchronize, automate, download, and more.

The CData Cmdlets for MYOB AccountRight are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to MYOB AccountRight.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the MYOB AccountRight API, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete MYOB AccountRight data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for MYOB AccountRight. To access MYOB AccountRight data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for MYOB AccountRight.

After obtaining the needed connection properties, accessing MYOB AccountRight data in PowerShell consists of three basic steps.

These properties are required when connecting to a company file (both for Cloud and On-Premise instances).

  • CompanyFileId: You can find this by starting MYOB, opening your data file, and selecting Help -> About MYOB
  • User: The username associated with your company file.
  • Password: The password associated with your company file.

Connecting to a Cloud Instance

To connect to a cloud instance of MYOB, you can use the embedded OAuth credentials or create an OAuth app with MYOB. This process is detailed in the Help documentation.

Connecting to an On-Premise instance:

When connecting to an on-premise instance, you will need to set the following connection property in addition to those above:

  • InitiateOauth: Set this to OFF.
  • Url: The Url of your MYOB instance.

PowerShell

  1. Install the module:

    Install-Module MYOBCmdlets
  2. Connect:

    $myob = Connect-MYOB -OAuthClientId "$OAuthClientId" -OAuthClientSecret "$OAuthClientSecret" -CompanyFileId "$CompanyFileId" -CallbackURL "$CallbackURL" -User "$User" -Password "$Password"
  3. Search for and retrieve data:

    $type = "Bank" $accounts = Select-MYOB -Connection $myob -Table "Accounts" -Where "Type = `'$Type`'" $accounts

    You can also use the Invoke-MYOB cmdlet to execute SQL commands:

    $accounts = Invoke-MYOB -Connection $myob -Query 'SELECT * FROM Accounts WHERE Type = @Type' -Params @{'@Type'='Bank'}

ADO.NET

  1. Load the provider's assembly:

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for MYOB AccountRight\lib\System.Data.CData.MYOB.dll")
  2. Connect to MYOB AccountRight:

    $conn= New-Object System.Data.CData.MYOB.MYOBConnection("OAuthClientId=YourClientId; OAuthClientSecret=YourClientSecret; CompanyFileId=yourCompanyFileId; CallbackURL=http://localhost:33333; User=companyFileUser; Password=companyFilePassword; InitiateOAuth=GETANDREFRESH") $conn.Open()
  3. Instantiate the MYOBDataAdapter, execute an SQL query, and output the results:

    $sql="SELECT Id, Name from Accounts" $da= New-Object System.Data.CData.MYOB.MYOBDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.id $_.name }

Update MYOB AccountRight Data

PowerShell

Update-MYOB -Connection $MYOB -Columns @('Id','Name') -Values @('MyId', 'MyName') -Table Accounts -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.MYOB.MYOBCommand("UPDATE Accounts SET Type='Bank' WHERE Id = @myId", $conn) $cmd.Parameters.Add(new System.Data.CData.MYOB.MYOBParameter("@myId","10456255-0015501366")) $cmd.ExecuteNonQuery()

Insert MYOB AccountRight Data

PowerShell

Add-MYOB -Connection $MYOB -Table Accounts -Columns @("Id", "Name") -Values @("MyId", "MyName")

ADO.NET

$cmd = New-Object System.Data.CData.MYOB.MYOBCommand("INSERT INTO Accounts (Type) VALUES (@myType)", $conn) $cmd.Parameters.Add(new System.Data.CData.MYOB.MYOBParameter("@myType","Bank")) $cmd.ExecuteNonQuery()

Delete MYOB AccountRight Data

PowerShell

Remove-MYOB -Connection $MYOB -Table "Accounts" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.MYOB.MYOBCommand("DELETE FROM Accounts WHERE Id=@myId", $conn) $cmd.Parameters.Add(new System.Data.CData.MYOB.MYOBParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()