Accessing Sage 100 Contractor 2016 Database with Microsoft Access 2010

Question

How can the Sage 100 Contractor 2016 (version 20.4.51.0 or greater) databases be accessed using Microsoft Access 2010? 
 I would like to link to the data source by creating a linked table. To do this I have created a Machine Data Source using ODBC in Access 2010. The link tests are successful when I am logged in with my account or with the SQL Administrator account. However, when I attempt to link to a table in the list I get an error. 
 
 It seems like the index being used by Sage is not a valid name, maybe Access does not like the use of a colon or a period. Does anyone to a work around for this? Is there a visual basic reference library that I should be using? 
 FYI: I have used SQL Admin Studio to access the databases and it works well. However, I would like to use MS Access because it is easier to use and familiar to our staff.

Kelcie Jensen · Answer

I ran into the same problem, and ultimately, you have three options: 
 
 You can create a pass through Query to connect to the data. 
 This is done by selecting the v. 20 database (like you've done) and on the query design page click PASS THROUGH . 
 Unfortunately, this makes you connect to the database one time for each query. 
 
 Use Microsoft Query. 
 I had a lot of success doing this directly in excel and selecting Data->New Query->SQL Database . Then enter your SQL Server name and Database name. I grabbed the SQL statement from Access (there were some modifications I had to make like changing double quotes to single quotes) and pasting the SQL into the box under Advanced Options. 
 To 'grab' your SQL, open your Access query in SQL view instead of design view. 
 You'll likely have to make changes to it. I believe searching for t-SQL (the language that the v.20 databases are using) should tell you what you need to know to get your SQL right. 
 
 now your data pulls into Excel and you can create the pivot table. 
 
 Use a third party option. Data-Channel is a new company that's starting to export data from v.20 into formatted Excel spreadsheets. This might be another option for you. 
 
 Hope that helps! 
 Kelcie

Sage Construction & Real Estate

Accessing Sage 100 Contractor 2016 Database with Microsoft Access 2010

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING