For more information on this control, see Microsoft's documentation on their website

Object Creation

For all Pocket PC devices, including Windows Mobile devices, you should use:
Set RS = CreateObject("adoce.recordset.3.0")
or
Set RS = CreateObject("adoce.recordset.3.1")

Methods

The ability to do SQL (Standard Query Language) commands is what makes ADO so powerful. There are lots of books and resources that describe SQL. Not all parts are currently supported by ADOCE, but the following subset should work OK.

ADO and Pocket Access

For those of you attempting to use ADO and Pocket Access here is a simple example of how this is done. Hats off to Matt Woodward and John Riekena at Microsoft for their help. This example loads the names from the Employees table in the Northwind database located in "My Documents".

Private Sub cmbTest_Click()
  Dim EmployeeRS

' Setup an error handler.
  On Error Then Resume Next

' Retrieve data from the Employee table.
  Set EmployeeRS = CreateObject("adoce.recordset")
  EmployeeRS.Open "SELECT * FROM Employees", "\My Documents\Northwind.cdb",
adOpenDynamic, adLockOptimistic

' Loop through the recordset loading names into a combo box.
  Do While Not EmployeeRS.EOF
    cmbEmployees.AddItem EmployeeRS.Fields("LastName") & ", " &
EmployeeRS.Fields("FirstName")
    EmployeeRS.MoveNext
  Loop

' Select the first employee in the list.
  cmbEmployees.ListIndex = 0

End Sub

ADO and External Databases

ADOCE on H/PC Pro does support external databases. To utilize them you pass the filename of the external database as a string in the connection parameter. Larry posted something about this recently, but a recap doesn't hurt.

ADOCE also supports the create database syntax "create database '\database.cdb'" and "drop database '\database.cdb'". Here's a bit of sample code that always recreates a a database with a table called t1 in it:

    ' include constants from Appendix A below as needed

    On Error Resume Next

    rs.Open "drop database '\ado.cdb'"
    On Error GoTo 0

    rs.Open "create database '\ado.cdb'"
    rs.Open "create table t1 (c1 integer, c2 varchar(200))", "\ado.cdb"

    rs.Open "select c1, c2 from t1", "\ado.cdb", adOpenDynamic, adLockOptimistic

    rs.AddNew
    rs.Fields("c1") = 1
    rs.Fields(1) = "t1 -- One"
    rs.Update

    rs.AddNew
    rs.Fields("c1") = 2
    rs.Fields(1) = "Xt1 -- Two"
    rs.Update

    rs.AddNew
    rs.Fields("c1") = 3
    rs.Fields(1) = "t1 -- Three"
    rs.Update

    rs.AddNew
    rs.Fields("c1") = 4
    rs.Fields(1) = "Xt1 -- Four"
    rs.Update

    rs.AddNew
    rs.Fields("c1") = 5
    rs.Fields(1) = "t1 -- Five"
    rs.Update

    rs.AddNew
    rs.Fields("c1") = 6
    rs.Fields(1) = "t1 -- Six"
    rs.Update

    rs.Close

This program allows the User to enter a SQL command eg Create table, Select etc.
'In the case of a command that produces a recordset (ie select) then the first three fields
'of the recordset are displayed. The user may Add new records, modify existing data or
'delete existing records.

Option Explicit
'-----------------------------------------------------------
' ADO Cursor Types
'-----------------------------------------------------------
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
'-----------------------------------------------------------
' ADO Lock Types
'-----------------------------------------------------------
Const adLockReadOnly = 1
Const adLockOptimistic = 3

dim cmdAddNew_enabled, cmdDelete_enabled, sqlCommand, rs_opened, num_fields, RS
rs_opened = 0
sqlCommand=""
form_load
on error resume next
set RS=createObject("adoce.recordset")
if err then
  err.clear
  set RS=createObject("adoce.recordset.3.0") 'for Pocket PC
end if  
if err then
  msgBox "Active Data Objects (ADOCE) must be installed for this sample to work. Please check the Tech Notes for more information."
  err.clear
  bye
end if
on error goto 0

Sub Execute_Click()
  On Error Resume Next
  Dim fcol
  cmdaddnew_Enabled = False
  cmdDelete_Enabled = False

  If rs_opened = 1 Then ' previous command has used the Recordset object. Close the old one
    RS.Close
    If Err Then ChkErr
    rs_opened = 0
  End If
'
'Open the ADOCE Recordset using the SQL statement that the user has typed in.
'Using adOpenKeyset to allow backward scrolling and adLockOptimistic to allow
'entry of data into the database
'
  RS.Open txtsql.text, , adOpenKeyset, adLockOptimistic
  txtResult.cls
  txtResult.drawText Hex(Err) & " -" & Err.Description
  if err then exit sub
  Set fcol = RS.fields
  Num_fields = fcol.Count
  
  If Num_fields Then
    '
    'If the Recordset returned has fields then assume that the sql statement was a Select
    'and has returned a recordset to browse. If Num_fields has no value then the SQL will
    'still have been executed eg a create table command.
 
    'Disable / Enable the Addnew button if the Addnew method is supported for this recordset
    cmdaddnew_Enabled = RS.supports(adAddNew)
 
    'Disable / Enable the Delete button if the delete method is supported for this recordset
    cmdDelete_Enabled = RS.supports(adDelete)
    rs_opened = 1
    RefreshForm
  End If
End Sub

Sub RefreshForm() 'This routine transfers the data to the form
  dim s
  'On Error Resume Next
  Dim NumFieldstoDisplay
  Dim fcol    ' FIELDS COLLECTION
  Dim fld     ' FIELD
  Dim lp      ' Loop value

'Display Status bar information
  txtcrecs.cls
  txtcrecs.drawtext "Record #" & RS.absoluteposition & " of " & RS.recordcount & " records."

'Create field object for fastest access
  Set fcol = RS.fields
'
'loop thru the field collection and display each field

  NumFieldstoDisplay = min(12, Num_fields)
  s=""
  For lp = 1 To NumFieldstoDisplay
    'create field object from the fields collection
    Set fld = fcol(lp - 1)
    If Err Then ChkErr

    s=s & fld.name
    s=s & ":" & fld.value
    s=s & vbCrLf
  Next
  txtField1.cls
  txtField1.fontname="arial"
  txtField1.fontsize=8
  txtField1.drawtext s
End Sub

Sub addnew_Click()
  if cmdAddNew_enabled=false then exit sub
  On Error Resume Next
  Dim NumFieldstoDisplay, lp, f(3)

  NumFieldstoDisplay = min(2, Num_fields)
  If NumFieldstoDisplay  0 Then
 
    'swap the data from the form into an Array
    f(1) = txtField1
    If NumFieldstoDisplay = 1 Then f(2) = txtField2
    If NumFieldstoDisplay = 2 Then f(3) = txtField3
 
    'Transfer the data to the field value property prior to the update. 
    'Remember that the Fields collection is Zero based.
    RS.addnew
    For lp = 1 To NumFieldstoDisplay
      RS.fields(lp - 1).Value = f(lp)
      If Err.Number Then ChkErr
    Next
 
    RS.Update
    If Err.Number Then ChkErr
  End If
End Sub

Sub Delete_Click()
  if cmdDelete_enabled=false then exit sub
  On Error Resume Next
  RS.Delete
  If Err <> 0 Then ChkErr
  cmdDelete_Enabled = False
  RefreshForm
End Sub

Sub ChkErr()
  On Error Resume Next
  If Err.Number <> 0 Then
    MsgBox "Error: " & Hex(Err) & " -" & Err.Description
  End If
End Sub

Sub cmdMoveFirst_Click()
  if rs_opened=0 then exit sub
  On Error Resume Next
  RS.MoveFirst
  If Err <> 0 Then ChkErr
  RefreshForm
End Sub

Sub cmdMoveLast_Click()
  if rs_opened=0 then exit sub
  On Error Resume Next
  RS.MoveLast
  If Err <> 0 Then ChkErr
  RefreshForm
End Sub

Sub cmdMoveNext_Click()
  if rs.absolutePosition=rs.recordCount then exit sub
  On Error Resume Next
  RS.MoveNext
  If RS.EOF Then RS.MoveLast
  If Err <> 0 Then ChkErr
  RefreshForm
End Sub

Sub cmdMovePrev_Click()
  if rs.absolutePosition<=1 then exit sub
  On Error Resume Next
  RS.moveprevious
  If RS.BOF Then RS.MoveFirst
  If Err <> 0 Then ChkErr
  RefreshForm
End Sub

Sub Form_Load() 'set up screen objects

  addObject "textbox","txtsql",10,10,300,18
  txtsql.text= ""

  addObject "picturebox","txtField1",10,30,300,98
  txtField1.borderstyle=1
  
  addObject "commandButton","Execute",320,10,60,18
  addObject "commandButton","AddNew",320,30,60,18
  addObject "commandButton","Delete",320,50,60,18

  addObject "picturebox","txtResult",10,132,300,18
  txtResult.borderStyle=1

  addObject "commandButton","cmdMoveFirst",320,112,30,18
  cmdMoveFirst.text= "<<"
  addObject "commandButton","cmdMovePrev",350,112,15,18
  cmdMovePrev.text="<"
  addObject "picturebox","txtcrecs",320,132,120,18
  txtcrecs.borderstyle=1
  addObject "commandButton","cmdMoveNext",395,112,15,18
  cmdMoveNext.text= ""
  addObject "commandButton","cmdMoveLast",410,112,30,18
  cmdMoveLast.text= ""

End Sub

Function min(a, b)
  min = a
  If b < a then min=b
End function



Here is a small procedure that implements a "browse file" based on external
variables only. It is contributed by Gilbert Vuilleumier, gilbert.vuilleumier© span.ch. 

Parameters are:
Filename     Database File name
F_ID         Array that will receive the recordset
Filter       Database filter
Gridx        Grid object name
Lines        Number of apparent lines of the grid
Cols         Columns descriptor bi-dimensionnal array, 
             one line for each column of the grid,
             Each line contains 3 elements: title (not used),
             width,column number
PS           is a 4-element array that contains X-pos, y-pos, width,
             height of the grid object

Sub BrowsePanel(FileName, F_Id, R_Set, Filter, Gridx, Lines, Cols, PS )
'**********************************************************************
  VBCEutil.WaitCursor True
  dim Ix,Jx,SQLC,S,X,Y,L,H                                              '
Create a drop list from a structured data file
  Set R_Set = CreateObject("adoce.recordset")
  if Filter ="" then
    SQLC = "SELECT * from " & FileName
  else
    SQLC = "SELECT * from " & FileName & " WHERE " & Filter
  end if
  R_Set.open SQLC ,vbNullString,1, 1
  If R_Set.RecordCount>0 Then
    R_Set.MoveFirst
    F_Id = R_Set.GetRows()
  Else
   F_Id = array("")
  End If
  Execute "NewGrid  = not IsObject(" & Gridx & ")"
  if NewGrid  then
    AddObject "GridCtrl.GridCtrl.1",Gridx,PS(0),PS(1),PS(2),PS(3)
  Else
    X = PS(0) * 15
    Y = PS(1) * 15
    L = PS(2) * 15
    H = PS(3) * 15
    Execute Gridx & ".Move " & X & "," & Y & "," & L & "," & H
'    GR_ID.Move 0,30
    Execute Gridx & ".CellBackColor = vbWhite"
  end if
  Execute Gridx & ".Rows = " & R_Set.RecordCount
  Execute Gridx & ".Cols = " &  1+ Ubound(Cols(0))
  For Ix=0 to Ubound(Cols)
    Jx = Cols(Ix)(1) * 13.5
    Execute Gridx & ".ColWidth(" & Ix & ") = " & Jx         ' twips->pixels conversion
  Next 'Ix
  For Jx=0 To R_Set.RecordCount-1
    For Ix=0 to Ubound(Cols(0))
      S = F_Id(Ix,Jx)
      Execute Gridx & ".TextMatrix(" & Jx & "," & Ix & ") = " & chr(34) & S & chr(34)
    Next 'Ix
  Next 'Jx
  R_Set.close
  set R_Set = nothing
  Dim code
  code = "sub " & Gridx & "_EnterCell" & vbCrLf & Gridx & ".CellBackColor =vbRed" &_
vbCrLf & "N_Categ.Text = GR_ID.TextMatrix(GR_ID.RowSel,1)" & vbCrLf & "End Sub" & vbCrLf
'  Execute code
  code = "sub " & Gridx & "_LeaveCell" & vbCrLf & Gridx & ".CellBackColor = vbWhite" &_
vbCrLf & "End Sub" & vbCrLf
  Execute code
  VBCEutil.WaitCursor False
End Sub

'-----------------------------------------------------------
' Declarations For VBA Errors
'-----------------------------------------------------------
  Const vbErrInvalidArg = 5
  Const vbErrOverflow = 6
  Const vbErrOutOfMemory = 7
  Const vbErrOutOfRange = 9
  Const vbErrTypeMismatch = 13
  Const vbErrOutOfStringSpace = 14
  Const vbErrObjectNotSet = 91
  Const vbErrUnInitForLoop = 92
  Const vbErrBadUseOfNull = 94
  Const vbErrObjectRequired = 424
  Const vbErrCantCreate = 429
  Const vbErrNotSupported = 438
  Const vbErrInvalidAssignment = 450
  Const vbErrNotCollection = 451
'-----------------------------------------------------------
' Declarations For ADO Errors
'-----------------------------------------------------------
  Const adErrInvalidArgument = &HBB9
  Const adErrNoCurrentRecord = &HBCD
  Const adErrIllegalOperation = &HC93
  Const adErrFeatureNotAvailable = &HCB3
  Const adErrItemNotFound = &HCC1
  Const adErrObjectNotSet = &HD5C
  Const adErrDataConversion = &HD5D
  Const adErrObjectClosed = &HE78
  Const adErrObjectOpen = &HE79
  Const adErrProviderNotFound = &HE7A
  Const adErrInvalidParamInfo = &HE7C
  Const adErrInvalidConnection = &HE7D
'-----------------------------------------------------------
' ADO Cursor Types
'-----------------------------------------------------------
  Const adOpenUnspecified = -1
  Const adOpenForwardOnly = 0
  Const adOpenKeyset = 1
  Const adOpenDynamic = 2
  Const adOpenStatic = 3
  Const DEFCursor = 0
'-----------------------------------------------------------
' ADO Lock Types
'-----------------------------------------------------------
  Const adLockUnspecified = -1
  Const adLockReadOnly = 1
  Const adLockPessimistic = 2
  Const adLockOptimistic = 3
  Const adLockBatchOptimistic = 4
  Const DEFLock = 1  ' check what should be the default lock type
  Const DEFOption = " "
'-----------------------------------------------------------
' ADO Field Attributes
'-----------------------------------------------------------
  Const adFldMayDefer = 2
  Const adFldUpdatable = 4
  Const adFldUnknownUpdatable = 8
  Const adFldFixed = 16
  Const adFldIsNullable = 32
  Const adFldMayBeNull = 64
  Const adFldLong = 128
  Const adFldRodID = 256
'-----------------------------------------------------------
' ADO Field Types
'-----------------------------------------------------------
  Const adWChar = 130
  Const adBinary = 128
  Const adInteger = 3
  Const adSmallInt = 2
  Const adDouble = 5
  Const adDate = 7
  Const adUnsignedSmallInt = 18
  Const adUnsignedInt = 19
  Const adUnknown = -1
'-----------------------------------------------------------
' ADO Supports() Types
'-----------------------------------------------------------
  Const adAddNew = 16778240
  Const adApproxPosition = 16384
  Const adBookMark = 8192
  Const adDelete = 16779264
  Const adHoldRecords = 256
  Const adMovePrevious = 512
  Const adResync = 131072
  Const adUpdate = 16809984
  Const adUpdateBatch = 65536
'-----------------------------------------------------------
' ADO Absolute Page/Absolute Position Special Constants
'-----------------------------------------------------------
  Const adPosUnknown = -1
  Const adPosBOF = -2
  Const adPosEOF = -3
'-----------------------------------------------------------
' ADO Edit Modes
'-----------------------------------------------------------
  Const adEditNone = 0
  Const adEditInProgress = 1
  Const adEditAdd = 2

ADOCE for Palm-size PC

I downloaded the Palm-PC SDK v1.2 yesterday from MS and did a custom install of only the ADOCE files. In \Program Files\WINCE SDK\wce211\ms palm size pc\adoce\ is the file MSCEADODT.DLL and in the mips41 folder are 5 more files:

• adoce.dll
• adocedb.dll
• adocereg.dll
• adoceres.dll
• adosync.dll

I copied all 6 of these to the PPC and registered adoce.dll

The nsBasic "View Controls.vb" app when run now shows ADO at the top of the list.

I use a db manager app called FlashBackDB from developerone.com and it now shows four additional files (created when I registered adoce.dll?):

• MSysFields
• MSysIndexes
• MSysProcs
• MSysTables

I modified some nsBasic sample code (to enter a SQL string and manipulate tables) and reformatted it for the PPC (will send to nsBasic later). With this, I was able to do a select on the MSysTables and see the four records in it.

I created some small apps using nsBasic with the ADO Control that:

• create a table (now shows up in the FlashBackdb list of tables)
• deletes a table (removes from db list)
• enter, update, delete records in the table.

I was also able to create and manipulate external db files (Pocket Access format .cdb)

INTERACTION OF WIN-CE SERVICES 2.2:

You can do 3 out of the possible 4 scenarios of file transfer:

1. Create an Access .mdb file and do an Import with Win-CE Svcs Mobile Devices which converts the file to a Pocket Access .cdb file on the PPC.

2. Copy a .mdb file from Desktop to PPC and it automatically converts it to .cdb

3. Do an Export with Win-CE Svcs Mobile Devices which lists tables in the CE Data Store that were created ONLY with ADOCE (excluding the Msys* tables) and creates a .mdb file on the Desktop.

I can't find ANY way to Import/Export/Copy a .mdb file to a CE Data Store table. This means that when you create an app that uses a data table, you will have to use an external .cdb file or create the table from sourcecode if you want it in the CE Data Store.

Sample for accessing Microsoft SQL from NS Basic/CE

Dim objADO
Dim objRs
Dim strSQL
Dim strUserID

SqlOdbc="sql"
'Create ADO Object
Set objADO = CreateObject("ADODB.Connection")

' **** Open MS SQL DB by ADO ***
objADO.Open="SQL"


'Set SQL
strSQL="SELECT * from daily "

'Set DynaSet(RecordSet) Object
Set objRS = objADO.Execute(strSQL)


'Show records data
Do Until objRS.Eof = True

'Show the field
Print objRS(0),objRS(1),objRS(2),objRS(3) 

'move next record
objRS.MoveNext

Loop


'Close ADO
objADO.Close