Using access vba to pass the login via variables to automate the login to Sage Line 50 ver20

Question

So i managed to connect Sage to my access database which is great but every time i close the database and open it up again i have to login again manually is there anyway of storing that data in Access? Be it via a variable or a table because clearly access can save the login information somewhere because i only need to login once.

Edit - added login connect details

Sub login_Click() 
    Dim sagedb As Database 
    Dim accessdb As Database 
    Dim rs As Recordset 
    Dim strConnect As String 
    Set sagedb = OpenDatabase("Directory") 
    strConnect = "DSN=SageAccountsVer20;uid=xxx;pwd=xxx;" 
    Set accessdb = OpenDatabase("", False, False, strConnect) 
    accessdb.Close 
    Set accessdb = Nothing 
    Set rs = sagedb.OpenRecordset("dbo_authors") 
    Debug.Print rs(0) 
    Debug.Print "Recordset Opened Successfully" 
    rs.Close 
    sagedb.Close
    Set rs = Nothing

Answer 1

I don't know Sage 50, but I've worked with the Sage 300 API, which is presumably similar; you can/should store an instance of your Sage session and keep it alive for as long as you need the Sage connection alive.

The session interface I've worked with needs to be initialized, and then opened; your code needs to provide a string handle, an appID, a program name and an app version to be initialized, and then when you open the session you need a userID, a password and a database name.

Here's a C# wrapper interface I'm using (referenging Sage's COM API), that shows how the values are passed:

public interface ISession : IDisposable
{
    void Init(string handle, SageAppInfo info);
    void Open(SageCredential credential, DateTime timestamp, int flags = 0);
    IDbLink OpenDbLink(DBLinkType type, DBLinkFlags flags);
    IEnumerable<string> Errors { get; } 
}

public class SessionWrapper : ISession
{
    private readonly ISessionComInterop _session;

    public SessionWrapper(ISessionComInterop session)
    {
        _session = session;
    }

    public void Init(string handle, SageAppInfo info)
    {
        _session.Init(handle, info.AppId, info.ProgramName, info.AppVersion);
    }

    public void Open(SageCredential credential, DateTime timestamp, int flags = 0)
    {
        _session.Open(credential.UserId, credential.Password, credential.DatabaseName, timestamp, flags);
    }

    public IDbLink OpenDbLink(DBLinkType type, DBLinkFlags flags)
    {
        return new DbLinkWrapper(_session.OpenDBLink(type, flags));
    }

    public IEnumerable<string> Errors
    {
        get
        {
            for (var i = 0; i < _session.Errors.Count; i++)
            {
                yield return string.Format("[{0}] {1} ({2})", _session.Errors[i].Code, _session.Errors[i].Message, _session.Errors[i].Source);
            }
        }
    }

    public void Dispose()
    {
        _session.Dispose();
    }
}

Of course a VBA implementation will be wildly different, but the important part is this:

_session.Open(credential.UserId, credential.Password, credential.DatabaseName, timestamp, flags);

My calling code looks like this:

_session.Init(string.Empty, _info);
_session.Open(_credential, DateTime.Now);
_db = _session.OpenDbLink(DBLinkType.Company, DBLinkFlags.ReadWrite);

So, have your code supply a UserId a Password, a DatabaseName, a timestamp and whatever DBLinkFlags you need; your VBA code could look like this:

mySession.Open APP_USERID, APP_PWD, APP_TESTDB, Now, DBLinkFlags.ReadWrite

Where mySession is a global object variable, and then wherever APP_USERID comes from is entirely up to you. Hard-code them in the .Open call, make them in-code constants, or make them in-code variables and store the values in your Access database, or in an external xml configuration file, anything works; start by getting hard-coded credentials working, and then figure out a strategy to parameterize them.

Answer 2

This might explain why you're getting prompted each time for login info. I think you're using two opens - but only one is connected with your connxn string.

Does this work any better?

    Dim sagedb As Database 
    Dim rs As Recordset 
    Dim strConnect As String 

    strConnect = "DSN=SageAccountsVer20;uid=xxx;pwd=xxx;" 
    Set sagedb = OpenDatabase("Directory", False, False, strConnect) 
    Set rs = sagedb.OpenRecordset("dbo_authors") 

    Debug.Print rs(0) 
    Debug.Print "Recordset Opened Successfully" 

    rs.Close 
    sagedb .Close 
    Set sagedb = Nothing 

    sagedb.Close
    Set rs = Nothing