DSN-Less Connection to SQL Server With Server Authentication (1 Viewer)

[grendell2099]

Hi all. I am stuck trying to find a solution to DNS-less connection between an Access 2010 front end and SQL Server 2008 backend tables using SQL Server authentication. The current environment/ application uses Windows authentication for access to the Server. I want to switch the application to Server authentication for several reasons, including eliminating a chronic problem of random users who have no permissions in the database logging into production computers running the application- resulting in stations that cannot run the application until someone figures this out. Various IT policies mean that any machine currently running the app can also run any number of other apps that would allow interaction with the server tables.

I use the code below to loop through the tables and pass through queries in the app and “connect” them to either the development or production database. I also created a test login for our SQL server and the code runs with no errors when passing through the connection string with login and password. Looking at the connection string during execution, it appears to be correct.
However, the relinking does not appear to retain the login and password. When I take a copy to a test pc that has no Windows authentication rights to the Server, I get a connection pop up. It appears that everything is defaulting to the “trusted connection” type.
Any ideas on how/if I can link the tables with a login/ password so we can switch to server authentication?

Public Sub RelinkTables(strConnectionString As String)
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strTabelleAccess As String
    Dim strTabelleSQLServer As String
    Dim i As Integer
    Dim qdf As QueryDef
    Dim x As String
    '********************************************
	'this is the format of the connection string I am passing:
	'strConnectionString= ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DbaseName;UID=EOCR;PWD=Password
    Set db = CurrentDb
    For i = db.TableDefs.Count - 1 To 0 Step -1
        Set tdf = db.TableDefs(i)
        If Left(tdf.Connect, 4) = "ODBC" Then
            strTabelleAccess = tdf.Name
			strTabelleSQLServer = tdf.SourceTableName
			db.TableDefs.Delete strTabelleAccess
			Set tdf = db.CreateTableDef(strTabelleAccess, 0, strTabelleSQLServer)
			tdf.Connect = strConnectionString
			db.TableDefs.Append tdf
        End If
    Next i
    'now relink the pass thru queries
    For Each qdf In CurrentDb.QueryDefs
        If qdf.Type = dbQSQLPassThrough Then
            qdf.Connect = strConnectionString
        End If
    Next
    Application.RefreshDatabaseWindow
End Sub

 

[grendell2099]

solved the issue

 

[jdraw]

And the solution was/is????

Someone else may learn from your solution.