Urgent - problem with permissions to ms sql server

[jaryszek]

Hi,



i set up ms sql server as BE and Access as FE

I am connecting to MS SQL Server using Tab External Data and ODBC Database.

NExt i am creating computer data source : SQLServerProd:



And it is working fine.

Problem is that it is working only on my computer. Anywehere else users can not log into MS SQL Server Database:


This code throws an error:

Set rs = CurrentDb.OpenRecordset("tbl_Login_Emp", dbOpenSnapshot, dbReadOnly)

So it is problem with connecting into MS SQL Server.

So i need to create data source connection automatically for every user using FE.
Users do not have my private computer connection "SQLServerProd" so this is the error.

How can i fix it?

Best,
Jacek

[jaryszek]

Problem is how to make my ODBC connection available to all users?

Jacek

[jaryszek]

I am trying to use this code:

Sub Start()




    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim StringConnect As String
    
StringConnect = "Provider=SQLOLEDB;Data Source=NT-WRO1-PLAT01\SQLEX2014;Initial Catalog=Sody;User ID=sod;Password=Sod#2018"
    
    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        ' Only make a change if the table is a linked table
        If Len(tdf.Connect) Then
            tdf.Connect = StringConnect
            tdf.RefreshLink
        End If
    Next
End Sub

But all the time windows forces me to choose DSN data source...:



Please help,
Jacek

[aytee111]

Don't know much about this! Isn't there a way to store the connection information when you link the table, a checkbox allowing you to save it? There is also a way to set the ODBC connection in VBA, which you could use in the database open routine.

[Gicu]

Hi Jacek,
How did you setup your MS SQL server, specifically what authentication type have are you using. It seems like you want to go with SQL authentication (basically sharing one SQL user's credentials among all your users). It might be better to give read/write permissions to a group and make all your intended users part of that group. That way you can use auditing tools in SQL server to monitor individual's actions. Here is a link explaining the differences: https://stackoverflow.com/questions/...ows-authentica

You may also need to relink your tables making sure you check the Save Password check box. Here is a link showing you hot to do it in code https://access-programmers.co.uk/for...d.php?t=285814 . You'll need to modify the code to add a loop through all the CurrrentDb.Tabledefs (checking for the .connect string if contains "SQL" to identify the SQL tables).

Cheers,
Vlad

[jaryszek]

Thank you very much my Friends!

I learned a lot reading your posts and links and tryint to solve problem.
Problem was connected with wrong driver when connecting FE to BE.

Driver instead of MS SQL Server Native 11.0 should be SQL Server (basic one).
This solve the problem connection for users on Access Runtimes.

Thank you very much !
Jacek

[ArviLaanemets]

You export the Windows registry key for ODBC connection in your computer to file;
Every user must have local administration rights when installing ODBC Connection, and must have a copy of registry editor file you created on some local folder in computer (p.e. on Desktop);
The user starts the registry editor file, and responds YES when asked. When finished, the ODBC connection is created, and the user can delete the registry editor file from computer.
In case users will access database from terminal server, an administrator has to install the connection to TS.

Users start their front-end, and when user rights are set properly for SQL Server database, all must work.

[jaryszek]

In case users will access database from terminal server, an administrator has to install the connection to TS.

Administrator has to create connection from his local computer to terminal server, and this connection should be installed on every user computer?

It is not necessary my friend. I created one ODBC connection on my local computer with SQL Server driver to BE MS SQL Server on terminal server and all users can also access to BE tables from Access Runtime FE.

Jacek