How can I store MAS authentication in an Excel ODBC link?

I'm new to MAS, recently hired on to a company using MAS 200 4.40.  I'm trying to utilize some of my experience with querying SQL databases to streamline work and am stumbling over how to avoid typing in my username and password to refresh my data in Excel.  Searching through the forums here has yielded some possibilities but nothing has worked.  Despite making changes to the connection string I still encounter the authentication pop-up.  I've set up a 'dummy' ODBC connection and tried a few different syntax sequences but haven't seen success.  I'm also looking into how to trigger a login routine in VB but am not having much success on that endeavor.

I'm using Excel as my import for several reasons.  One is that the MAS server doesn't seem to let me link tables between modules, which I want to do (the end goal being coordination of inventory purchases based on work orders entered into the system), and in Excel I can show the people using the data where it comes from easily.  Another is that the end users are more familiar with Excel than with Access, so giving them a tool to use that doesn't require learning a new system is preferable.  A third would be my skill in Access; I'm not a database guy but rather someone who has learned bits and pieces over time from exposure.  So don't be surprised if you make a suggestion and I come back with 'how would I go about doing that?'

Thanks in advance for any help you can provide.

Parents
  • 0 in reply to Wayne Schulz

    I created an alias data source (twice, at least) and have tried multiple positions of user ID/password/company identifiers in the string but none seem to work, even on my first refresh.  Much of the discussion I've seen on here has been around using Access to pull data via ODBC, I'm wondering if there is anything specific to the connection string in Excel that I'm missing?  Reading the article you linked to I switched to caps for the user ID, but to no avail.

    I'm trying to avoid entering my credentials 3 times to access data from 3 different tables; I can't seem to organize a query between tables in different modules that will return information in a timely manner.

  • 0 in reply to snowmaker

    While I can't answer your question on eliminating the need to enter your credentials entirely, you can get it to work with only entering your credentials once.  I like to use Crystal to design the SQL statement and then you can cut and paste the statement into the Excel Data Query tool and then you only have to enter your credentials once when you do a data refresh.

  • 0 in reply to snowmaker

    Create a silent DSN with the password in the DSN (per Wayne's link).  Use that DSN as your data source instead of SOTAMAS90.  One DSN per company code... no password prompts, ever.

  • 0 in reply to Tyler Christensen

    Have you had success querying data from multiple modules via Crystal?  I've been trying to link PO#s and SO#s to item codes in an effort to identify when low inventory parts are coming in and when they will need to go out, but any time I ask the server to provide that data set all I get is the spinning wheel.  I can quickly pull those tables into Excel sheets and then merge them within the book, but then I run into the authentication hurdle I prefer not to deal with.  Again, I'm just starting out with MAS and may be missing something really obvious to an experienced user.

  • 0 in reply to Kevin M

    Here's my connection string from the Excel connection:

    DSN=SOTAMAS90_Excel;UID=XXX;PWD=1234;Description=MAS 90 4.0 ODBC Driver;Directory=\\[server]\apps\MAS200\ver44\MAS90;Prefix=\\[server]\apps\MAS200\ver44\MAS90\SY\, \\[server]\apps\MAS200\ver44\MAS90\==\;ViewDLL=\\[server]\apps\MAS200\ver44\MAS90\HOME;Company=001;PWD=1234;LogFile=\PVXODBC.LOG;CacheSize=4;StripTrailingSpaces=1;SERVER=NotTheServer

    You'll see I am using a silent DSN (I called it an alias above) and I'm only accessing the default company (and no, my password isn't actually 1234...)  Is there something that I'm missing/mixing up in this?  As I mentioned, I tried different locations for the UID, PWD, and Company identifiers.  The string above is what the default spit out; I've tried putting the company ID next to my UID also.

  • 0 in reply to snowmaker

    The connection string is created automatically when you use MS Query to initially pull in the data.  As long as you can "test" the DSN fine within the ODBC utility, it should work simply by:

    Data- From Other Sources - MS Query... choose your silent DSN from the list and go from there.  Configured properly there should be no password prompt.

  • 0 in reply to Kevin M

    Starting over from scratch with MS Query and the silent DSN doesn't appear to work.  Every time I refresh the table I'm asked for both the user ID and password.  Which suggests to me that neither one is being saved.  I've saved my info in the 'Logon' tab of the silent DSN setup but that doesn't seem to help either.

    I'm using visual basic to re-write the query based on cell contents each time the table refreshes; perhaps I can use some VB code to authenticate.  Any idea if that is in the realm of possibilities?

  • 0 in reply to snowmaker

    It absolutely does work.  Be sure to test your DSN.

    Start with a new spreadsheet so a Refresh All doesn't also try and refresh a SOTAMAS90 query.

  • 0 in reply to Kevin M

    I'm able to connect to the source no problem, I just can't seem to get around entering my user ID and password every time I try to refresh a table.

    Even when I have the connection string pulled up in the driver setup window and verify my user ID and password are in the string, it asks for authentication when I click on Test Connection.

    I tried the new file-MS Query route; it asked for authentication when I connected to the data source and again when I wanted it to return data.  When I hit Refresh it once again wanted username and password.

    At this point I think I'm going to call it a week.  Thanks Kevin/Tyler/Wayne/Rsmcnamara for your pointers, if I end up with some free time next week (this is a side project) I'll jump back into it.

  • 0 in reply to snowmaker

    Okay, so just after trying 'SOTAMAS90_Excel' I tried a different connection I had created, 'Excel_From_MAS' that I SWEAR is identical in setup.  It ran with the user info and password saved without any problem at all.  Is it possible that because I was using a connection that began with the same name as the default connection it reset itself?  No idea.  I'll try setting up multiple queries with this connection next week as time permits and will report back.

    Thanks again all who gave their input.

  • 0 in reply to snowmaker

    If it ask for log in when you test the connection you have the log in information incorrect.

Reply Children
No Data