This is a complicated question and the best answer really depends on how you
want to use the local data. There are three main options:
1. Use ADO persisted recordsets. These are XML files that can be saved
and opened using the ADO object model (MDAC 2.6 and later). This option
tends to work best with read-only data that is frequently used for
comboboxes, etc.
2. Use MSDE as the local data store and have the local MSDE link to the
remote MSDE. This option works great when you want to do a lot of local
data analysis and updating. However, it also requires more resources on
the client machine. We use this structure for point-of-sale applications
so that there is redundancy when the network goes down. If the POS
application can't connect to the remote SQL Server, then it connects to the
local MSDE and keeps on ringing up sales.
3. Continue to use Jet locally and connect to it from your ADP
programmatically. We've used this option before in situations when we
really wanted to use MSDE locally, but the client PCs couldn't reliably
support it (they were all Win98). However, there needs to be very good
reasons for choosing this format over just using an mdb file with linked
tables and pass-through queries to SQL Server. In our case, we already had
an ADP application so modifying it to use JET local instead of SQL Server
was less work than creating a new mdb from scratch.
As you can see, the options are various and the best answer requires a lot
of careful thought.
Brian M. Sockey
www.farsightsolutions.com