Migration to MSSQL Back End from Complicated MS Access BE/FE Replicated Database

[Guest]

I currently have a MS Access 97 database, BE/FE configuration. The program
creator set up two versions of the FE, one which works with the BE directly
on the server and one which connects to a replicated version of the BE
locally to allow users to work off-line when away from the office. All
replication is handled by VB code in the FE user interface, with the on-line
FE letting users create the replicated off-line copy on their systems and
the off-line FE allowing them to reconcile the changes made off-line with
the on-line version once reconnected to the network The off-line operation
is a critical requirement.

The push now is to migrate the MS Access BE to MS SQL - to increase
recoverability, reliability, data integrity and security - but continue to
work with an Access FE. There is also a desire to upgrade the Access FE to
2002. My research, to date, indicates that for the off-line use of the
database to continue, I have the following options:

- Use a MS SQL/MS Access replication approach
(MS SQL BE replicates to a local MS Access BE, the offline MS Access FE then
links to this BE. The FE would initiate the replication/reconciliation
process)

- Use a MS SQL/MDSE replication approach
(MS SQL BE replicates to a off-line MDSE BE, offline MS Access FE then links
to MDSE BE. The FE would initiate replication/reconciliation process)

- Use a custom coded approach
(MS Access FE maintains local tables to use off-line, FE manages BE
interaction either manually or through testing for presence of MS SQL BE)

Being a relative novice with MS SQL replication, I have a few questions:

1) Is replication is the way to go for this situation? If so, what is the
best way to go and why?

2) Is there a better alternative for achieving the off-line capability that
I have missed?

Any comments or guidance would be appreciated.

thanks,

Brian Mondoux

 

[Vadim Rapp]

Hello (e-mail address removed):
You wrote in conference microsoft.public.access.adp.sqlserver on Fri, 27
Aug 2004 10:29:54 -0400:

t> FE to 2002. My research, to date, indicates that for the off-line use
t> of the database to continue, I have the following options:

t> - Use a MS SQL/MS Access replication approach
t> (MS SQL BE replicates to a local MS Access BE, the offline MS Access FE
t> then links to this BE. The FE would initiate the
t> replication/reconciliation process)

t> - Use a MS SQL/MDSE replication approach
t> (MS SQL BE replicates to a off-line MDSE BE, offline MS Access FE then
t> links to MDSE BE. The FE would initiate replication/reconciliation
t> process)

t> - Use a custom coded approach
t> (MS Access FE maintains local tables to use off-line, FE manages BE
t> interaction either manually or through testing for presence of MS SQL
t> BE)

t> Being a relative novice with MS SQL replication, I have a few questions:

t> 1) Is replication is the way to go for this situation? If so, what is
t> the best way to go and why?

I think it's the best way indeed, with your option 2 - remote central SQL
Server and local MSDE for each user. The biggest advantage here is that this
approach eliminates custom efforts to translate Access data into SQL server.


Vadim