Multi User Replication...

  • Thread starter Thread starter Goobz
  • Start date Start date
The backend or the complete database!?
Click to expand...

The back-end is the only thing that should be on the server unless you are
running a Terminal Server session. If you are running Terminal Server, the
only reason to replicate is if the client computer is running without a
connection to the Terminal session.

The point is that each user must have his/her own copy of the front-end. It
is never shared, for any reason. The back-end is running on Jet and has
built-in handling for multiple connections to the data.
 
The "conflict resolver" is actually somewhat misnamed, as it's not
really resolving the conflict so much as *confirming* it. When you
see conflicts, they've already been resolved, in favor of the data
in what is presented as the WINNING RECORD. If you do nothing, the
conflict has already been resolved. The conflict resolver only gives
you the opportunity to confirm that Jet resolved it correctly, or to
override Jet's resolution, or to mix up data from both records into
the winning record (by copying and pasting data from the two
versions of the record).
Click to expand...

Much better explanation than mine.
 
The back-end is the only thing that should be on the server unless you are
running a Terminal Server session. If you are running Terminal Server, the
only reason to replicate is if the client computer is running without a
connection to the Terminal session.

The point is that each user must have his/her own copy of the front-end. It
is never shared, for any reason. The back-end is running on Jet and has
built-in handling for multiple connections to the data.
Click to expand...

Okay.. That just explained probably 99.9% of my questions in one
sentence about the Jet capabilities. So if I have 3 people that all
need to use that database, mainly a Form called "DN Update". I can
have all users with their own FE copy of say phones.mde on their local
system, then that will allow all 3 users to be in DN Update form at
the same time, making changes to the same database, without any
conflicting issues!? So if one user updates extension 1234, another
user can see that information instantaneously when they bring up the
data within seconds, correct!? Bascially just reloading the record for
that extension!?

Then, all I would have to do is setup an automated bacup of the
database, and it doesn't matter if anyone nukes their FE copy, because
they are all the same, correct!?
 
So then basically when you are saying "Linked tables to the BE on the
server", basically you are saying link them through the Link Table
Manager!? I currently have one table linked, "Employee Master", which
I am linking from another database currently..

Yes, link them all. The linked table manager is only useful when links
already exist. To create them use:

File >>> Get External Data >>> Link Tables

and select them all from the backend database. There are several utilities
to help break the replication. First MAKE A BACKUP. Always work from a
backup. Then split the tables out and make a backup of that.

Use the KB article first. If that doesn't work, try:

http://www.trigeminal.com/lang/1033/utility.asp?ItemID=7#7

Lastly, I've had problems that demanded that I manually break the
replication. To do that you need to use Make-Table queries to build all new
tables with the exception of the 3 to 5 replication fields, which you will
leave off of the new tables. Then delete the existing replication tables and
rename and reset the referential integrity on the new tables. On an average
size database with 30 to 40 tables, it will take several hours of work.
 
So then basically when you are saying "Linked tables to the BE on the
server", basically you are saying link them through the Link Table
Manager!? I currently have one table linked, "Employee Master", which
I am linking from another database currently..

Yes, link them all. The linked table manager is only useful when links
already exist. To create them use:

File >>> Get External Data >>> Link Tables

and select them all from the backend database. There are several utilities
to help break the replication. First MAKE A BACKUP. Always work from a
backup. Then split the tables out and make a backup of that.

Use the KB article first. If that doesn't work, try:

http://www.trigeminal.com/lang/1033/utility.asp?ItemID=7#7

Lastly, I've had problems that demanded that I manually break the
replication. To do that you need to use Make-Table queries to build all new
tables with the exception of the 3 to 5 replication fields, which you will
leave off of the new tables. Then delete the existing replication tables and
rename and reset the referential integrity on the new tables. On an average
size database with 30 to 40 tables, it will take several hours of work.
Click to expand...

Okay...

I had a backup of the thing here locally on my PC... So I ran the
utility that was suggested, and it did just as it said - it is no
longer a replicable database. I then "Split" the database, and see
that all my tables are now Linked tables, and if I do the manager,
they are all linked to the c:\accessstuff\redmond\phones.mdb, which is
where I put the actual database at to test with, so I see that is
working fine...

I then went and ran the "Make MDE File" from the tools menu, and on my
desktop, I have a "phones.mde" file, which also has a padlock on it.
Does that mean that it is going to lock the database in exclusive mode
when one user is in it, so no one else can make any changes, or is
that just the normal icon?

I tried one of my forms, and it seems to be operating flawlessly, and
what do you know.. I even have my original action of when I click the
"Next Record" arrow on the bottom, my DN field is back to sequential
order like it was before I had the almost 8,000 conflicts! :)
 
The only possible conflict is if two users are trying to edit the same
record at the same time. One user will have to wait. Yes, the moment the
second user reloads his form or even requeries the recordsource, all changes
are seen.

Best practice is to keep a pristine copy of the front-end on the server,
already linked to the back-end. If anyone corrupts their local copy, the
quick fix is to delete the local copy and copy the pristine front-end that
lives on the server. That way they always have a perfect copy available, and
you are even down more than a few seconds to a minute.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

The back-end is the only thing that should be on the server unless you are
running a Terminal Server session. If you are running Terminal Server, the
only reason to replicate is if the client computer is running without a
connection to the Terminal session.

The point is that each user must have his/her own copy of the front-end.
It
is never shared, for any reason. The back-end is running on Jet and has
built-in handling for multiple connections to the data.
Click to expand...

Okay.. That just explained probably 99.9% of my questions in one
sentence about the Jet capabilities. So if I have 3 people that all
need to use that database, mainly a Form called "DN Update". I can
have all users with their own FE copy of say phones.mde on their local
system, then that will allow all 3 users to be in DN Update form at
the same time, making changes to the same database, without any
conflicting issues!? So if one user updates extension 1234, another
user can see that information instantaneously when they bring up the
data within seconds, correct!? Bascially just reloading the record for
that extension!?

Then, all I would have to do is setup an automated bacup of the
database, and it doesn't matter if anyone nukes their FE copy, because
they are all the same, correct!?
 
The only possible conflict is if two users are trying to edit the same
record at the same time. One user will have to wait. Yes, the moment the
second user reloads his form or even requeries the recordsource, all changes
are seen.

Best practice is to keep a pristine copy of the front-end on the server,
already linked to the back-end. If anyone corrupts their local copy, the
quick fix is to delete the local copy and copy the pristine front-end that
lives on the server. That way they always have a perfect copy available, and
you are even down more than a few seconds to a minute.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com






Okay.. That just explained probably 99.9% of my questions in one
sentence about the Jet capabilities. So if I have 3 people that all
need to use that database, mainly a Form called "DN Update". I can
have all users with their own FE copy of say phones.mde on their local
system, then that will allow all 3 users to be in DN Update form at
the same time, making changes to the same database, without any
conflicting issues!? So if one user updates extension 1234, another
user can see that information instantaneously when they bring up the
data within seconds, correct!? Bascially just reloading the record for
that extension!?

Then, all I would have to do is setup an automated bacup of the
database, and it doesn't matter if anyone nukes their FE copy, because
they are all the same, correct!?- Hide quoted text -

- Show quoted text -
Click to expand...

Okay.. In that sense, what are the disadvantages, to me creating a
folder on the server somewhere, with a bunch of MDE files. The reason
I say a bunch, is because each MDE file that is made, will have a
different database startup, and just have the users run the MDE's from
there!? The MDE files will be on the public file server, and the MDB
will be snuggled safely somewhere deep in folders, on another server
on the LAN.
 
Use the KB article first. If that doesn't work, try:

http://www.trigeminal.com/lang/1033/utility.asp?ItemID=7#7
Click to expand...

I would recommend the *opposite* -- use the TSI Unreplicator first,
and only do it manually if that doesn't work.
Lastly, I've had problems that demanded that I manually break the
replication.
Click to expand...

I've done it both manually and with the unreplication tools (both
the A97 wizard created by Microsoft, and the TSI UnReplicator), but
it's not something I do often, so I've never experienced the kind of
problem you mention. Can you go into more detail?

It definitely is time-consuming if you have to do it manually,
mostly because of Referential Integrity.
 
m:
All the users are local... There are 3 users that are working here
on my current database, besides myself. We have a 4th person who
has a local copy of a specific table inside the database, and he
just basically uploads/cuts&pastes the changed information into
his table, which has no relationships anywhere else on the table,
at this point...
Click to expand...

The 4th user should have a separate MDB with his/her data table in
it. He/she can copy it over the VPN to your server and you can have
a static link to this copy of his/her data in your front end so that
others can use it.

Make sense?

In short, replication was not needed.

You weren't even using it, right?
 
m:
So if I have 3 people that all
need to use that database, mainly a Form called "DN Update". I can
have all users with their own FE copy of say phones.mde on their
local system, then that will allow all 3 users to be in DN Update
form at the same time, making changes to the same database,
without any conflicting issues!? So if one user updates extension
1234, another user can see that information instantaneously when
they bring up the data within seconds, correct!? Bascially just
reloading the record for that extension!?
Click to expand...

Well, the answer is YES and NO.

If a user updates a data field and doesn't save the record, other
users won't see the change.

If another user is viewing the same record and the first user saves
a change, the other user won't see the new data until the refresh
interval has passed (that's usually 1 second).

Anyone loading the record fresh will see the updated data as soon as
the updates are saved.
 
:
what are the disadvantages, to me creating a
folder on the server somewhere, with a bunch of MDE files. The
reason I say a bunch, is because each MDE file that is made, will
have a different database startup, and just have the users run the
MDE's from there!? The MDE files will be on the public file
server, and the MDB will be snuggled safely somewhere deep in
folders, on another server on the LAN.
Click to expand...

Do you run Microsoft Word from a server installation?

Nope.

So, why would you run your database application?

Remember, the MDE is that *application*, not the database (the back
end is the database). Only the database should be shared and stored
on the server.

The disadvantage is that you'll be loading the front end across the
network, which is orders of magnitude slower than loading from the
workstation's local hard drive. It also exposes the front end to
corruption from dropped connections that would not be the case if
loaded from the local hard drive. Granted, as you correctly observe
in another message, a corrupt front end can just be replaced by a
fresh copy, but it's inconvenient and interrupts the user.
 
The disadvantage is that you'll be loading the front end across the
network, which is orders of magnitude slower than loading from the
workstation's local hard drive. It also exposes the front end to
corruption from dropped connections that would not be the case if
loaded from the local hard drive. Granted, as you correctly observe
in another message, a corrupt front end can just be replaced by a
fresh copy, but it's inconvenient and interrupts the user.
Click to expand...

Well, that makes total sense, thank you for explaining it that way. I
take it that making a MDE file takes FOREVER!? I have had this machine
here making a MDE/MDA file for crap - probably about 10 or 15 minutes
now, and the only thing I have is an hour glass, and my hard drive
blinking in spurts... Is this another one of the reasons you don't
want to run a MDE file from the server, because it takes to fricking
long, or is my computer locked!?
 
Do you run Microsoft Word from a server installation?

Nope.

So, why would you run your database application?

Remember, the MDE is that *application*, not the database (the back
end is the database). Only the database should be shared and stored
on the server.

The disadvantage is that you'll be loading the front end across the
network, which is orders of magnitude slower than loading from the
workstation's local hard drive. It also exposes the front end to
corruption from dropped connections that would not be the case if
loaded from the local hard drive. Granted, as you correctly observe
in another message, a corrupt front end can just be replaced by a
fresh copy, but it's inconvenient and interrupts the user.
Click to expand...

So in this MDE file, what should be stored!? Only Tables & Forms, only
tables, only inquiries, what!?

I take it then, I should have nothing but the Tables in the -BE.mdb
file stored on the server, and copy everything else to the local MDE
file!? Is this correct!? All my forms, queries, modules, etc., will
lie on the local MDE file...
 
So in this MDE file, what should be stored!? Only Tables & Forms, only
tables, only inquiries, what!?

I take it then, I should have nothing but the Tables in the -BE.mdb
file stored on the server, and copy everything else to the local MDE
file!? Is this correct!? All my forms, queries, modules, etc., will
lie on the local MDE file...
Click to expand...

You need *three* files:

- A Backend .mdb file stored on the server, containing *ONLY* the tables.
- A Frontend .mdb file, kept in a safe place, thoroughly backed up; this will
contain all the Forms, Reports, Queries, macros, modules, etc. with links to
the backend. Any design changes to forms and the like will be made in this
database.

- The .mde frontend:
Once you have the frontend .mdb file working, compile it (open the VBA editor
and select Debug... Compile); compact it; and finally create a .mde file. This
is a pseudo-compiled version of the frontend (which will NOT let you modify
any code or forms or reports, hence the need to keep the .mdb); you would
distribute copies of it to each user.

John W. Vinson [MVP]
 
I've done it both manually and with the unreplication tools (both
the A97 wizard created by Microsoft, and the TSI UnReplicator), but
it's not something I do often, so I've never experienced the kind of
problem you mention. Can you go into more detail?
Click to expand...

Yes. Using Michka's utility, I got an error message that stated that the
database wasn't replicated. It was a neat msg box, that I could tell that
Michka wrote because of the humorous way it was written. It's been a while
but it was something like.

"This database isn't replicated. I could lie to you and tell you I was done,
but I won't"

I tried several times, each time with the same answer. The database was
replicated and it was easy to see, nor would it let me simply delete the
replicated fields. I still have it. It's a medical database, or I'd send it
to you, so you could see for yourself.
 
Yes. Using Michka's utility, I got an error message that stated
that the database wasn't replicated. It was a neat msg box, that I
could tell that Michka wrote because of the humorous way it was
written. It's been a while but it was something like.

"This database isn't replicated. I could lie to you and tell you I
was done, but I won't"

I tried several times, each time with the same answer. The
database was
replicated and it was easy to see, nor would it let me simply
delete the replicated fields. I still have it. It's a medical
database, or I'd send it to you, so you could see for yourself.
Click to expand...

That's very interesting. What version of Access was it created with?
Were you using the same version to unreplicate it? Was it perhaps
corrupted in some way?
 
m:
I
take it that making a MDE file takes FOREVER!?
Click to expand...

No. If it is taking a long time, then you have some kind of problem.

Before creating an MDE, I always:

1. compact

2. decompile

3. compact again

4. compile the VBA project

5. compact again.

Only then do I attempt to create the MDE.
 
m:
So in this MDE file, what should be stored!? Only Tables & Forms,
only tables, only inquiries, what!?
Click to expand...

John has given you the answer, but I would suggest that you read up
on what an MDE actually is, and the answer should be clear to you.
 
So in this MDE file, what should be stored!? Only Tables & Forms, only
tables, only inquiries, what!?

Only the back-end tables should be stored. Making an MDE on the back-end is
useless, since it doesn't protect data. An MDE only compiles the VBA code.

I take it then, I should have nothing but the Tables in the -BE.mdb
file stored on the server, and copy everything else to the local MDE
file!? Is this correct!? All my forms, queries, modules, etc., will
lie on the local MDE file...

Yes, exactly correct.
 
Back
Top