[RESOLVED] ADO Help

[BrainA]

i read the ADO Beginners Tutorial by beacon. i still dont undertand. is there easier tutorial or can someone help me please? thank you

[RobDog888]

Here is my quick attempt at a small ADO tutorial/demo. Sure you may need to add more error handling/trapping but this should give you the basics so you can get a feel for how it all works.

Assumes you wanted VB 6 code and Access 2000-2003
Access 2002-2003 compatible database attached
Change the path to the db to reflect the location on your system
This demo functions just like Access Forms do. Click Add first to set it in editmode. Then add the text and cick update.

Option Explicit
'Add a reference to MS ActiveX Data Objects x.x Library
'Declare our connection and recordset objects
Private oCnn As ADODB.Connection
Private oRs As ADODB.Recordset
 
Private Sub Form_Load()
    On Error GoTo MyError
    Dim sSQL As String
    'instanciate the connection object variable
    Set oCnn = New ADODB.Connection
    'Set the connection string to our designated database (2000-2003)
    oCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\VB-Guru\Documents\RobDog888.mdb;User Id=admin;Password=;"
    'Open the connection to the db
    oCnn.Open
    'Setup the sql statement that will be our recordset's datasource (get last record)
    'Assumes a Table1 with only a single Field of Field1 name
    sSQL = "SELECT Field1 FROM Table1 ORDER BY Field1 DESC"
    'Instanciate the recordset object variable
    Set oRs = New ADODB.Recordset
    'Open the recordset
    oRs.Open sSQL, oCnn, adOpenKeyset, adLockOptimistic, adCmdText
    'Test for returned record(s)
    If oRs.BOF = False And oRs.EOF = False Then
        Text1.Text = oRs.Fields("Field1").Value
    Else
        MsgBox "No Record(s) found"
    End If
    Exit Sub
MyError:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    'Close and clean up object variables
    If TypeName(oRs) <> "Nothing" Then
        If oRs.State = adStateOpen Then oRs.Close
    End If
    If TypeName(oCnn) <> "Nothing" Then
        If oCnn.State = adStateOpen Then oCnn.Close
    End If
    Set oRs = Nothing
    Set oCnn = Nothing
End Sub
 
Private Sub cmdBOF_Click()
    If oRs.BOF = False Then
        oRs.MoveFirst
        Text1.Text = oRs.Fields("Field1").Value
    Else
        Text1.Text = vbNullString
    End If
End Sub
 
Private Sub cmdEOF_Click()
    If oRs.EOF = False Then
        oRs.MoveLast
        Text1.Text = oRs.Fields("Field1").Value
    Else
        Text1.Text = vbNullString
    End If
End Sub
 
Private Sub cmdNext_Click()
    If oRs.EOF = False Then
        oRs.MoveNext
        If oRs.EOF = False Then
            Text1.Text = oRs.Fields("Field1").Value
        Else
            oRs.MovePrevious
        End If
    End If
End Sub
 
Private Sub cmdPrevious_Click()
    If oRs.BOF = False Then
        oRs.MovePrevious
        If oRs.BOF = False Then
            Text1.Text = oRs.Fields("Field1").Value
        Else
            oRs.MoveNext
        End If
    End If
End Sub
 
Private Sub cmdAdd_Click()
    Text1.Text = vbNullString
    'Only allow one addnew at a time until the update button is clicked
    If oRs.BOF = True And oRs.EOF = True Then
        oRs.AddNew
    Else
        If oRs.EditMode = adEditNone Then
            oRs.AddNew
        End If
    End If
End Sub
 
Private Sub cmdDelete_Click()
    oCnn.Execute "DELETE Table1 FROM Table1 WHERE Field1 = '" & Text1.Text & "';"
    'Refresh the recordset with the deleted data
    oRs.Requery
    'Check where we should position the recordset so it displays the data correctly in the textbox
    cmdBOF_Click
End Sub
 
Private Sub cmdUpdate_Click()
    oRs.Fields("Field1").Value = Trim$(Text1.Text)
    oRs.Update
    'Refresh the recordset with the updated data
    oRs.Requery
End Sub

[Hack]

i read the ADO Beginners Tutorial by beacon. i still dont undertand. is there easier tutorial or can someone help me please? thank you

That tutorial is as basic as it gets.

What specifically don't you understand?

Did RobDog888's example help?

[BrainA]

Here is my quick attempt at a small ADO tutorial/demo. Sure you may need to add more error handling/trapping but this should give you the basics so you can get a feel for how it all works.

Assumes you wanted VB 6 code and Access 2000-2003
Access 2002-2003 compatible database attached
Change the path to the db to reflect the location on your system
This demo functions just like Access Forms do. Click Add first to set it in editmode. Then add the text and cick update.

[highlight=vb]Option Explicit
'Add a reference to MS ActiveX Data Objects x.x Library
'Declare our connection and recordset objects
Private oCnn As ADODB.Connection
Private oRs As ADODB.Recordset

thx for the example..but when i tried to run it says "Compile Error:
User-defined type not defined" and it highlights "oCnn As ADODB.Connection" i did change the location and it doesnt run? what should i do?

[RobDog888]

Did you add a reference to ADO linke shown in the comments?

[BrainA]

Did you add a reference to ADO linke shown in the comments?

I believe so. can you show me where they are please? thanks

[RobDog888]

Project > References > check "MS ActiveX 2.x Data Objects Library > click OK.

[BrainA]

Project > References > check "MS ActiveX 2.x Data Objects Library > click OK.

thx man it works..ill figure the rest out myself..thanks much