Connection Management

This document details the database connection management features of the cDataBase class, including connection, disconnection, and connection pool.

---

Table of Contents

• Database Connection
• Supported Database Types
• Connection String Configuration
• Connection State Management
• Connection Pool Management
• Error Handling

---

Database Connection

Connect Method

The Connect method is used to establish a database connection.

Syntax

Function Connect( _
    ByVal DbType As enumDbType, _
    Optional ByVal DbAddress As String = "127.0.0.1,1433", _
    Optional ByVal username As String = "sa", _
    Optional ByVal password As String = "Sa123456", _
    Optional ByVal DefaultDataBase As String = "master") As Boolean

Parameters

Parameter	Type	Description
DbType	enumDbType	Database type (required)
DbAddress	String	Database address (optional, default "127.0.0.1,1433")
username	String	Username (optional, default "sa")
password	String	Password (optional, default "Sa123456")
DefaultDataBase	String	Default database (optional, default "master")

Return Value

• True - Connection successful
• False - Connection failed (view error via LastErr)

Example

Dim db As New VBMAN.cDataBase

' SQL Server connection
If db.Connect(VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "password", "mydb") Then
    Debug.Print "Connection successful"
Else
    Debug.Print "Connection failed: " & db.LastErr
End If

---

Supported Database Types

enumDbType Enumeration

Public Enum enumDbType
    Access = 1    ' Microsoft Access
    Mysql = 2     ' MySQL
    MsSql = 3     ' Microsoft SQL Server
    Csv = 4       ' CSV file
End Enum

SQL Server Connection

' Basic connection
db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "password", "master"

' Using named instance
db.Connect VBMAN.enumDbType_MsSql, "SERVER\INSTANCE,1433", "sa", "password", "mydb"

' Using Windows authentication (requires modifying connection string)
' Note: Need to modify connection string internally to support Windows authentication

MySQL Connection

' Basic connection (default port 3306)
db.Connect VBMAN.enumDbType_Mysql, "localhost:3306", "root", "password", "testdb"

' Specify port
db.Connect VBMAN.enumDbType_Mysql, "192.168.1.100:3306", "user", "pwd", "mydb"

' Using Chinese comma (auto-converted)
db.Connect VBMAN.enumDbType_Mysql, "localhost，3306", "root", "pwd", "testdb"

Access Connection

' Connect to Access database file
db.Connect VBMAN.enumDbType_Access, "C:\data\mydb.mdb"

' Using relative path (auto-converted to absolute path)
db.Connect VBMAN.enumDbType_Access, "data\mydb.mdb"

CSV Connection

' Connect to CSV file directory
db.Connect VBMAN.enumDbType_Csv, "C:\data\csvfiles"

' CSV files are treated as tables for querying
db.Sql("SELECT * FROM data.csv").Query

---

Connection String Configuration

Auto-generated Connection Strings

The library automatically generates connection strings based on database type:

SQL Server

Driver={SQL Server};Server=127.0.0.1,1433;Uid=sa;pwd=password;Database=mydb;

MySQL

Driver={MySQL ODBC 5.1 Driver};Server=localhost:3306;Uid=root;pwd=password;Database=testdb;

Access

Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\data\mydb.mdb;

CSV

Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\data\csvfiles

Custom Connection String

If you need to use a custom connection string, set it before connecting:

' Note: Need to modify internally to support custom connection strings
' Or use ADO Connection object directly
Set db.Conn = New ADODB.Connection
db.Conn.ConnectionString = "Provider=SQLOLEDB;Data Source=...;..."
db.Conn.Open
db.IsConnect = True

---

Connection State Management

IsConnect Property

The IsConnect property indicates the current connection state.

' Check connection state
If db.IsConnect Then
    Debug.Print "Connected"
Else
    Debug.Print "Not connected"
End If

CheckConnection Method

The CheckConnection method checks the connection state and attempts to reconnect.

' Check and reconnect
If Not db.CheckConnection Then
    Debug.Print "Connection lost, attempting to reconnect..."
    db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"
End If

Connection State Check

Check connection state before executing operations:

If Not db.IsConnect Then
    Debug.Print "Database not connected"
    Exit Sub
End If

' Execute query
db.Sql("SELECT * FROM users").Query

---

Connection Pool Management

ConnInst Method

The ConnInst method creates or retrieves a database instance from the connection pool.

Syntax

Function ConnInst(ByVal InstName As String, Optional ByVal IsCloneMasterConnection As Boolean = True) As cDataBase

Parameters

Parameter	Type	Description
InstName	String	Instance name (required)
IsCloneMasterConnection	Boolean	Whether to clone master connection (optional, default True)

Example

Dim db As New VBMAN.cDataBase

' Master connection
db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"

' Create connection instance 1 (clone master connection)
Dim db1 As VBMAN.cDataBase
Set db1 = db.ConnInst("db1", True)
db1.Sql("SELECT * FROM table1").Query

' Create connection instance 2 (independent connection)
Dim db2 As VBMAN.cDataBase
Set db2 = db.ConnInst("db2", False)
db2.Connect VBMAN.enumDbType_Mysql, "localhost:3306", "root", "pwd", "testdb"
db2.Sql("SELECT * FROM table2").Query

' Get existing instance
Set db1 = db.ConnInst("db1")

ConnInstRemove Method

The ConnInstRemove method removes an instance from the connection pool.

Syntax

Sub ConnInstRemove(Optional ByVal InstName As String)

Example

' Remove specified instance
db.ConnInstRemove "db1"

' Remove all instances
db.ConnInstRemove ""

Connection Pool Usage Scenarios

Scenario 1: Multi-database Operations

Dim db As New VBMAN.cDataBase

' Main database
db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"

' Log database
Dim dbLog As VBMAN.cDataBase
Set dbLog = db.ConnInst("log", False)
dbLog.Connect VBMAN.enumDbType_Mysql, "192.168.1.100:3306", "loguser", "pwd", "logdb"

' Use different databases
db.Sql("SELECT * FROM users").Query
dbLog.Sql("INSERT INTO logs (msg) VALUES ('test')").Exec

Scenario 2: Dynamic Database Switching

Dim db As New VBMAN.cDataBase

' Create connections dynamically based on configuration
Function GetDatabase(sConfigName As String) As VBMAN.cDataBase
    Dim dbInst As VBMAN.cDataBase
    Set dbInst = db.ConnInst(sConfigName, False)

    ' Connect to different databases based on configuration
    Select Case sConfigName
    Case "main"
        dbInst.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "maindb"
    Case "backup"
        dbInst.Connect VBMAN.enumDbType_Mysql, "192.168.1.100,3306", "user", "pwd", "backupdb"
    End Select

    Set GetDatabase = dbInst
End Function

' Usage
Dim dbMain As VBMAN.cDataBase
Set dbMain = GetDatabase("main")
dbMain.Sql("SELECT * FROM users").Query

---

Disconnect Connection

Disconnect Method

The Disconnect method closes the database connection.

Syntax

Function Disconnect() As Boolean

Functionality

• Close database connection
• Close recordset
• Release resources
• Auto rollback incomplete transactions

Example

' Disconnect
If db.Disconnect Then
    Debug.Print "Disconnected"
End If

Auto Disconnect

The class automatically disconnects when destroyed:

Private Sub Form_Unload(Cancel As Integer)
    ' No need to manually call Disconnect, class handles automatically
    Set db = Nothing
End Sub

---

Error Handling

Error Properties

Property	Type	Description
LastErr	String	Last error message (full description)
LastErrNumber	Long	Last error code
LastErrDescription	String	Last error description

Error Handling Example

' Connection error handling
If Not db.Connect(VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb") Then
    Debug.Print "Connection failed"
    Debug.Print "Error code: " & db.LastErrNumber
    Debug.Print "Error description: " & db.LastErrDescription
    Debug.Print "Full info: " & db.LastErr
    Exit Sub
End If

' Query error handling
If Not db.Sql("SELECT * FROM users").Query Then
    Debug.Print "Query failed: " & db.LastErr
    Exit Sub
End If

Common Connection Errors

Error 1: Cannot connect to server

Error code: -2147467259
Error description: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied

Solution:

• Check if database service is running
• Verify server address and port
• Check firewall settings

Error 2: Login failed

Error code: -2147467259
Error description: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'

Solution:

• Verify username and password
• Check SQL Server authentication mode
• Confirm user permissions

Error 3: Database does not exist

Error code: -2147467259
Error description: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login

Solution:

• Verify database name
• Check if database exists
• Confirm user has access permissions

---

Best Practices

1. Connection Management

' Recommended: Connect when needed, disconnect when done
Private Sub ProcessData()
    Dim db As New VBMAN.cDataBase

    If db.Connect(VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb") Then
        ' Execute operations
        db.Sql("SELECT * FROM users").Query
    End If

    db.Disconnect
    Set db = Nothing
End Sub

2. Connection Reuse

' Recommended: Keep connection for frequent operations
Private m_DB As VBMAN.cDataBase

Private Sub Form_Load()
    Set m_DB = New VBMAN.cDataBase
    m_DB.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If Not m_DB Is Nothing Then
        m_DB.Disconnect
        Set m_DB = Nothing
    End If
End Sub

3. Error Handling

' Recommended: Always check return values and handle errors
If Not db.Connect(...) Then
    MsgBox "Connection failed: " & db.LastErr, vbCritical
    Exit Sub
End If

---

Last Updated: 2026-01-21