Get Connection String to Access DAO Database

To specify the connection string to a database, we use getDbsConString function. There are, at least, two variables to be set to specify the connection string. They are strProvider and strPassword. The strProvider variable specifies database provider to be employed, for example: the provider for MS Access database is MS Access. If the connection string has a password, then the strPassword variable must also be set. The result of getDbsConString function is a string value.

Following is the complete getDbsConString function:

Function getDbsConString() As String
  Dim strProvider As String
  Dim strPassword, strPWD As String
On Error GoTo Err_Msg
  strProvider = "MS ACCESS"
  strPassword = ""
  If strPassword <> "" Then strPWD = ";PWD=" & strPassword Else strPWD = ""
  getDbsConString = strProvider & strPWD
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function getDbsConString, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

Implementation:

Setting the strProvider and strPassword as follows:

strProvider = "MS ACCESS"
strPassword = "abcd"

we get the connection string as follows:

getDbsConString = strProvider & strPWD
getDbsConString = "MS ACCESS;PWD=abcd"

Comments

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.

Function to Get the Values for Field Properties in a Table

Every field in a table has properties and their values The values for field properties may help us in entering data into the table. These values may describe the field briefly, such as description, caption, type, and size. In addition, these values may be used to validate data to be entered before being stored in a table, such as validation rule and validation text

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.

Me.Access

Search This Blog