I have several query connections that pull data from my Sage 2021 database. I consolidate the queries into one table, so whenever I need to get the most updated data, I have a macro that updates the queries, which leads to needing to type in my password once per query. Is there a way to write a macro that will input server credentials into the login window to streamline the refresh process ?
I understand that I can use Message Boxes to have users input their login id and password to store within a macro. However, I'm completely lost on how I will have excel input that information into the pop up window.
Here's the connection string for the database query if that's helpful:
DSN=SOTAMAS90;Description=MAS 90 4.0 ODBC Driver;Directory=\\****sage2021\SageSoftware\v700\MAS90;Prefix=\\****sage2021\SageSoftware\v700\MAS90\SY\, \\****sage2021\SageSoftware\v700\MAS90\==\;ViewDLL=\\****sage2021\SageSoftware\v700\MAS90\HOME;CacheSize=4;DirtyReads=1;BurstMode=1;StripTrailingSpaces=1;SERVER=NotTheServer
Here's an example refresh code for a query:
Dim ws2 As Worksheet
Set ws2 = Sheets("TotalContainer")
ws2.ListObjects("Table_Query_from_SOTAMAS90").QueryTable.Refresh BackgroundQuery:=False
Here's the pop-up window that appears when refreshing a query. We must also choose our company, but we shortcut this process by pressing 'm' if that helps at all:
If you have any questions or need more information, I'll be happy to tell you. If you don't think it'll be possible, that's good information to know as well. Thank you !
