Trouble connecting to Sage 50 with Excel via ODBC

Question

I'm trying to get into Sage 50 (v20 2014) via ODBC in Excel 2016 64bit. I keep running into connection problems.

I've set up a 64bit DSN using the utility on the Sage disk and I get the following...

• If I go through 'Data/From other sources/From Microsoft Query' I get the usual 'Choose data source' box and the Sage DSN is visible. If I click it and click ok MS Query launches and then crashes immediately and I get 'Microsoft Query has stopped working'. Interestingly, if I go back to the 'Choose Data Source' box, I get the error 'One or more of the folders in the search path could not be accessed'.

The default path set in the DSN is to 'C:\ProgramData\Sage\Accounts\2014\Company.000\ACCDATA' - the actual Sage database that I'm trying to access is in a network folder - 'V:\SHARED\Sage\COMPANY.002\ACCDATA'. I've tried both paths in the DSN and get the same results.

• If I go through 'Data/New Query/From Other Sources/From ODBC' and then choose the Sage DSN, I get a full list of the tables but when I click on one of them to generate a preview, I get the following error...

  DataSource.Error:ODBC:ERROR[42S02] Table not found
  
  Details:
  
  DataSourceKind=Odbc
  DataSourcePath=dsn=SageLine50v20
  OdbcErrors=Table
  

Also, if I use 'From data Connection Wizard', Excel crashes as soon as I choose the Sage DSN.

I'm not using any kind of connection string so I don't think it's authenticating with a user - I'm wondering if that would make a difference and if so, what would the connection string be?

I've been through everything that comes up if I google the problem and I've set up logins correctly etc but it will not connect. I don't know if this might be a 32/64bit comparability issue, but I'm using the 64bit Sage driver from the disk so I assumed that it wouldn't be a problem. I've also tried connecting with MS PowerBI but I get the same problems.

Answer 1

Sage 50 is temperamental in how it allow connections to their database, what I mean: When it don't change the port, it just reject connections in awkward ways or not connect at all.

I didn't find a solution. However I found this workaround: Open sage, login and close it then connect using the DSN sage created with the sysadmin account(It grants you full access).

Answer 2

When defining the data path, it is critical to include a final backslash e.g C:\ProgramData\Sage\Accounts\2018\COMPANY.001\ACCDATA\