Here is the function I use to create the connection, you'll need to customize for your server. Also note that you may or may not want to use the INTEGRATED SECURITY. We have a single user account that all users use to access the SQL database. However, if INTEGRATED SECURITY=sspi, then it will try to log in as your windows login name. However, if each user has their own account, then definitely use it so you don't have to embed the password (a security risk). You could also prompt for password, but our users don't actually know who they are logging in as, they just press a button on the toolbar 🙂
-----------------------------------------------------------------------
Function CreateSQLConnection() As ADODB.Connection
Dim sProvider As String
Dim sServer As String
Dim sCatalog As String
Dim sUser As String
Dim sPassword As String
sProvider = "SQLOLEDB"
sServer = 'your server name here
sCatalog = 'your catalog here
sUser = 'your user account here
sPassword = ' your password here
' Create a connection object.
Dim cnDB As ADODB.Connection
Set cnDB = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = strConn & "PROVIDER=" & sProvider & ";"
'Connect to the database on the server.
strConn = strConn & "DATA SOURCE=" & sServer & ";" & "INITIAL CATALOG=" & sCatalog & ";"
'Set user and password
strConn = strConn & "User Id=" & sUser & ";" & "Password=" & sPassword & ";"
'Use an integrated login (NOTE: DON'T USE WHEN LOGGING IN FROM OTHER USER ACCOUNT)
'strConn = strConn & "INTEGRATED SECURITY=sspi;"
'Open the connection
On Error Resume Next
cnDB.Open strConn
If Err Then
Debug.Print Err.Description
Debug.Print Err.Number
Debug.Print Err.Source
Err.Clear
Return
End If
On Error GoTo 0
Set CreateSQLConnection = cnDB
End Function
-----------------------------------------------------------------------
