Page 1
Page 2
Page 3
Pulling the Customer table
We
now get to the raison d'être of this article. We will use the RDA object
to pull data from a remote SQL Server (SS) into a table in our local SQL
Server CE (SS CE) database. The most important pieces of information for this
operation are: specifying the local table name, the data we want to pull, where
we are pulling the data from; the other options are essentially switches. But
first let's take a look at the overall process.
Clicking
on the "Pull Customer Data" button will do the following:
-
delete the existing local copy of the Customers table
-
pull the Customers table from the remote SS using the SQL Server CE RDA Object;
the syntax of which is:
ceRDA.Pull(LocalTableName, SQLSelectString, OLEDBConnectionString,
RDA_TRACKOPTION, ErrorTableName) - load the Customers
from SS CE into the listbox
Deleting the existing
local copy
This is necessary since we can't pull a table
if the local SS CE copy already exists. There are obviously several ways to do
this, one method for small databases is to use the ADOXCE catalog object and some
delete SQL.
Pull the Customers table from the remote
SS
We use the SQL Server CE RDA Object to pull the data
from the remote SS. Examining the code:
'pull the customers
Call RDAPull("locCustomers", _
"SELECT CustomerID, CompanyName
FROM Customers", _
"locErrCustomers", "Customers uploaded.")
you
will see that we are using a sub called RDAPull to encapsulate this process.
We pass:
- paramLocalTblName:
the name of the local SS CE table for holding the Customer information
- paramSQL:
the SQL string which will select the information
- paramLocalErrTblName:
the name of the table for logging errors
- paramMsg:
a message to display in the status bar if we are successful
The
RDAPull sub takes these parameters and assigns them to the appropriate
properties of the SQL Server CE RDA Object:
'init
RDA objects
Set ceRDA = CreateObject("SSCE.RemoteDataAccess.1.0")
ceRDA.LocalConnectionString = gSQLEProvider & gFullFileSpec
ceRDA.InternetURL
= gInternetURL
ceRDA.InternetLogin = gInternetLogin
ceRDA.InternetPassword
= gInternetPwd
'Pull the database
ceRDA.Pull paramLocalTblName, _
paramSQL, _
gRemoteConnect, _
TRACKINGON, _
paramLocalErrTblName
Set ceRDA = Nothing
UpdateStatus paramMsg
Examining
the ceRDA.Pull method, you can see we are specifying the LocalTableName
(paramLocalTblName), the SQLSelectString
(paramSQL) and the OLEDBConnectionString
(gRemoteConnect); but what about the other two parameters;
TRACKINGON and paramLocalErrTblName?
These two parameters go hand in hand. If you specify the TRACKINGON
option it tells SS CE to track any changes to the pulled table. When you push
the table any errors that occur will be stored in the table specified by the paramLocalErrTblName
parameter. This only applies if you use TRACKINGON (as opposed to TRACKINGOFF)
and as such comes into play when you call the RDA Push method.
So
how do we handle Pull errors?
The SQL Server CE RDA ErrorRecords
property contains the SSCEErrors collection. Reading this property will
tell you if any errors have occurred. The code we use is:
If ceRDA.ErrorRecords.Count > 0 Then
Call ShowErrors(ceRDA.ErrorRecords)
Else
UpdateStatus paramMsg
End If
The ShowErrors
module is copied directly from the SS CE online books.
After
we have successfully pulled the customer information, the table is read from the
local SS CE table locCustomers into the listbox using the LoadIntoCtrl
routine.
'load the customers into the listbox
Call
LoadIntoCtrl(Form1.lbCustomers, _
"select CustomerID, CompanyName from
locCustomers")
And there you have it! You have just
pulled a remote SQL Server table into SQL Server CE on your Pocket PC.
Previous Page