ODBC Connection

[Bluebird93]

Hello,

I have an Excel 2013 file that has multiple ODBC connections that I am trying to save the user ID and password with and am having problems. I've tried a number of things, but no luck so far.

Any suggestions would be appreciated.

[dflak]

The way to do it is go to the Data tab and select Queries & Connections, then right click the connection and select properties. On the definition tab, there is a checkbox to Save Password. A word of warning: the password is saved in clear text in the connection string. So, it would be a good idea NOT to share the workbook with people you cannot trust to have the password.

I have some users who use workbooks that have multiple queries, all to the same database. I have some code that prompts them for their password, does the query and then removes the password.

[Bluebird93]

Hi,

Thanks for your reply. I tried that and the user ID and password are not being retained. Any other suggestions?

[dflak]

Look at your Privacy Settings. File -> Options -> Trust Center -> Trust Center Options -> External Content. The middle radio buttons should be selected.

[Bluebird93]

There is a Data Connections and Workbook Links and both are set to the middle radio button (Prompt user).

[dflak]

Can you post what the connection string looks like. I might be able to buy into not showing the password, but not having the user ID doesn't make sense.

What happens when you run the query? Does it prompt you for a user name and password and does it run properly?

[Bluebird93]

Here is the connection string.
DSN=SOTAMAS90;Description=MAS 90 4.0 ODBC Driver;Directory=E:\Data\ERP\Sage 100 Advanced v2017\MAS90;Prefix=E:\Data\ERP\Sage 100 Advanced v2017\MAS90\SY\, E:\Data\ERP\Sage 100 Advanced v2017\MAS90\==\;ViewDLL=E:\Data\ERP\Sage 100 Advanced v2017\MAS90\Home\;LogFile=\PVXODBC.LOG;RemotePVKIOHost=10.20.2.5;RemotePVKIOPort=20222;CacheSize=4;DirtyReads=1;BurstMode=1;StripTrailingSpaces=1;SERVER=NotTheServer

Yes, it prompts for the user name and login. Also the specific company code in the software that I am using the ODBC connection with. Maybe that is why?

[dflak]

This looks similar to an Access connection string in that it goes to a specific folder or drive. Unlike the Oracle Drivers I'm used to working with, there appears to be no place in the string where the user ID and password are stored.

The company code is apparently a parameter for the query, so this connection string must be passing the credentials and parameters to another program that interfaces with the database. That's the program we'd have to get into.

You can try recording a macro, but I doubt it will yield useful information. Excel most likely won't be able to "see through" to the intermediate program, but then again it might give insight on how to call it and pass parameters to it.

I don't suppose you have a user's manual that gives command line options.

We could possibly accomplish this with SendKeys to emulate what you would type from the keyboard, but that could be a messy solution.

I am afraid I can't help.

[Bluebird93]

I had tried creating a macro also and it doesn't store the info either. Nothing useful in the scripting either.

Thanks for trying. Really appreciate the efforts!