VBA Login sage50

Question

I connect to sage50 with VBA code and the application starts - no problem. But then the system aks to put in username and password. How do I subbmit this values by VBA code?

I connect sage50 like this:

Option Explicit

Sub StartSage50()
    Dim MyAppID
    ' --- Start sage50
    MyAppID = Shell("C:\Program Files (x86)\Sage\Sage50\Lohn\Sage50Lohn.exe", 1) 

' -

End Sub

Answer 1

Not sure if Sage50 uses the same COM API Sage 300 uses, but if it does you'll want to add a reference to the AccpacCOMAPI type library.

To connect to Sage, you need to create an AccpacSession object:

Dim session As AccpacCOMAPI.AccpacSession
Set session = New AccpacCOMAPI.AccpacSession

Then you need to invoke its Init method:

'Application ID "XY" is reserved for non-SDK applications to use.
Const NonSDKAppId As String = "XY"
Const ProgramName As String = NonSDKAppId & "9999"
Const AppVersion As String = "01A"

session.Init vbNullString, NonSDKAppId, ProgramName, AppVersion

From there you can get the available organizations (Sage databases) like this:

Dim orgs As Collection
Set orgs = New Collection

Dim index As Long
Dim org As AccpacCOMAPI.AccpacOrganization

For index = 0 To session.Organizations.Count - 1
    With session.Organizations.ItemByIndex(index)
        If .Type = AccpacCOMAPI.ORG_COMPANY Then orgs.Add Array(.DatabaseID, .Name)
    End with
Next

ReDim organizations(0 To orgs.Count - 1, 0 To 1)
Dim itemIndex As Long
Dim item As Variant
For Each item In orgs
    organizations(itemIndex, 0) = item(0)
    organizations(itemIndex, 1) = item(1)
    itemIndex = itemIndex + 1
Next

And now you have an organizations 2D array that contains the database ID in one column, and the description in another - which is perfect for populating a 2-column dropdown/combobox on a custom login form, if you want one. Otherwise you can just hard-code your credentials when you Open the session:

session.Open "username", "password", "database ID", VBA.DateTime.Now, 0, vbNullString

And the last & final step is to open the DbLink:

Dim dblink As AccpacCOMAPI.AccpacDBLink
Set dblink = session.OpenDBLink(AccpacCOMAPI.DBLINK_COMPANY, AccpacCOMAPI.DBLINK_FLG_READWRITE)

And from there, you're good to go!

Debug.Assert session.IsOpened '<~ should be true now