SageForum for UK versions of Sage 50- Help and support.

Hello,

I am trying to connect a Sage v28 ODBC Data Source to the On-Premises Data Gateway, so I can automatically schedule data refreshes on Power BI.

What is the correct syntax to write out this Connection String. The options I have tried all do not work (Status code 400 is displayed, which means connection is invalid). For security reasons I have replaced several Connection String elements below with XX, but I will explain what data I used for each so you know I am using the right thing.

"Driver=Sage Line 50 v28;ServerDSN=XX;Uid=XX;Pwd=XX;"
"DSN=XX;Driver=Sage Line 50 v28;UID=XX;PWD=XX;DIR=XX;"

DSN/ Server DSN: This is the Name/ Data Source Name, under [System Data Sources] within ODBC Data Source Administrator.
DIR: This is the Data Path, found when you click into a data source, under [System Data Sources] within ODBC Data Source Administrator.
UID: User ID used to login to Sage Company which you have the ODBC connection for.
PWD: Password used to login to Sage Company which you have the ODBC connection for.

Regards,
Ryan

This is what I use in Office VBA to build the string

ConnectionString = "ODBC;DSN=SageLine50v" & DN & ";UID=" & UR & ";PWD=" & PW & ";DIR=" & DR & ";"

DN is the Sage version
UR is the UserName
PW is the Password
DR is the path to the data folder including ACCDATA at the end.