Sage 100

Just upgraded a customer with many Excel SOTAMAS90 Queries...which are all now pointing to the prior version of Sage. Is there an easy way to change the connection strings in all the Excel queries or does it need to be done one at a time. TIA

When I've done it, it was one at a time - but I only had less than 5.

I always go into Query edit. Copy the connection string to a text file, make the string changes in the file, then copy and paste back to excel. Verify that that one works, then copy and past to all the other queries.

I had to change them one at a time. It was a real pain. I would be interested to know if we could do this a better way. Cooper

Thank you all. Just needed confirmation before I started down this path.

One can realize a bit of a short cut if you change the connection string to just DSN=SOTAMAS90. Excel will then complete the string automatically from the current SOTAMAS90 settings. Another nice thing about basing one's Excel queries on MS Access pass through queries instead, is that one can code the connection string with just credentials (i.e. ODBC;DSN=SOTAMAS90;UID=usr|ABC;PWD=password ). Path or configuration settings are optional. Whatever is left out will be taken from the ODBC control panel at run time. Your queries will run silently using the SOTAMAS90 settings from the most recently run MAS90. No need to revisit those connection strings unless a password changes.

I had one of these recently, and used a mapped drive to just above the MAS90 folder, so the path could be updated in the future by simply pointing the mapped drive to the new location, without editing the Excel queries again. I like the idea of removing the path entirely though, and will have to try that next time.

If it's a ton of queries in just a few Excel files, you could create a VBA macro to update them. Here's a link for the basics, and I can probably write a search-and-replace wrapper if it's needed. http://stackoverflow.com/questions/20521056/excel-vba-updating-a-connection-string

Thanks Steve. Good to know for next time. I think they copy and pasted all the important ones already