How to point to MS sql server database from MS access form (1 Viewer)

[masoud_sedighy]

I would like to know, How to point to sql database from MS access form after upsizing MS access file to sql server and linking tables from MS access to sql server.
I mean If I made a sign in form in MS access for entering username and password and then click ok button, how to point to sql server database?
When I use MS access tables, I made a table in MS access file and put userId, Username and password field for managing that with something like below code:

<code>

Set db=DB engine (0) (0)
Set rst=db.openRecordset(“select tblUsers.Usename from tblusers where username=’”Me.txtUsername……)

</code>
For sql server how I have to do that?
How to login to sql server with txtUsername and txtPassword from MS access form and find users and roles in sql server.

 

[Minty]

I would normally use the same user table stored in SQL Server, and allow and disallow them features in the database based on their user level. Your tables are linked so by definition the user has access to them, unless explicitly barred.

It wouldn't be normal to set per user permissions on the SQL server, normally you set up a user group that includes all members of staff, and whenever a user is added to your domain they are added to that group.

 

[masoud_sedighy]

For connecting to back-end access file I am using below code



Dim db As DAO.Database, rst As DAO.Recordset

' Point to this database

Set db = DBEngine(0)(0)
' Open a recordset based on user type

Select Case Me.ogUserType

Case UserType.Administrator

Set rst = db.OpenRecordset("SELECT tblAdministrators.AdministratorID AS PersonID " & _ "FROM tblAdministrators " & _ "WHERE UserID = '" & Me.txtUserID & _ "' AND Password = '" & Me.txtPassword & "'", dbOpenSnapshot)

Now I would like to know for connecting to sql server what changes I have to do?

 

[Minty]

None. If your tables are linked and have the correct permissions set on them on SQL Server then anyone should be able to connect to them.

Whether you let a user see that data is down to your design.

I'm not sure I am getting your question?

 

[masoud_sedighy]

I mean when i need connect to sql server that is on the server and it has user and password for login to sql server, the connection string how should be?
when it was access i was using

Set db = DBEngine(0)(0)

now for connecting to sql server how should be?

 

[Minty]

The recommended route is to use DSN Less connections for your linked tables.

Have a read here https://access-programmers.co.uk/forums/showthread.php?t=158159