Re: Need example of using ADO DataControl and DataGrid control - V

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ant (Ant_at_discussions.microsoft.com)
Date: 01/22/05 

Date: Sat, 22 Jan 2005 04:15:01 -0800

Hey L Mehl.

MSDN is where I learnt ADO. Just follow the little purple books via:
MSDN library - Platform SDK - Database & Messaging services - Microsoft Data
Access SDK - Microsoft ActiveX Data Objects - ADO Programmers reference -
Learning ADO

But just to get you started, here's a little code using two buttons & a
datagrid to get you going. It's only one way of doing it but it will suffice
as an example. As you'll find, you can use ADO in many differnt ways.

Just cut & paste this into a form
Ant

'*********************************************************************************
' First set a reference to the ADO object by clicking 'Project', then
scrolling *
' down to references & clicking that. Scroll down &
    *
' Check 'Microsoft ActiveX Data Objects 2.X Library'. The reference is now
set. *
      *
'
    *
'*********************************************************************************

    'Declare the objects. You need a connection object to create a conection
    ' to the database & a recordset object to be used as a cursor to store
    ' the retrieved records
    Private mCn As ADODB.Connection
    Private mRs As ADODB.Recordset
    

Private Sub Form_Load()
' Add a datagrid & two command buttons to your form
' keep the names datagrid1, command1 & command2

   
    
    ' Used to build the connection string & SQL string
    Dim strSQL As String, strCn As String
    
    ' Instantiate the ADO object (Set the objects to the variables)
    ' Learn the proper way to destroy them after use. But for now don't worry.
    Set mCn = New ADODB.Connection
    Set mRs = New ADODB.Recordset

    
    
    ' Create connection string to access the database
    strCn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security "
    strCn = strCn & "Info=False;Initial Catalog=pubs"
    
    ' Create the query
    strSQL = "SELECT TOP 10 title, notes FROM titles"
    
    ' Pass the connection the connection string then open the connection
object
    With mCn
        .ConnectionString = strCn
        .Open
    End With
    
    ' Set up the recordset. Just use these settings for now
    With mRs
        .ActiveConnection = mCn
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Source = strSQL
        .Open
    End With
    
    ' Bind the recordset to the data grid. Must use set to set an object
    Set DataGrid1.DataSource = mRs
End Sub
Private Sub Command1_Click()
    ' Use the recordset object to scroll through the records
    With mRs
        ' If the cursor is not before the first record (Beginning Of File)
        'then move to the previous record
        If Not .BOF = True Then .MovePrevious
    End With
End Sub
Private Sub command2_click()
    ' Use the recordset object to scroll through the records
    With mRs
        ' If the cursor is not past the last record (End Of File), then move
to the
        ' next record
        If Not .EOF = True Then .MoveNext
    End With
End Sub

Relevant Pages

  • Re: Query error after converting
    ... If removing the reference to ADO solved the problem, ... I already mentioned that Recordset is an object in both the ADO and DAO ... libraries for much of its functionality, and any external library it uses is ...
    (microsoft.public.access.conversion)
  • Re: Export text file
    ... It is better to use just DAO and no ADO reference at all. ... recordset objects in the drop down list. ...
    (microsoft.public.access.externaldata)
  • Re: Help w/ Me Function? Or: concatenating a string for a ADO field reference?
    ... >Now I need that exact functionality in an ADO call: ... >ADO call? ... Me is an object reference to the form (technically the Class ... A recordset object's default collection is its Fields ...
    (microsoft.public.access.formscoding)
  • Re: Error Type Mismatch : Runtime error 13
    ... Both the DAO and ADO models have Recordset objects in them. ... You're trying to use DAO. ... Access 2000 and 2002 only have a reference ...
    (microsoft.public.access.forms)
  • Re: Datagrid record order
    ... Connection string is... ... for my recordset, IS sorted the data I get returned may not be? ... This may be done for a batch of lessons. ... Which version of ADO are you using? ...
    (microsoft.public.vb.controls)