Sage 100

Can anyone share with me the ""magic"" of setting up MAS 90 4.0 ODBC Drivers on a Windows 7 machine? I'm sure it's easy and I'm asking a stupid question but I'm trying to plow through a pile of junk and this is in the way. Thanks in advance!

It should be the same. If it's a 64-bit machine you'll need to use the 32-bit ODBC in the ..\system\syswow64 folder and locate odbc32.exe.

Thanks, Bill! I looked at it on my personal machine and found ODBCAD32.EXE in the c:\Windows\SysWOW64 folder. After double clicking, the old familiar 32 bit ODBC admin came up and allowed me to create a new User DSN. Woo-Hoo!

Performing a Workstation Setup is usually the easiest way to get it. But is that not an option for you?

Brett, in setting up Access pass through queries for reports accessing multiple companies, I have to set up a different DSN for each company. Wksetup is not an option in this instance.

That's why, Stanley, it's so important for the original poster to form a complete question with details which is helpful to members and prevents us from offering off-topic suggestions which can quickly clog the replies.

Joe Bob, caught the ID error just as you posted your note to Stanley.

Brett, Jeff is right. We need an ODBC setup that points to a particular company with a particular login so that an Access database can be easily populated. I use this very often when customizing certain data access.

Alls well that ends well. Getting familiar with the locations on a WIN 7 machine takes a little getting used to.

This might be hlpful. Here is the document I hand out to set this up.

Thanks Myron. Excellent explanation. Another ""twist"" to this issue is when you are using Access PTQ's to do the heavy lifting (10X faster than the MAS ODBC connection in Crystal), you don't need a separate ODBC for each company. Simply by changing the company for the ODBC connection string used in that PTQ property (SIE below), you can use the SOTAMAS90 driver. ODBC;DSN=SOTAMAS90;UID=JAS|SIE;PWD=XXXXXX;Directory=\\Pcs5037\g\Sage\MAS\v45\MAS90\;EnforceDouble=1;StripTrailingSpaces=1;SILENT=1;SERVER=NotTheServer A couple caveats - To use the same Access database for multiple companies, you have to set up separate PTQ's and change the connection string to the appropriate company for each one. Second - If are pulling data from multiple companies (e.g. creating a consolidated report spanning several companies using a UNION query), you have to create a separate ODBC for each company (as outlined in the document). To use in the connection string, swap out SOTAMAS90 with the name of the ODBC that you want to use.. For those that find Crystal reports take FOREVER when pulling data from large tables (e.g. AR_InvoiceHistoryDatail and Header), you can improve the response time by a factor of 10 simply by linking the Crystal report to an Access database that has a Pass Through Query defined for each MAS table. At one client, the Crystal report chugged for 65 minutes every Monday. By simply inserting an intermediate Access DB on the server, the report now runs in SIX minutes! And the report is displayed under the Custom Reports menu on the MAS desktop so the client doesn't need to have Crystal Designer to generate the report. It is NOT necessary to have a copy of Access on the server although it is helpful for debugging the report. You ONLY need the Access ODBC driver installed. This method IS a little labor intensive to set up but once done, it works very well. If you have a LOT of table to map, contact Dan Burleson for his MAS Mirror program. And of course, if your report requirements are massive, contact DSD for their SQL Mirroring product.

@WayneSchulz . One more thing to remember about running the 32 bit ODBC Data Source Administrator, since this is Windows 7 all you need to do is type the file name (odbcad32.exe) in the search field on the start menu. Trying to locate it in the SysWOW64 folder isn't necessary.

@ChuckPeddy, When I launch odbcad32 from the run window, I get an error when I attempt to edit the existing SOTAMAS90 User DSN. If I manually navigate to the C:\Windows\SysWOW64 folder and launch the applet from there, I can edit the DSN without issue. Am I missing something?

If you just go to Run and put in odbcad32 it brings up the 64-bit ODBC by default. You have to specify the syswow64 folder in the run command or pick it from there to bring up the 32-bit. Or, create yourself a shortcut.

That is what I thought but wanted to make sure I wasn't missing something. Thanks @DawnAnastasi

Thanks everyone for the great tips...Especially @JeffSchwenk for the great insight on the Access PTQ's. I didn't realize you could use those and still have the report run from the custom reports menu in MAS.

It has always baffled me that Sage has done nothing in the way of development to improve their ODBC data query functions in MAS.

I've always thought of Sage as ""one and done"". Once a feature ships they tend to leave it alone except for bug fixes or if they believe there's something quick they can add to it.

Wayne's evernote link isn't working for me. But then maybe it is just me. Can someone verify that it is working for them?

Access PTQ's also work with KnowledgeSync if you get the Access DB instead of the MAS version. The KLF's that come with the MAS version from my understanding are just predefined Access queries. By getting the Access version, you can use PTQ's for other databases and save the client some money (or you time because you don't have to learn multiple database (if that is an issue). We also use UNION queries to generate consolidated company reports (e.g. AR agings).

If anyone is interested, I would be happy to do a webinar for the group on Excel Pivot Tables and Access PTQ's and Crystal reporting. Need about five to sign up to make it a go.

Link in working for me.

It worked for me after I left it alone for a while - not sure how long as I did it soon after he posted it then forgot about it until now as I was working on something else...

I'd be interested @JeffSchwenk - I am actually working on KnowledgeSync reporting at this very moment....

Patience is not my strong suit. But it worked in this case when I launched the link in Mozilla.

Link worked for me.

@JeffSchwenk I would be interested in your webinar.

Actually, I would rather not advertixe it outside the group. This ""trick"" falls into the ""secret sauce"" category. Once everyone knows about it, we lose some of our edge. You have to be a techie to implement it so for many end users, knowing about it wouldn't work by itself. And if people have a need for speed and have budget for it, bring in the DSD big gun and just get it over with. Wayne, I noticed you posted my comments in the evernote article. If it is viewable outside the group, then I would appreciate it if you would delete the comments. I don't mind shaing the secret sauce with the group, but not with anyone outside of the group.

No problem - Evernote is now private. Sorry.

No worries. Just have to protect the secret sauce a la McD's.................. Intangibles are all we have to market!

I would also be interested in the webinar @JeffSchwenk

Last call for webinar registrations. As per Sage guidelines, the early bird registration fee is not longer available. Sign up by Wednesday to ensure a seat a the conference table! Time and date TBA.

I'm still in @JeffSchwenk

I am still in @JeffSchwenk , would like an invitation. Will you also show us how to make a million dollars in real estate so we can wear Hawaiian shirts and flip flops year round?

I'm curious, too. Sign me up @jeffschwenk.

Looking for the ""Secret Sauce"" I'm in also :)

Sign me up as well, @JeffSchwenk

Please sign me up, too.

And me, please.

OK, I've set up a Webinar for NEXT Tuesday (7/24) at Noon (Eastern) for a one hour Go To Meeting Webinar (compliments of @ThereseLogeais) - Session is more on the idea side of things demonstrating simple PTQ's linked to various MAS tables (I will show Select and Union queries also, but you can't get technical with them). I'll then show you some Crystal reports that are linked to these tables. If time permits, I'll show you how an Access PTQ is used with KnowledgeSync instead of using the linking tools that KS provides with for MAS. To register for the webinar, click on the link below. https://www3.gotomeeting.com/register/926413318

Thanks Jeff - I just signed up!

Since @ThereseLogeais is hosting the webinar, she is working on getting free movie tickets and popcorn for the event. Keep your fingers crossed.........

Thanks Jeff. I need all the help I can get.

We could do movie and popcorn (along with a pop/'soda') if you all wanted to come to Minneapolis!

Thanks @JeffSchwenk and @ThereseLogeais

Here is a thought. Sort of a compromise. I'll play a DVD prior to the start of the webinar and finish it after the main event. Pop and Popcorn will be up to the individual participants.. In thinking more about this, I've decided to honor Therese by playing ""A Prairie Home Companion"" I know she has seen it several times, but it DOES seem to grow on you.

While I am a MN native, Garrison Keillor is not one of my favorites. My mother-in-law was on it once at the MN State Fair. She has worked at a church diner their forever and he asked her to be on his show broadcast from the fair. Oh well...

Not so much of an honor then?

@JeffSchwenk yes to the webinar!

@MoiraGoggin be sure to register!

@ThereseLogeais - just did thank you Therese and thank you for hosting too!

Time to put this back to the top. Tuesday is just a couple days away and there is still time to sign up. OK, I've set up a Webinar for NEXT Tuesday (7/24) at Noon (Eastern) for a one hour Go To Meeting Webinar (compliments of @ThereseLogeais) - Session is more on the idea side of things demonstrating simple PTQ's linked to various MAS tables (I will show Select and Union queries also, but you can't get technical with them as I am not a techie). I'll then show you some Crystal reports that are linked to these tables. If time permits, I'll show you how an Access PTQ is used with KnowledgeSync instead of using the linking tools that KS provides with for MAS. To register for the webinar, click on the link below. https://www3.gotomeeting.com/register/926413318 If you previously replied that you would like to attend but haven't registered via the above link, please do so now.

About 18 hours and counting down until the PTQ webinar. There are still seats left in the classroom for those that haven't signed up yet. To register for the webinar, click on the link below. https://www3.gotomeeting.com/register/926413318

Just wanted to say thanks to @JeffSchwenk and @ThereseLogeais for a great webinar.

+1 to that

Ditto. @JeffSchwenk, you make it look much easier than it really is.

Ditto. Very informative.

Yes! Big thanks!

All the thanks go to @JeffSchwenk for putting his demo together. All I did was create the event and then figure out how to unmute him! Did you like the title (The Bottom Line on Using Pass Through Queries) and that I added his logo and picture to the wait screen? You're welcome, Jeff!

Big thanks to @ThereseLogeais and @JeffSchwenk for putting this all together....it was EXTREMELY valuable information. I enjoyed Jeff's usual wit as well. If I were going to Summit this year I would buy both of you drinks. I may have to find a proxy to do it for me. THANKS AGAIN!

Thanks Jeff and Therese for putting this together.

@BrianBuck - A Visa gift card would deliver the same sentiment. @ThereseLogeais and I could hoist a couple (yes, only a couple as it IS the Gaylord) for you. A BIG shout out for @ThereseLogeais for donating the use of her Go To Meeting link. And a big thank you to all who put up with the 20 minutes (or so) of me talking to myself. It reminded me of dinner with teenagers......... Please don't hesitate to contact me if you have any questions on the PTQ options. It REALLY is easier than it looks as are pivot tables!!!! As a take away for those that signed the CPE attendance form and left it with the proctor at the door, I created a simple starter kit consisting of a PTQ mdb (AR_HistoryHeader and AR_HistoryDetail) and a Crystal report that pulls data through the mdb. The Crystal Report will run as Custom Report menu item. Please send me an e-mail if you would like to take these two files on a test drive.

@JeffSchwenk, while playing with your above posting, I added a company parameter into your connection string and was able to connect directly to a specific company using the SOTAMAS90 DSN. I added the parameter COMPANY=XYZ; directly in front of the UID parameter. If this proves out with additional testing, it could further reduce the setup overhead by not having to create an ODBC definition for each company. Just a thought. Thanks for the insight your posting provides. It really came in handy in a pinch.

Did you attend the PTQ session last month? All attendees received a sample PTQ mdb and a Crystal report. In the mdb, the ODBC strings had company, user and PW defined. Yes, by defining this information in the string, you can put multiple companies in multiple PTQ's (company per PTQ unless there is another way) with ONE sotamas ODBC. HOWEVER, if you doing union queries to consolidate data from multiple companies into one report (consolidated Aging report), you MUST set up a separate ODBC for each company for the union to work correctly.

@JeffSchwenk, I was unable to make the session, much to my chagrin. Thank you for the clarification on when to use and not use an embedded company reference. Your example is easy to understand. I really appreciate you openness to help.