I have an access form that pulls together data from different tables. Some of them require a username and password be entered. This is going out to other users and I would rather them not have to enter the information over and over. Is there a code that I can use that whenever it prompts for a password to have it automatically logged in?
Currently I already have a connection for one DB connection that runs whenever the DB is opened. It looks like:
Public Function StartUp()
Dim cnn As ADODB.Connection
Dim localrst As New ADODB.Recordset
Dim remoterst As New ADODB.Recordset
On Error Resume Next
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB; Data Source=SOURCE; Initial Catalog=NAME;" _
& "User Id=ID; Password=PW;"
If cnn.State = adStateOpen Then
MsgBox ("You have an established connection with the L&TD SQL Server Database and the CDData table has been uploaded to the server.")
MsgBox ("Cannot connect to SQL Server. Data will be stored locally to CDData Table until application is opened again with an established connection.")
On Error GoTo 0
Is there a way to add more connections to this so it connects to all 3?
The literal answer is; no. You can't make a password form autofill. However, you can set up your connection string so that no one has to fill it in.
'Set up the connection string strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=MyServerName;INITIAL CATALOG=MyDatabaseName;UID=MyStandardUserID;PWD=MyStandardPassword;" cnComments.Open strConn
You can also set it up without the username and password:
'Set up the connection string strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=MyServerName;INITIAL CATALOG=MyDatabaseName;TRUSTED_CONNECTION=yes;" cnComments.Open strConn