Automatic login for Access

[SanguineWizard]

Hello everyone,
I'm trying to create something that automatically writes the username and password to my databases when I open them.

I found this piece of code:
Open() method of the ADODB.Connection object:
Provider=SQLOLEDB.1;Data Source=SqlServerComputerName;Initial
Catalog=DatabaseName;User ID=MyAccount;Password=MyPassword

The issue is; I have no idea how or where to implement this. My initial idea was to create an "opening form" which has a "when this form opens"- function that runs the code.
I have been looking around the net and now I'm even more confused after researching. I've tried going through; https://learn.microsoft.com/en-us/tr...ordset-objects, but I'm still not sure where I need to write the code. The article refers to something called Northwind, which is not something I'm familiar with.

Do I need some software to use the code? Maybe there is a file in Windows where I can edit this in? Or is there a simpler and easier method to achieve the same? (I want to note that I don't have the luxury of recreating every single database and check "save password").

Thanks.

[ranman256]

i have an AUTOEXEC macro, that runs function STARTUP
the inspects the users:

function Startup()
   'get users login id
vUser = getUserID()


   'see if in the database
If IsValidUser(vUser) then
    docmd.Openform "fMainMenu"
else
   msgbox "You are not authorized",vbCritical, "Denied"
   docmd.Quit
endif
end function




Public Function IsValidUser(byval pvUser) as boolean
IsValidUser =not IsNull( dlookup("[UserID]","tblUsers","[UserID]='" & pvUser & "'"))
End Function




Public Function getUserID() As String
getUserID = Environ("Username")
End Function

[SanguineWizard]

i have an AUTOEXEC macro, that runs function STARTUP
the inspects the users:

function Startup()
   'get users login id
vUser = getUserID()


   'see if in the database
If IsValidUser(vUser) then
    docmd.Openform "fMainMenu"
else
   msgbox "You are not authorized",vbCritical, "Denied"
   docmd.Quit
endif
end function




Public Function IsValidUser(byval pvUser) as boolean
IsValidUser =not IsNull( dlookup("[UserID]","tblUsers","[UserID]='" & pvUser & "'"))
End Function




Public Function getUserID() As String
getUserID = Environ("Username")
End Function

Would this work when connecting to an SQL server? It's actually the SQL-server, I need to write the same password 3 times for, because Access connects to 3 different databases.

[Welshgasman]

You download the Northwind DB and view how they did it. Though that just uses an Access BE?

Not the latest and greatest Northwind Traders BTW.

[Welshgasman]

Have a look at this thread and see if that helps.
https://www.accessforums.net/showthread.php?t=87371

[SanguineWizard]

Have a look at this thread and see if that helps.
https://www.accessforums.net/showthread.php?t=87371

Thank you. I think it puts me on the right path. I'm still not sure where to write the code though. I'm used to tieing the code to fields, buttons etc. But I have no idea where to find a place to write code that runs as soon as I open the database. Is it as simple at creating a form that opens when the database does? and then tieing the code to the opening of the form?

[Welshgasman]

As Ranman mentioned, you can create an AutoExec or put it in the first form that loads in the DB.

[orange]

Steve Bishop has a youtube video on Creating a User Login screen.

[SanguineWizard]

As Ranman mentioned, you can create an AutoExec or put it in the first form that loads in the DB.

Thank you. That clarified it for me.

[ranman256]

connect the SqlSvr tables via odbc. No need to login to each table, only once to open the app.

[NoellaG]

you can turn on the login audit on SQL server: https://learn.microsoft.com/en-us/sq...l-server-ver15
or retrieve the information of active logins from sys.dm_exec_sessions . There you'll find all info for all active sessions on your server, included login name, login time, which database, used CPU time, hostname: from which computer did they logon, ect. If you create a log table and a procedure and job that writes this info every x minutes to the log table you have an excellent login audit.