MS access and SQL server password

Question

I've recently migrated My Access Database to SQL Server, I use Access as the front end. I've had to use SQL Authentication due to not having windows Business edition, so windows authentication wouldn't work. I have a log in form in access which is how my agents get to their area of the database. but then whenever they log in they have to then log into the SQL authentication too, which is just a little annoying.

I have experience using VBA to populate fields in other programs, for example a telephone number into a dialler. I'm wondering if it is possible to take the information in the Access log on Form and populate the SQL authentication log in username and password. if this is possible, would there be any kind soul who could direct me to a place I could see the VBA code for this?

thanks in advance

I've tried googling for more information but it's really difficult to express the issue on a search engine, all I get is directions for using windows authentication.

The most straightforward way is to save the user/password when linking the tables in Access. Then secure/lockdown the Access frontend to the needed degree. Or use [this technique](https://stackoverflow.com/questions/9093266/save-password-for-odbc-connection-to-ms-sql-server-from-ms-access-2007). — Andre, Apr 24 '23 at 18:00

score 1 · Accepted Answer · edited Apr 24 '23 at 18:02

It is possible to populate the SQL authentication username and password fields using VBA in Access. One way to do this would be to create a function in VBA that retrieves the username and password from the Access login form and then uses those values to populate the corresponding fields in the SQL authentication dialogue box.

Here is an example of how you might write the VBA code for this:


    Private Function LoginToSQL() As Boolean
        ' Retrieve the username and password from the Access login form
        Dim username As String
        Dim password As String
        username = Me.txtUsername.Value
        password = Me.txtPassword.Value
        
        ' Use the SendKeys method to enter the username and password into the SQL authentication dialogue box
        SendKeys username & "{TAB}" & password & "{ENTER}"
        
        ' Wait for the SQL Server Management Studio window to appear
        Dim hwnd As Long
        hwnd = FindWindow(vbNullString, "SQL Server Management Studio")
        Do While hwnd = 0
            hwnd = FindWindow(vbNullString, "SQL Server Management Studio")
            DoEvents
        Loop
        
        ' Check if the login was successful
        Dim success As Boolean
        success = (hwnd <> 0)
        
        ' Return the result
        LoginToSQL = success
    End Function

In this code, txtUsername and txtPassword are the names of the username and password text boxes on the Access login form. The SendKeys method simulates typing the username and password into the SQL authentication dialogue box. The FindWindow function waits for the SQL Server Management Studio window to appear, indicating that the login was successful.

Note that this approach is not very secure, as the SQL authentication username and password are stored in plain text in the Access database. It may be preferable to use Windows authentication or to prompt the user for their SQL authentication credentials each time they log in.

If you're getting a "Sub or Function not defined" error when trying to use the VBA code I provided, it may be because the FindWindow function is not recognized by Access.

FindWindow is a Windows API function, and in order to use it in VBA you need to declare it first. Here's how you can declare it in your VBA module:

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

You can add this declaration at the top of your VBA module, before any Sub or Function declarations.

Once you've added the declaration, you should be able to use the FindWindow function in your VBA code without any errors.

Using Sendkeys with passwords is really dangerous... something goes wrong and the pw lands in a textbox where it's readable. — Andre, Apr 24 '23 at 18:01

MS access and SQL server password

1 Answers1