Before you start you must create an Access database on the desktop PC called Products.mdb. Create a table called Products with three fields Desc
, Price
and ProdID
where ProdID
is the primary key of type numeric.
Drag
and drop this mdb file into the MyMobileDevice
folder and you will see
the ConfirmFileConvert Dialog, follow the instructions to convert your
database into PocketAccess format and synchronise it with the database
you have created on your desktop.
Form Layout
We shall now create a simple application for the PocketPC which will access this data and display it on a form.
1. Open Microsoft eMbedded Visual Basic and choose “New Windows CE For Pocket PC 2002 Project”.
2. Add a Label to the form and change it’s name property to lblName, it’s caption property to Simple Data Access Example, it’s backcolor property to &H00E0E0E0& and it’s alignment property to 2 - vbCenter
3. Add another label to the form and set it’s name = lblEnter, caption = Enter a Product ID, backcolor = &H00E0E0E0& and alignment = 1 - vbRightJustify
4. Add a textbox to the form and set it’s name = txtEnter and clear it’s text property
5. Change the forms caption property to something like “Product Inventory” and it’s backcolor property to &H00E0E0E0&
6. Add a frame component to the form and change it’s caption to “Details” and it’s visible property to false
7. Add another label but instead of placing it anywhere on the form, make sure to drag it onto the frame. Change it’s backcolor to white, alignment to vbRightJustify and it’s caption property to “Description”. Repeat this process but change the label’s caption property to “Price”
8. Add a textbox to the frame clearing it’s text property and changing it’s name to txtDesc. Repeat this process for txtPrice.
9. Add a Button to the Form changing it’s style property to 1 – vbButtonGraphical, it’s BackColor to &H00FF8080& and it’s caption to “GO”. The form should look something like:
The Code
10. Add an event handler for the button – double click on your button; you should see the code editor with the following text:
Option Explicit
Private Sub Command1_Click()
End Sub
Private Sub Form_OKClick()
App.End
End Sub
11. Declare global variables - After the “Option Explicit
” statement insert the following declarations:
Dim paramSQL As String
Public conndb As ADOCE.Connection
Dim rs As ADOCE.Recordset
Public desc As String
Public price As String
Dim tempno as String
12. Open
a connection to the database and point the recordset object at it by
inserting the following code underneath the line - Private Sub
Command1_Click()
:
Set conndb = CreateObject("ADOCE.Connection.3.0")
conndb.ConnectionString = "data source = \My Documents\Products.cdb"
conndb.Open
Set rs = CreateObject("ADOCE.RecordSet.3.0")
13. Now that the connection and recordset have been created we can generate an SQL statement to retrieve records:
tempno = txtEnter.Text
paramSQL = "select Desc, Price from Products where ProdID = '" & tempno & "'"
rs.Open paramSQL, conndb, adOpenForwardOnly, adLockReadOnly
14. Check that the record has been found, if it has then make the Frame visible and display the details in our textboxes:
If Not ((rs.EOF = True) And (rs.BOF = True)) Then
Frame1.Visible=True
txtDesc.Text = rs.Fields(0)
txtPrice.Text = rs.Fields(1)
End If
Rs.Close
conndb.Close
Set conndb = Nothing
The whole code should look something like this:
Option Explicit
Dim paramSQL As String
Public conndb As ADOCE.Connection
Dim rs As ADOCE.Recordset
Public desc As String
Public price As String
Dim tempno as String
Private Sub Command1_Click()
tempno = txtEnter.Text
Set conndb = CreateObject("ADOCE.Connection.3.0")
conndb.ConnectionString = "data source = \My Documents\Products.cdb"
conndb.Open
Set rs = CreateObject("ADOCE.RecordSet.3.0")
paramSQL = "select Desc, Price from Products where ProdID = '" & tempno & "'"
rs.Open paramSQL, conndb, adOpenForwardOnly, adLockReadOnly
If Not ((rs.EOF = True) And (rs.BOF = True)) Then
Frame1.Visible = True
txtDesc.Text = rs.Fields(0)
txtPrice.Text = rs.Fields(1)
End If
Rs.Close
conndb.Close
Set conndb = Nothing
End Sub
Private Sub Form_OKClick()
App.End
End Sub
15. To run this application and see the results on your handheld device select File -> Make Project1.vb…, the standard Windows dialog box will appear and you can save your application in the chosen folder. If you are running the PPC 2003 O/S it is not possible to run your application using the emulator. It is necessary therefore to open windows Explorer and navigate to the Mobile Device folder, ensuring your PDA is in its cradle. Drag and drop the Project1.vb application from its current location onto this Explorer Window, the application is now ready to use. To view this first application select Start -> Programs -> File Explorer on your hand-held device, navigate to the My Documents folder and click the Project1 icon.
Comments