Is it possible to import data directly into Excel 2007 via ODBC? I realize there have been discussion on this in the past, but I don't know if there is a clear resolution.
Even now, with the 64 bit ODBC driver installed, upon connecting, I get the MAS login screen. After providing login credentials, I get the 'unable to retrieve list of tables from datasource' error.
I do NOT want to use Microsoft query for this - ultimately, I would like to pull data in from MAS via ADODB connection in vba code behind.
Is there another method I might try to make this work properly.
The ODBC driver for any Sage 100 version is READ ONLY. You cannot pull data into MAS via ADODB. Now you may hunt around and find the ProvideX Read-Write ODBC driver but DO NOT use it as it will result in 100% data corruption 100% of the time. The ONLY way to bring data into MAS is with VI or BOI. Note that if you can write VBA code then you should be able to write code and use BOI to do what you want.
In reply to BigLouie:
Thanks for the response BigLouie, but please note that I was asking how to pull data from MAS INTO Excel. (WITHOUT using Microsoft query).
In reply to Justin K:
OK now I see. Microsoft query seems to be an integral part of Excel and I don't see a way to pull data without using it.
So you can't pull data into excel using Data --> From Other Sources-->From Data Connection Wizard?
Only Data --> From Other Sources-->From Microsoft Query?
Unless something changed between 4.5 and 2013, it should work. I use it almost everyday.
Data - From Other Sources - From Microsoft Query
Edited - Sorry misread your post - I believe you do have to use Microsoft Query.
In reply to jnelsonut:
OK thanks. Just going to throw this out there -
What I'm attempting to do is populate a column on a hidden worksheet with a mas vendor list upon opening the workbook. This column would be the datasource for a validation list of a cell on a visible worksheet.
You should be able to do this with a silent DSN. That is a newly created DSN based on the Sage ODBC driver and with the user log in info included.
I would love for that to work, but it seems like no matter what I try - either using a silent DSN or a non-silent DSN (which would prompt me with a the MAS login screen) - with both 32/64 bit odbc drivers, I get the same 'Could not retrieve list of tables from datasource' message.
Sounds like you should use an Access DB instead of Excel. It would be more automatic like you are looking for.
In reply to IBPIT:
I have noticed that the problem seems to occur when the 64 bit version of office is installed, but I don't have the issue with the 32 bit. I think the ODBC driver is the issue as you are using a 64 bit tool to work through a 32 bit ODBC.
© 2015, Sage Software, Inc. All Rights Reserved. Sage energizes the success of businesses and their communities around the world through the use of smart technology and the imagination of our people. Sage has reimagined business and brings energy, experience and technology to inspire our customers to fulfil their dreams. We work with a thriving community of entrepreneurs, business owners, tradespeople, accountants, partners and developers who drive the global economy. Sage is a FTSE 100 company with 14,000 employees in 24 countries. For more information, visit www.sage.com.
Community Terms of Service | Community Guidelines | Blog List | Community Help