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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Back
Top