Page 1
Page 2
I started an app a few months ago. The main
idea was to have a pocket solution for technicians on the road.
They needed to be able to see the work they had to do, and they
needed to register hours and material. All this had to be synchronised
between server app and pocket app.
At first, we started with a Jornada 680,
and used a Pocket Access database. After a few weeks, we saw this
had not the performance and functionality we wanted.
So, when the Jornada 720 came out in Belgium,
we bought one and took a look at SQL Server CE. Now, after a few
experiments with failures and success, I developed an app that
is a balance between design and performance. Because we didn't
need the whole database of the server app in the Pocket, we made
a strip down version and synchronise with MSMQ messages. I try
to point out the main items of how I did the SQL Server CE stuff.
Before starting
In the code snippets you'll see an error routine.
Here is the code for the Show_Error function. It uses a resource
file to get the specific message. The GetMessage function does
the same, but takes a parameter to make the message from the resource
file dynamic. For example, when the message in the resource file
reads 'There's an error in function %1', my GetMessage function
takes the first parameter to put in place of the '%1'. In my app,
it can take up to three parameters.
Public Function Show_Error(strNumber As String,
strDescription As String, strFormModule As String, strFunctionSub
As String) As Boolean
Dim strMessage As String
Dim strTitle As String
''''''''''''Error_Handling
Show_Error = False
strTitle = LoadResString(88)
strMessage = GetMessage(89, strFormModule, "", "")
strMessage = strMessage & vbCrLf
strMessage = strMessage & GetMessage(90, strFunctionSub, "",
"")
strMessage = strMessage & vbCrLf
strMessage = strMessage & strNumber
strMessage = strMessage & vbCrLf
strMessage = strMessage & strDescription
strMessage = strMessage & vbCrLf
strMessage = strMessage & LoadResString(91)
MsgBox strMessage, vbCritical, strTitle
Err.Clear
Show_Error = True
End Function
Every sub or function starts with an 'On error
resume next', and ends with a check if errors have occured. This
way application doesn't stop running 'cause of an error.
Also, every time I open a recordset, I check
for errors and close the connection and recordset before getting
out of the function. It has to be closed before opened again,
or you'll get another error !
The connection and recordset object
Since I needed a connection object and recordset
object throughout the whole app, I initialise them at the startup
of my main app. In a function called Init_Vars(), called by Main(),
I declare the objects :
Set conConnection = CreateObject("ADOCE.Connection.3.1")
conConnection.ConnectionString = CONNECTION_STRING & App.Path
& "\Database.sdf"
Set rsRecordset = CreateObject("ADOCE.Recordset.3.1")
In a module Mod_Variables I have the following
declares :
Public conConnection As Object
Public Const CONNECTION_STRING = "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0;
data source="
Public rsRecordset As Object
When the app is about to end, these objects
are set to nothing :
Set conConnection = Nothing
Set rsRecordset = Nothing
App.End
Cursor types, Lock types and Table Access
Some additional variables I declared in the
Mod_Variables, concerning recordsets :
'Recordset CursorTypes
Public Const CT_OPEN_DYNAMIC = 2 'Cursortype dynamic
Public Const CT_OPEN_FW_ONLY = 0 'Cursortype forward only
Public Const CT_OPEN_KEYSET = 1 'Cursortype keyset
Public Const CT_OPEN_STATIC = 3 'Cursortype static 'Recordset
LockTypes
Public Const LT_OPTIMISTIC = 3 'Locktype optimistic
Public Const LT_READONLY = 1 'Locktype readonly
'Recordset Table type
Public Const TT_TABLEDIRECT = 512 'Table type for inserts
That's about all I needed for my inserts,
updates and deletes
Inserting records in the database
You can use two methods of inserting records
in a table, either by building an SQL string and executing it,
or by performing an AddNew on an opened recordset
dim strSQL as string
strSQL = "INSERT INTO TableName(Field1,Field2,Field3) VALUES
(Value1,Value2,Value3)
conConnection.Open
rsRecordset.Open strSQL, conConnection
If Err.Number <> 0 Then
dummy = Show_Error(CStr(Err.Number), Err.Description, "ModuleName",
"FunctionName")
rsRecordset.Close
Err.Number = 0
End If
conConnection.Close
OR
conConnection.Open
rsRecordset.Open "TableName", conConnection, CT_OPEN_DYNAMIC,
LT_OPTIMISTIC, TT_TABLEDIRECT
rsRecordset.AddNew
rsRecordset("Field1") = Value1
rsRecordset("Field2") = Value2
rsRecordset("Field3") = Value3
rsRecordset.Update
If Err.Number <> 0 Then
dummy = Show_Error(CStr(Err.Number), Err.Description, "ModuleName",
"FunctionName")
rsRecordset.Close
conConnection.Close
Err.Number = 0
Exit Function
End If
rsRecordset.Close
conConnection.Close
I prefer the second method, because when you
have a table with many columns, the strSQL will be difficult to
build, and fields and values must follow the same order.
Next Page