SAGE ODBC
Posted: 25 Feb 2016, 11:30
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:
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?
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 SubIs there any other way around this? Can I get Access to "remember" credentials and automatically input them?