Automate replication

[Guest]

Hi,
Access 2002, Windows XP.
I have a db that I have split, multi-user environment and have set up basic
security with wizard.
We have a requirement for a couple of the users to be able to use the db
remotely on their laptops.
Replication seems the way to go, but have not done this before.
I also want to automate the process to make it as simple for the users as
possible.

It appears that the process is more complex than appears at first glance.
e.g. passwords, autonumbers, etc needing to be re-set.

I have downloaded a 'Whitepaper' document but that looks a bit daunting. Any
advice going forward is much appreciated.

 

[aaron.kempf]

use SQL Server / MSDE on the laptops

it's a much much better solution than this MDB _CRAP_

 

[David W. Fenton]

Access 2002, Windows XP.
I have a db that I have split, multi-user environment and have set
up basic security with wizard.
We have a requirement for a couple of the users to be able to use
the db remotely on their laptops.
Replication seems the way to go, but have not done this before.
I also want to automate the process to make it as simple for the
users as possible.

It appears that the process is more complex than appears at first
glance. e.g. passwords, autonumbers, etc needing to be re-set.

I have downloaded a 'Whitepaper' document but that looks a bit
daunting. Any advice going forward is much appreciated.

If your users are going to work outside the office, and then come
back into the office, hook up to the LAN and then synch with the
shared back end in the office, you can do it the easy way, with
DIRECT replication.

This is done with code like this:

Dim dbLocal As DAO.Database

Set dbLocal = DBEngine.OpenDatabase("localreplica.mdb")
dbLocal.Synchronize "remotereplica.mdb"

dbLocal.Close
Set dbLocal = Nothing

The local replica would be the one stored on the laptop. The remote
replica would be the one on the server in the main office.

You may need to supply username/password/systemDB arguments to the
OpenDatabase command. In general, if you're using the same workspace
as the current MDB is running in, you won't need to change
workgroup, but you may need to supply the username/password. I
generally create a username "Synchronizer" with no password for
doing synchronization.

But that could be a security risk if you're using security to
protect your back-end data.

 

[Guest]

Thanks David,
sorry for the delayed response.
I'm not that clued up on scripting but assume that this is VB?
Could you give me a little more info on the script (Where do I add it) and
how to add/build the OpenDatabase command (I assume that I do this in a
macro)?

Cheers,

Graeme

 

[David W. Fenton]

I'm not that clued up on scripting but assume that this is VB?
Could you give me a little more info on the script (Where do I add
it) and how to add/build the OpenDatabase command (I assume that I
do this in a macro)?

The code is VBA. The most obvious thing to do would be to create a
form with a command button on it and paste the code into the command
button's OnClick event.

DBEngine.OpenDatabase() is a function of the DBEngine object, which
is a member of the Application object. You don't have to do anything
to use it, as it's there in Access available for you to use.

 

[Guest]

Hi David,
I've added the script.
Excuse the my ignorance, but running the script nothing appears to happen. I
think I have got my script wrong.
where you have "localreplica.mdb" am I meant to insert the name of the
database there?

I also assume that this script synchronises the two databases.
It does not seem create a replica though, or am I wrong?

Sorry, but this replication stuff is all new to me!

 

[David W. Fenton]

I've added the script.
Excuse the my ignorance, but running the script nothing appears to
happen. I think I have got my script wrong.
where you have "localreplica.mdb" am I meant to insert the name of
the database there?

Yes, that is correct.

I also assume that this script synchronises the two databases.
Yes.

It does not seem create a replica though, or am I wrong?

No. You can do that in code, but it's more complicated (this is one
area where JRO is easier than DAO, the only one I can think of, in
fact). But I've never in 10 years of using replication in apps for
clients created a replica in code -- I always use the Access user
interface.

Sorry, but this replication stuff is all new to me!

It was new to me at one time, and it took me years to figure it out.
That's why I make a point of trying to make sure that all questions
about replication in the Access newsgroups get an answer.

 

[Guest]

Thanks David.
Don't know what JRO or DAO stands for but that was a great help.
Cheers,
Graeme

 

[David W. Fenton]

Don't know what JRO or DAO stands for but that was a great help.

DAO is "Data Access Objects" and applies to the Jet database engine,
which is what you are using when you use an Access (Jet) MDB to
store your data, and particularly when you are using Jet Replication
(i.e., Access replication).

JRO is "Jet Replication Objtects," which has no real purpose for
existing except that Microsoft made a decision to promote ADO
(Activex Data Objects) as their default data access method for all
kinds of data. ADO is a *great* way to work with SQL Server data,
but for Jet, it's stupid, as it has to translate into Jet's native
format, whereas DAO is the native interface for Jet, with no
translation. ADO is a data abstraction layer for all database
engines that provide an ADO driver, thus, JRO was needed to provide
access to features specific to Jet that were not included in the
basic set of features supported by ADO.

The result is that if you choose ADO as your data access library for
Jet data, you have to use JRO to use Jet-specific features (like
compacting and synchronization between replicas).

However, Microsoft was evil in planning how they would support the
new version of Jet that came out at the same time they made ADO the
default data access library -- and the result is that ADO is the
only way to access certain esoteric Jet features, as MS chose not to
update DAO to be able to do the same things. In regard to
replication, JRO is the only way to use Microsoft-provided libraries
and initiate an indirect synchronization.

If you don't know what that refers to, then you're going to be fine
with plain old DAO!