What references need ADODB?

[michelle]

Dear VB users,

I try to open a database with an ADO command.

Dim cnnDB As New ADODB.connection

If selecting references:

"MicroSoft ADO Ext. 2.1 for DLL and security and security."


the code will stumble the Dim row.

Can someone tell me what references I do have to choose?

Thanks for reading,

Michelle.

[HeSaidJoe]

'from an ADO sample exercise

Option Explicit

' Private references to the ADO 2.1 Object Library

Private mCN As Connection
Private mRS As New Recordset

' Internal reference to the current records ID value

Private mintRcdID As Integer

Private Sub cmdAbout_Click()
    frmAbout.Show vbModal
End Sub

Private Sub cmdAdd_Click()
    AddRecord
End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub OpenConnection(strPath As String)

' Close an open connection

    If Not (mCN Is Nothing) Then
        mCN.Close
        Set mCN = Nothing
    End If
    
' Create a new connection

    Set mCN = New Connection

    With mCN

' To connect to a SQL Server, use the following line:
' .ConnectionString="driver=[SQL Server];uid=admin;server=mysrv;database=site"

' For this example, we will be connecting to a local database

        .ConnectionString = "Provider=Microsoft.JET.OLEDB.3.51;Data Source=" & strPath

        .CursorLocation = adUseClient
        .Open

    End With

End Sub

Private Sub AddRecord()

' Add a new record using the recordset object
' Could be done using the connection object
    
    mRS.Open "SELECT * FROM People", mCN, adOpenKeyset, adLockOptimistic

    With mRS

        .AddNew
        .Fields("Name").Value = txtName.Text
        .Fields("URL").Value = txtURL.Text
        .Fields("Notes").Value = txtNotes.Text
        
' After updating the recordset, we need to refresh it, and then move to the
' end to get the newest record. We can then retrieve the new record's id
        
        .Update
        .Requery
        .MoveLast
        
        mintRcdID = .Fields("ID").Value
        
        .Close

    End With

End Sub

Private Sub DeleteRecord()

' Delete a record and clear the textboxes

    mRS.Open "SELECT * FROM People WHERE ID =" & mintRcdID, mCN, adOpenKeyset, adLockOptimistic

    mRS.Delete
    mRS.Close

    txtName.Text = ""
    txtURL.Text = ""
    txtNotes.Text = ""

End Sub

Private Sub GetInfo()

' Get the data for a record based on its ID value

    mRS.Open "SELECT * FROM People WHERE ID =" & mintRcdID, mCN, adOpenKeyset, adLockOptimistic

    With mRS

        txtName.Text = .Fields("Name").Value
        txtURL.Text = .Fields("URL").Value
        txtNotes.Text = .Fields("Notes").Value
        .Close

    End With

End Sub

Private Sub UpdateRecord()

' Update a record's values

    mRS.Open "SELECT * FROM People WHERE ID =" & mintRcdID, mCN, adOpenKeyset, adLockOptimistic

    With mRS

        .Fields("Name").Value = txtName.Text
        .Fields("URL").Value = txtURL.Text
        .Fields("Notes").Value = txtNotes.Text

        .Update
        .Close

    End With

End Sub

Private Sub cmdDelete_Click()
    DeleteRecord
End Sub

Private Sub cmdGet_Click()

' Ask the user which record should be retrieved and get the data
' for that record

    mintRcdID = Val(InputBox$("Enter ID of record:", App.Title, "1"))

    GetInfo

End Sub

Private Sub cmdSave_Click()
    UpdateRecord
End Sub

Private Sub Form_Load()

    OpenConnection App.Path & "\people.mdb"

End Sub

Private Sub Form_Unload(Cancel As Integer)

    If Not (mRS Is Nothing) Then
        Set mRS = Nothing
    End If

    If Not (mCN Is Nothing) Then
        mCN.Close
        Set mCN = Nothing
    End If

End Sub

[mlewis]

Try referencing

Microsoft ActiveX Data Objects 2.1 Library

[michelle]

Hello HeSaidJoe & mlewis,

Thanks for your information.

Have a nice weekend!


Michelle.