Excel VBA - Remember SQL ODBC User/Password

0
votes

First time here, so I hope to get my answer, I looked throughout the Google but I couldn't find the answer.

Well, I got VBA code which refresh a code in SQL on Exel.

VBA code: Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

It's asking everytime my ODBC login/password and I try to refresh manually, and I'd like to run automatically by saving those information on Excel Cells or in VBA directly (I prefer a variable that is linked in excel cells in "Validação de Dados" sheets). And after that, making it better by forcing the user to insert those information in a form (to keep better looking). But the problem is that everytime I tried to run the macro, it gets an error (something like "authenticantion failure").

  • My SQL code in Excel location:

SQL Code Window

  • Asking about my user and login:

User/Login

  • Error getting by not saving user/login:

Error Trying to Use VBA Code

Office version: Microsoft Office 365 Business

Thanks everyone!

2
sqlvbaexcelodbc
you can choose to store the u/p directly in the data connection file where it says "cadeia de conexao". Add UID="user";PWD="pwd" - Scott Holtzman
That could work only with one (or mine) information right ? Because this query will be available for a lot of people, so everyone has their own "user" and "pwd". So I'll create a form and link those information in a "text box" in Excel cells or something else that could work fine. - Guilherme Matheus

2 Answers

0
votes

You should be able to use windows integrated authentication as well if you have an SQL Server. You'd have to add your user in SSMS.

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication?view=sql-server-2017

0
votes

I managed to ask the password from the standard window with this code:

ActiveWorkbook.Worksheets("Base").ListObjects(1).QueryTable.Refresh

"Base" is the name of your worksheet, and ListObjects(1) is if you have only one SQL query.