Báo lỗi <type not defined khi khai báo New ADODB.Connection

[cadafi]

Em không biết tại sao khi chay sub khai báo sau thì bị lỗi, các anh giúp em với:

Sub DBConection()
'On Error GoTo Err_DBConnection

Set cnn = New ADODB.Connection   ==>Báo lỗi type not define ngay dòng này
DBName = "T:\AMGACCT\AMGSYS.mdb"
With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Jet OLEDB:Database Password") = ""
    .Open "Data Source=" & DBName
End With
a = MsgBox("Da ke noi thanh cong, an OK de tiep tuc", , "Thong bao")
Exit Sub

Err_DBContection:
    MsgBox "Khong the tim duoc file " & DBName
    ThisWorkbook.Saved = True
    Application.Quit
End Sub

 

[cadafi]

Thôi em biết rồi, chưa chọn reference Active X Data Object. Cảm ơn các anh chị.

 

[skyonline]

Lúc viết code theo em chưa nên đặt bẫy lỗi và câu lệnh mở cnn anh nên rút gọn cho dễ nhìn.

 

[levanduyet]

To: Ca_Dafi,
Khi viết code để connect với database, anh nghĩ chúng ta nên viết hàm hơn là thủ tục.

Ví dụ:

Public Function ConnectToDB(Optional bUseDSN As Boolean = False, _
                            Optional sDSNName As String, _
                            Optional sUserName As String = vbNullString, _
                            Optional sPass As String = vbNullString) As Integer

    'This function will return:
    '   1: Connect is successfull
    '   0: Can not connect to database or error

    'The ObjectStateEnum constants defined in ADO
    '.adStateClosed     | Means the connection is closed
    '.adStateOpen       | Means the connection is open
    '.adStateConnecting | Means the object is in the process of making a connection
    '.adStateExecuting  | Means the connection is executing a command

    Dim sConnect As String
    Dim lAttempt As Long
    Dim iConnectFirstTimes As Integer

    On Error GoTo ErrorHandler

    'Check the gcnAcess before open
    'gcnAccess.State = ObjectStateEnum.adStateOpen is openning
    'Not gcnAccess Is Nothing, we have create the gcnAccess variable
    
    If (gcnAccess.State = ObjectStateEnum.adStateOpen) And (Not gcnAccess Is Nothing) Then
        'i.e has connected to database
        'tuc la da ket noi voi CSDL
        ConnectToDB = eTinhTrangKetNoi.KetNoiThanhCong
        gcnAccess.Close
        GoTo ErrorExit
    End If

    'Suppose that can not connect to database
    'Gia su rang khong the ket noi voi CSDL
    ConnectToDB = eTinhTrangKetNoi.KetNoiKhongThanhCong
    iConnectFirstTimes = GetIni("DSN", "FIRSTTIME")

    If CInt(iConnectFirstTimes) = 1 Then
        iConnectFirstTimes = 1    'is the first times to connect/Neu bang 1 tuc la lan dau tien connect
    Else
        iConnectFirstTimes = 0    'does not the first times to connect/Neu khac 1 tuc la day khong phai lan dau tien
    End If

    sConnect = GetConStr(iConnectFirstTimes, bUseDSN, sDSNName, sUserName, sPass)
    
    If sConnect = vbNullString Then
        'If sConnect = vbNullString, then can not connect to database
        GoTo ErrorExit
    End If
    With gcnAccess
        .Mode = adModeReadWrite
        .ConnectionTimeout = 30    'Neu sau thoi gian nay ma khong ket noi duoc se bao loi
        .CursorLocation = adUseClient
        .ConnectionString = sConnect
        .Open
    End With

    ConnectToDB = eTinhTrangKetNoi.KetNoiThanhCong
    'Close the connection to enable connection pooling
    gcnAccess.Close

ErrorExit:

    Exit Function

ErrorHandler:
    'We will try to make the connection 3 times before bailing out
    If lAttempt < 3 And gcnAccess.Errors.Count > 0 Then
        If gcnAccess.Errors(0).NativeError = 17 Then
            lAttempt = lAttempt + 1
            Resume
        End If
    Else
        'If more than 3 times then
        ConnectToDB = eTinhTrangKetNoi.KetNoiKhongThanhCong    'i.e can not connect to Database
    End If
    If bCentralErrorHandler(mcsModName, "ConnectToDB", , False) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If
End Function

Và hàm xây dựng chuổi kết nối:

'---------------------------------------------------------------------------------------
' Procedure : GetConStr
' DateTime  : 11/07/2008
' Author    : Le Van Duyet
' Purpose   : To get the connection string
'             _ If you use DSN, then you pass this Argument to this function
'             and sUserName, sPass also
'             _ If you do not use DSN, the bUseDSN = False,
'             then the database file will suppost put
'             to the same folder with this workbook file
'
'             Please take note that we suppost our database is *.mdb
' Result    : vbNullString if there is any error
'             otherwise return the Connection String
'---------------------------------------------------------------------------------------
'
Private Function GetConStr(Optional iConnectFirstTimes As Integer = 0, _
                           Optional bUseDSN As Boolean = False, _
                           Optional sDSNName As String = vbNullString, _
                           Optional sUserName As String = vbNullString, _
                           Optional sPass As String = vbNullString) As String

    Dim sConnect As String
    Dim sDBPath As String
    On Error GoTo ErrorHandler

    'Define the sDBPath
    sDBPath = ThisWbPath & mcsDBFileName
    'If it's the first time connect to Database
    'Then connect to the database in the same folder
    If iConnectFirstTimes = 1 Then
       
            bUseDSN = False
        Else
            bUseDSN = True
       
    End If
    'According to sDNSName to create the connection string
    If IsMissing(sUserName) Or Len(sUserName) = 0 Then
        sUserName = "Admin"
    End If

    If bUseDSN And Len(sDSNName) = 0 Then
        sDBPath = mcsDSN    'Use the const mcsDSN
        'The OLE DB provider for ODBC is called MSDASQL
        '(MircroSoft Data Access SQL)
        sConnect = "Provider=MSDASQL; DSN=" & sDBPath & _
                   "; UID=" & sUserName & "; PWD=" & sPass & ";"
    ElseIf bUseDSN And Len(sDSNName) > 0 Then
        sDBPath = sDSNName    'Use the user sDSNName
        'The OLE DB provider for ODBC is called MSDASQL
        '(MircroSoft Data Access SQL)
        sConnect = "Provider=MSDASQL; DSN=" & sDBPath & _
                   "; UID=" & sUserName & "; PWD=" & sPass & ";"
    Else
        'Connect direct to the Database file, to easy to understand
        sDBPath = sDBPath
        sConnect = "Driver={Microsoft Access Driver (*.mdb)}; " & "Dbq=" & sDBPath & "; " & _
                   "UID=" & sUserName & "; PWD=" & sPass & "; "
    End If

    GetConStr = sConnect

ErrorExit:

    Exit Function

ErrorHandler:
    'If there is an error, set sCreateConStr = vbnullstring
    GetConStr = vbNullString
    If bCentralErrorHandler(mcsModName, "sCreateConStr") Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If

End Function

Như vậy khi dùng em chỉ việc kiểm tra xem iConnectToDB bằng bao nhiêu để quyết định thực hiện thao tác nào tiếp theo.

Các giá trị để dùng trong iConnectToDB em có thể dùng enum
Ví dụ:

Enum
iConThanhCong = 1 'Kết nối thành công
iConKhongTimThayDB = -2 'Không tìm thấy database
iConKhongDungPass = -3 'Không đúng Password
...
End Enum

Lê Văn Duyệt