Get Connection String to Access ADO Database

Besides using DAO, a connection string to a database using ADO can also be set. In ADO connection string, we use at least four constants to be set to specify the connection string. They are constStrProvider,constStrDataSource, constStrUserId, and constStrPassword. The constStrProvider Const specifies database provider to be employed, for example: the provider for MS Access database is Microsoft.ACE.OLEDB.12.0. The constStrDataSource Const refers to the database file path. The constStrUserId Const refers to user identity, if the database use access level. If the connection string has a password, then the constStrPassword Const must also be set.
Based on the four constants above, we create getDbsConStringADO function as follows:

Function getDbsConStringADO() As String
  Dim strPWD As String, strUserId As String
'Set the following Constants as neccessary
  Const constStrProvider As String = "Microsoft.ACE.OLEDB.12.0" 'OLE DB provider name (MS Access 2007)
  Const constStrDataSource As String = "D:\Access\Database_be.accdb" 'Database file path name
  Const constStrUserId As String = "" 'User identity, if the database use access level
  Const constStrPassword As String = "abcs" 'password, if any
On Error GoTo Err_Msg
  If constStrUserId <> "" Then strUserId = ";User ID=" & constStrUserId Else strUserId = ""
  If constStrPassword <> "" Then strPWD = ";JET OLEDB:Database Password=" & constStrPassword Else strPWD = ""
  getDbsConStringADO = "Provider=" & constStrProvider & _
                          ";Data Source=" & constStrDataSource & _
                          strUserId & strPWD
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function getDbsConStringADO, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

Implementation

If the above function is performed in Immediate Window

?getDbsConStringADO
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Access\Database_be.accdb;JET OLEDB:Database Password=abcs