Set up ODBC connection for SAGE on network

Hello,

I am trying to set up an ODBC connection between Excel and SAGE Line 50. SAGE is stored on the network. However just confused as to what information is required in stage 2 of the pop up below (User Name and Password). Can anybody help ? Thanks

Kind Regards

Kaps
SAGE-odbc.png
Kap68Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James ElliottManaging DirectorCommented:
If your Sage application requires a Username & Password to access it then you can enter yours here.

If Sage just opens based on your windows credentials (LDAP), or indeed, no credentials at all, then you can just leave this blank.
Kap68Author Commented:
Thanks. So it is a user name / password issue ?

 We have tried that and got an error. We have tried another machine and although ODBC DSN was an option we could not see SAGE as an option (even though it was open in the background).

Any thoughts ? Much appreciated.
Kap68Author Commented:
I have discovered that the PC I am using is a 64 bit machine and I understand that SAGE is a 32 bit application. How does this affect things ? Thanks in advance

Kaps
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Bruce DenneySage 50 Consultant and IntegratorCommented:
Only if you have installed the 64bit version of Office, and most people don't as there are warnings about compatibility.

Looks like this is the UK / ROW version rather than the US version.

You may still have issues as there is a requirement for a data path as well unless it is the default path you are using.

I would create a user dsn and that will allow you to put the path in.

You do not have to put the username and password in the DSN, you can have the Driver prompt you when you refresh in excel.

I have a little VBA that updates the DSN and refreshes it as when you upgrade you will need to change paths and driver versions and this makes it easy.
Kap68Author Commented:
Thank you Bruce (as ever). just checked wehave a 32bit office on a 64 bit machine. So any thoughts on why we cannot connect SAGE to Excel via ODBC ?
Bruce DenneySage 50 Consultant and IntegratorCommented:
Yes as per my previous post, I suspect you have not set up the data path it should end in \accdata
Kap68Author Commented:
Thanks. we don't seem to be able to add another user dsn. I've tried going in via msquery tool and get the error below.  

any thoughts ? Thanks in advance.
sage.png
Bruce DenneySage 50 Consultant and IntegratorCommented:
I have made a video to walk you through it, enjoy!

https://www.screenr.com/mS6N

https://www.youtube.com/watch?v=9d-2-IyYBM8

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kap68Author Commented:
Thanks - for some reason I cannot locate the SAGE data directory. I've shown a copy of the help screenshot below:-
sage2.png
Bruce DenneySage 50 Consultant and IntegratorCommented:
V2010 is pretty old.

In that version you click on directories, and then data directory will be on the right hand pane right clicking on the path gives you an option to open folder.

directory
Kap68Author Commented:
Thank You, I will try in the morning when I am back on client site.
Kap68Author Commented:
Video explained EVERYTHING !! This man is a true SAGE !!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.