SAGE ODBC

[owen_legend]

Hey Guys,

Just a quick (but apparently complicated one).

I connect SAGE to access through linked tables and run queries on data which are then called from Excel.

So Excel connects to Access which connects to SAGE.

The issue is that whenever I refresh the query in Excel it requests the ODBC user name and password every time which isn't ideal for when I need to have other users connect - I can't give out the SAGE login details really.

If i run queries in excel directly to SAGE - I can put the user name and password in the connection string and "remember password" so that Windows inputs the credentials for me automatically on refresh.
However this doesn't seem as simple in Access.

I have tried using VBA and create a module with the following code:

Code: Select all

Sub PrintTabledef()


Dim tdf As TableDef
Dim db As Database

    Set db = CurrentDb

    For Each tdf In CurrentDb.TableDefs
        If tdf.Connect <> vbNullString Then
           Debug.Print tdf.Name; " -- "; tdf.SourceTableName; " -- "; tdf.Connect
        End If
    Next
End Sub

Sub ChangeConn()


    Set db = CurrentDb
    Set tdf = db.TableDefs("TableName")
    tdf.Connect = "ODBC;DSN=SageLine50v22;UID=xxxx;PWD=xxxx;"


End Sub

So the first code (PrintTabledef()) prints the connection strings and the second code (ChangeConn()) is meant to amend them - but it doesn't - the code executes but I guess on refresh it removes the credentials.


Is there any other way around this? Can I get Access to "remember" credentials and automatically input them?

[brucedenney]

VBA is the way to embed credentials.