Sage 100

The client's various Excel Data Queries have issues as a result of migrating their Sage 100 from Standard to Advanced (same server), i.e. the Sage folder name has changed.  For all these Excel files the user specifies the date range and then clicks the custom 'Get Data' button in the Excel sheet.  This is apparently a macro that appears to be using a 'File DSN', of which we couldn't determine where that's coming from.  SOTAMAS90 on the computer has the new/correct pathing, but upon running the report the ODBC screen appears which shows the old pathing, so we fix it and run the report.  However, upon trying the same thing again, the old pathing is back in the ODBC screen in Excel.  These Excel files are run by multiple users on multiple computers.  We did check the Registry but no luck, but maybe we need to look again.  It certainly seems like the SOTAMAS90 pathing needs to be edited but we haven't been able to determine where.  And still trying to isolate this as to whether it's computer-specific; waiting on client.

 

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal
------------------------------

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------

Original Message:
Sent: 09-29-2021 10:47
From: Rob Neal
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal
------------------------------

Original Message:
Sent: 9/29/2021 10:56:00 AM
From: Brett Zimmerman
Subject: RE: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------

Did you look inside the button macro to see if it has the connection string hard-coded there?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 09-29-2021 10:56
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------


Original Message:
Sent: 09-29-2021 10:47
From: Rob Neal
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal

@Robert Wood, yes, it appears we can run/refresh a regular data query, but the 'Get Data' button/macro is tripping me up as I don't have much familiarity with that.
@Kevin Moyes​​, we tried looking inside the button/macro for any applicable hard-coded info, but didn't see anything.  Maybe I wasn't looking properly?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------

Original Message:
Sent: 09-29-2021 11:02
From: Kevin Moyes
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Did you look inside the button macro to see if it has the connection string hard-coded there?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 09-29-2021 10:56
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 10:47
From: Rob Neal
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal

Thanks, all.  I think we're now all set.  Will provide further info shortly.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------

Original Message:
Sent: 09-29-2021 11:46
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

@Robert Wood, yes, it appears we can run/refresh a regular data query, but the 'Get Data' button/macro is tripping me up as I don't have much familiarity with that.
@Kevin Moyes​​, we tried looking inside the button/macro for any applicable hard-coded info, but didn't see anything.  Maybe I wasn't looking properly?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------


Original Message:
Sent: 09-29-2021 11:02
From: Kevin Moyes
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Did you look inside the button macro to see if it has the connection string hard-coded there?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-29-2021 10:56
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 10:47
From: Rob Neal
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal

resolution, incl the normal steps:

1. open Excel data query file.
2. if present, click 'enable content' on yellow security warning near top.
3. Right-click on 'get data' button/macro, select 'assign macro', and then click edit on the screen that appears.
4. Edit the four instances of the sage 100 path near top, using UNC, e.g. \\abcsage\sage\Sage 100 Advanced\MAS90\ .  'Save' before exiting.
5. Then 'Save' the excel file back at the main Excel screen.
6. Click 'get data' button, and specify the company and logon credentials.
7. Once it finishes compiling, it saves and closes the excel file, and brings you back to essentially a blank excel screen.  Close it.
8. Upon re-opening the saved excel file, the 'get data' button should work as expected going forward.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------

Original Message:
Sent: 09-29-2021 13:14
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, all.  I think we're now all set.  Will provide further info shortly.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------


Original Message:
Sent: 09-29-2021 11:46
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

@Robert Wood, yes, it appears we can run/refresh a regular data query, but the 'Get Data' button/macro is tripping me up as I don't have much familiarity with that.
@Kevin Moyes​​, we tried looking inside the button/macro for any applicable hard-coded info, but didn't see anything.  Maybe I wasn't looking properly?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:02
From: Kevin Moyes
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Did you look inside the button macro to see if it has the connection string hard-coded there?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-29-2021 10:56
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 10:47
From: Rob Neal
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal

I believe the answer is 'no', but is there a way to get the SOTAMAS90 ODBC on a computer without performing the wkstn setup?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------

Original Message:
Sent: 09-29-2021 14:09
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

resolution, incl the normal steps:

1. open Excel data query file.
2. if present, click 'enable content' on yellow security warning near top.
3. Right-click on 'get data' button/macro, select 'assign macro', and then click edit on the screen that appears.
4. Edit the four instances of the sage 100 path near top, using UNC, e.g. \\abcsage\sage\Sage 100 Advanced\MAS90\ .  'Save' before exiting.
5. Then 'Save' the excel file back at the main Excel screen.
6. Click 'get data' button, and specify the company and logon credentials.
7. Once it finishes compiling, it saves and closes the excel file, and brings you back to essentially a blank excel screen.  Close it.
8. Upon re-opening the saved excel file, the 'get data' button should work as expected going forward.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------


Original Message:
Sent: 09-29-2021 13:14
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, all.  I think we're now all set.  Will provide further info shortly.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:46
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

@Robert Wood, yes, it appears we can run/refresh a regular data query, but the 'Get Data' button/macro is tripping me up as I don't have much familiarity with that.
@Kevin Moyes​​, we tried looking inside the button/macro for any applicable hard-coded info, but didn't see anything.  Maybe I wasn't looking properly?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:02
From: Kevin Moyes
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Did you look inside the button macro to see if it has the connection string hard-coded there?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-29-2021 10:56
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 10:47
From: Rob Neal
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal

See the Attached Sage KB article.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------

Original Message:
Sent: 09-29-2021 14:17
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

I believe the answer is 'no', but is there a way to get the SOTAMAS90 ODBC on a computer without performing the wkstn setup?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area
------------------------------


Original Message:
Sent: 09-29-2021 14:09
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

resolution, incl the normal steps:

1. open Excel data query file.
2. if present, click 'enable content' on yellow security warning near top.
3. Right-click on 'get data' button/macro, select 'assign macro', and then click edit on the screen that appears.
4. Edit the four instances of the sage 100 path near top, using UNC, e.g. \\abcsage\sage\Sage 100 Advanced\MAS90\ .  'Save' before exiting.
5. Then 'Save' the excel file back at the main Excel screen.
6. Click 'get data' button, and specify the company and logon credentials.
7. Once it finishes compiling, it saves and closes the excel file, and brings you back to essentially a blank excel screen.  Close it.
8. Upon re-opening the saved excel file, the 'get data' button should work as expected going forward.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 13:14
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, all.  I think we're now all set.  Will provide further info shortly.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:46
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

@Robert Wood, yes, it appears we can run/refresh a regular data query, but the 'Get Data' button/macro is tripping me up as I don't have much familiarity with that.
@Kevin Moyes​​, we tried looking inside the button/macro for any applicable hard-coded info, but didn't see anything.  Maybe I wasn't looking properly?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:02
From: Kevin Moyes
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Did you look inside the button macro to see if it has the connection string hard-coded there?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-29-2021 10:56
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 10:47
From: Rob Neal
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal

Anyone know what this means?  Trying to download Doug's PDF and am being blocked.



------------------------------
Jeff Schwenk
FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------

Original Message:
Sent: 09-29-2021 14:23
From: Doug Higgs
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

See the Attached Sage KB article.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------


Original Message:
Sent: 09-29-2021 14:17
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

I believe the answer is 'no', but is there a way to get the SOTAMAS90 ODBC on a computer without performing the wkstn setup?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 14:09
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

resolution, incl the normal steps:

1. open Excel data query file.
2. if present, click 'enable content' on yellow security warning near top.
3. Right-click on 'get data' button/macro, select 'assign macro', and then click edit on the screen that appears.
4. Edit the four instances of the sage 100 path near top, using UNC, e.g. \\abcsage\sage\Sage 100 Advanced\MAS90\ .  'Save' before exiting.
5. Then 'Save' the excel file back at the main Excel screen.
6. Click 'get data' button, and specify the company and logon credentials.
7. Once it finishes compiling, it saves and closes the excel file, and brings you back to essentially a blank excel screen.  Close it.
8. Upon re-opening the saved excel file, the 'get data' button should work as expected going forward.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 13:14
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, all.  I think we're now all set.  Will provide further info shortly.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:46
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

@Robert Wood, yes, it appears we can run/refresh a regular data query, but the 'Get Data' button/macro is tripping me up as I don't have much familiarity with that.
@Kevin Moyes​​, we tried looking inside the button/macro for any applicable hard-coded info, but didn't see anything.  Maybe I wasn't looking properly?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:02
From: Kevin Moyes
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Did you look inside the button macro to see if it has the connection string hard-coded there?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-29-2021 10:56
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 10:47
From: Rob Neal
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal

Works for me. Must be something in your Signature! ;-)

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

Original Message:
Sent: 09-29-2021 14:30
From: Jeff Schwenk
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Anyone know what this means?  Trying to download Doug's PDF and am being blocked.



------------------------------
Jeff Schwenk
FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------


Original Message:
Sent: 09-29-2021 14:23
From: Doug Higgs
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

See the Attached Sage KB article.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles


Original Message:
Sent: 09-29-2021 14:17
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

I believe the answer is 'no', but is there a way to get the SOTAMAS90 ODBC on a computer without performing the wkstn setup?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 14:09
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

resolution, incl the normal steps:

1. open Excel data query file.
2. if present, click 'enable content' on yellow security warning near top.
3. Right-click on 'get data' button/macro, select 'assign macro', and then click edit on the screen that appears.
4. Edit the four instances of the sage 100 path near top, using UNC, e.g. \\abcsage\sage\Sage 100 Advanced\MAS90\ .  'Save' before exiting.
5. Then 'Save' the excel file back at the main Excel screen.
6. Click 'get data' button, and specify the company and logon credentials.
7. Once it finishes compiling, it saves and closes the excel file, and brings you back to essentially a blank excel screen.  Close it.
8. Upon re-opening the saved excel file, the 'get data' button should work as expected going forward.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 13:14
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, all.  I think we're now all set.  Will provide further info shortly.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:46
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

@Robert Wood, yes, it appears we can run/refresh a regular data query, but the 'Get Data' button/macro is tripping me up as I don't have much familiarity with that.
@Kevin Moyes​​, we tried looking inside the button/macro for any applicable hard-coded info, but didn't see anything.  Maybe I wasn't looking properly?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:02
From: Kevin Moyes
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Did you look inside the button macro to see if it has the connection string hard-coded there?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-29-2021 10:56
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 10:47
From: Rob Neal
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal

Check this out.....  Thank you @George Khairallah

https://community.90minds.com/communities/community-home/digestviewer/viewthread?GroupId=13&MessageKey=3fda1af5-2225-4209-88b5-b99696b2f790&CommunityKey=4593d843-4557-43ff-ba4d-6240681ed696&tab=digestviewer&ReturnUrl=%2fresources%2fallrecentposts


​

------------------------------
Jeff Schwenk
FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------

Original Message:
Sent: 09-29-2021 15:53
From: Dan Burleson
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Works for me. Must be something in your Signature! ;-)

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------


Original Message:
Sent: 09-29-2021 14:30
From: Jeff Schwenk
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Anyone know what this means?  Trying to download Doug's PDF and am being blocked.



------------------------------
Jeff Schwenk
FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
Bottomline Software, Inc.
Waynesboro VA
540-221-4444


Original Message:
Sent: 09-29-2021 14:23
From: Doug Higgs
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

See the Attached Sage KB article.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles


Original Message:
Sent: 09-29-2021 14:17
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

I believe the answer is 'no', but is there a way to get the SOTAMAS90 ODBC on a computer without performing the wkstn setup?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 14:09
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

resolution, incl the normal steps:

1. open Excel data query file.
2. if present, click 'enable content' on yellow security warning near top.
3. Right-click on 'get data' button/macro, select 'assign macro', and then click edit on the screen that appears.
4. Edit the four instances of the sage 100 path near top, using UNC, e.g. \\abcsage\sage\Sage 100 Advanced\MAS90\ .  'Save' before exiting.
5. Then 'Save' the excel file back at the main Excel screen.
6. Click 'get data' button, and specify the company and logon credentials.
7. Once it finishes compiling, it saves and closes the excel file, and brings you back to essentially a blank excel screen.  Close it.
8. Upon re-opening the saved excel file, the 'get data' button should work as expected going forward.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 13:14
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, all.  I think we're now all set.  Will provide further info shortly.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:46
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

@Robert Wood, yes, it appears we can run/refresh a regular data query, but the 'Get Data' button/macro is tripping me up as I don't have much familiarity with that.
@Kevin Moyes​​, we tried looking inside the button/macro for any applicable hard-coded info, but didn't see anything.  Maybe I wasn't looking properly?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:02
From: Kevin Moyes
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Did you look inside the button macro to see if it has the connection string hard-coded there?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-29-2021 10:56
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 10:47
From: Rob Neal
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal

RE my note above about downloading difficulties.  Palm face plant.  Found the downloaded files - all 14 of them.........

------------------------------
Jeff Schwenk
FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------

Original Message:
Sent: 10-05-2021 17:41
From: Jeff Schwenk
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Check this out.....  Thank you @George Khairallah

https://community.90minds.com/communities/community-home/digestviewer/viewthread?GroupId=13&MessageKey=3fda1af5-2225-4209-88b5-b99696b2f790&CommunityKey=4593d843-4557-43ff-ba4d-6240681ed696&tab=digestviewer&ReturnUrl=%2fresources%2fallrecentposts


​

------------------------------
Jeff Schwenk
FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------


Original Message:
Sent: 09-29-2021 15:53
From: Dan Burleson
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Works for me. Must be something in your Signature! ;-)

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642


Original Message:
Sent: 09-29-2021 14:30
From: Jeff Schwenk
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Anyone know what this means?  Trying to download Doug's PDF and am being blocked.



------------------------------
Jeff Schwenk
FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
Bottomline Software, Inc.
Waynesboro VA
540-221-4444


Original Message:
Sent: 09-29-2021 14:23
From: Doug Higgs
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

See the Attached Sage KB article.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles


Original Message:
Sent: 09-29-2021 14:17
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

I believe the answer is 'no', but is there a way to get the SOTAMAS90 ODBC on a computer without performing the wkstn setup?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 14:09
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

resolution, incl the normal steps:

1. open Excel data query file.
2. if present, click 'enable content' on yellow security warning near top.
3. Right-click on 'get data' button/macro, select 'assign macro', and then click edit on the screen that appears.
4. Edit the four instances of the sage 100 path near top, using UNC, e.g. \\abcsage\sage\Sage 100 Advanced\MAS90\ .  'Save' before exiting.
5. Then 'Save' the excel file back at the main Excel screen.
6. Click 'get data' button, and specify the company and logon credentials.
7. Once it finishes compiling, it saves and closes the excel file, and brings you back to essentially a blank excel screen.  Close it.
8. Upon re-opening the saved excel file, the 'get data' button should work as expected going forward.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 13:14
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, all.  I think we're now all set.  Will provide further info shortly.

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:46
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

@Robert Wood, yes, it appears we can run/refresh a regular data query, but the 'Get Data' button/macro is tripping me up as I don't have much familiarity with that.
@Kevin Moyes​​, we tried looking inside the button/macro for any applicable hard-coded info, but didn't see anything.  Maybe I wasn't looking properly?

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 11:02
From: Kevin Moyes
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Did you look inside the button macro to see if it has the connection string hard-coded there?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-29-2021 10:56
From: Brett Zimmerman
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Thanks, @Rob Neal!  Yes, we have tried that also but it doesn't seem to help, unless I'm not doing it properly (e.g. in the wrong sequence).​

------------------------------
Brett Zimmerman
Net at Work
Greater Boston Area


Original Message:
Sent: 09-29-2021 10:47
From: Rob Neal
Subject: Excel Data Query issue - Sage 100 pathing, ODBC, File DSN

Are you able to edit the connection strings in the table properties? 

------------------------------
Rob Neal