Programatically enter SQL username and password

[CPGDeveloper]

Hi All ~

I can't seem to find this anywhere through several searches, but there must be a simple way to do this -- I have an MS Access application with an Azure SQL Back End. The SQL Tables and Views are linked through the Access ODBC tool. For the end user, I want to bypass them having to enter the username and password for SQL -- how would I go about this?

 

[ranman256]

create an odbc that links to your db,
be sure the checkbox SAVE PASSWORD is checked so users don't have to enter them.
(tho I'm not sure how Azure does it if its not in the OCBC creation)

 

[CPGDeveloper]

That was it. Thanks!

 

[CPGDeveloper]

Just FYI for anyone else who might need it -- to connect to Azure SQL, I created a separate initial form called 'fSQL' and on the open event of that form, ran the following code to test the connection and then if the test was successful, connected to SQL without using a DSN File -- this all works for us, provided the end user has the correct ODBC driver installed on their machine.

So on open, I call this subroutine --
Sub DSN()

Dim tdf As DAO.TableDef
Dim dbs As DAO.Database
Dim txtDriver As String
Dim txtuid As String
Dim txtdb As String
Dim txtServer As String
Dim txtpwd As String
Dim sqlsql As String
Dim sqlrs As DAO.Recordset

'tSQL is a local table where I store all the pertinent info for the DSN Connection String
sqlsql = "SELECT * FROM tSQL WHERE ID = 1;"
Set sqlrs = CurrentDb.OpenRecordset(sqlsql, 4, 512)
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("qAnalyst")

txtDriver = sqlrs!Driver
txtuid = sqlrs!uid
txtdb = sqlrs!Database
txtServer = sqlrs!server
txtpwd = sqlrs!pwd

Set sqlrs = Nothing

'I pass the connection string info to another function which tests the connection and returns true or false to a public boolean 'SQLTEST'
Call CanOpenSQLDbLB(txtDriver, txtServer, txtdb, txtuid, txtpwd)

If SQLTEST = True Then
tdf.Connect = "DRIVER=" & txtDriver & "; " & _
"UID=" & txtuid & "; " & _
"PWD=" & txtpwd & "; " & _
"TrustServerCertificate=No; " & _
"Database=" & txtdb & "; " & _
"Trusted_Connection=No; " & _
"SERVER=" & txtServer & "; " & _
"TABLE=dbo.qAnalyst; " & _
"APP=Microsoft Office"
tdf.RefreshLink

End If

End Sub

And this is the function to test the connection -- you would need a Microsoft Active X Reference
Function CanOpenSQLDbLB(pstrDriver As String, pstrServer As String, pstrDb As String, pstrUser As String, pstrPassword As String)

On Error GoTo CanOpenSQLDbLB_Err

Dim objConn As Object
Dim strConn As String
Dim strError As String, lngErr As Long

Set objConn = CreateObject("ADODB.Connection")

strConn = strConn & "DRIVER=" & pstrDriver
strConn = strConn & ";SERVER=" & pstrServer
strConn = strConn & ";APP=Microsoft Office"
strConn = strConn & ";DATABASE=" & pstrDb
strConn = strConn & ";UID=" & pstrUser
strConn = strConn & ";PWD=" & pstrPassword
strConn = strConn & ";TrustServerCertificate=No"
strConn = strConn & ";Trusted_Connection=No"

objConn.Open strConn
CanOpenSQLDbLB = True

SQLTEST = True

CanOpenSQLDbLB_Exit:
On Error Resume Next
objConn.Close
Set objConn = Nothing

Exit Function

CanOpenSQLDbLB_Err:
CanOpenSQLDbLB = False
SQLTEST = False

End Function