Skip to main content

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
Labels:

Comments

Post a Comment

Popular posts from this blog

Functions to Open and Close DAO Database

This post describes how to create functions to open and close MS Access database. The functions involve two common methods provided by MS Access, they are OpenDatabase and Close. Opening a database, especially  used in connecting to tables and queries is the first process in accessing database. Meanwhile, closing a database is the last process in accessing database.
Post a Comment
Read more

Function to Retrieve the Precision and Scale Property for Decimal Data Type

The uniqueness of decimal data type in Access are the existing properties named Precision and Scale. Precision property refers to the maximum number of digits in a number. Meanwhile, Scale property is the number of digits to the right of the decimal point in a number. Say for example, 123.45 has a precision of 5 and a scale of 2.
Post a Comment
Read more

Function to Check If a Table Exists in Access Database

We can create a function to check whether a specific table exist in MS Access Database. There are at least two functions we can create to evaluate the existence of a table in Access, one for external database and the other for current database. Let's name isTableExist function for checking the existence of a table in external database and isTableExistCurrentDbs function for checking the existence of a table in current database.
Post a Comment
Read more