Username and Password SQL Connection

[davie1982]

Hi

Basically, i have an ODBC query and i would like to hardcode the server username and password into VBA.

Code:
--------------------------------------------------------------------------------


Dim qt As QueryTable
Set qt = ActiveSheet.QueryTables(1)
qt.CommandText = "SELECT slitemm.customer," _
& " [item_no] AS [Invoice Number], [refernce] AS [2nd Ref], " _
& "[dated] AS [Invoice Date], [kind] AS [Type], " _
& "[unall_amount] AS [Amount], slitemm.open_indicator" & vbNewLine _
& "FROM scheme.slitemm slitemm" & vbNewLine _
& sqlWH _
& "ORDER BY slitemm.customer, slitemm.dated, slitemm.item_no"
qt.Refresh

--------------------------------------------------------------------------------


The popup requires people to enter a username and password. I'd like that to be put in the code, but i don't know how to manage it.

 

[Mike Blackman]

Hi,

You could maybe approach authorisations a different by housing a list of authorised users in a sheet and having the code only run if there name appears on the list;

Use Environ("Username") to check the user.

 

[RoryA]

You need to look at the querytable's connection property. It will need to be something like:

ODBC;Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

 

[davie1982]

mm, it's not that.

When you go to refresh the data on the spreadsheet it requires a login and password to the SQL database which is always the same. Is there a way to store these 2 in the vb code so you don't need to put the password in?