Replication for Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scook11@sbcglobal.net - 17 Jun 2005 01:12 GMT

I am trying to determine if it is a good idea to convert an Access 2000
database to Access 2003. The main reason we use Access is for the
Replication Manager (indirect synchronization). Is it a good idea to
go to Access 2003?

Reply to this Message

David W. Fenton - 17 Jun 2005 18:13 GMT

> I am trying to determine if it is a good idea to convert an Access
> 2000 database to Access 2003. The main reason we use Access is
> for the Replication Manager (indirect synchronization). Is it a
> good idea to go to Access 2003?

There is absolute no reason to convert the MDB file to Access 2003.

First, if you're replicated, you're surely replicating only the data
(if you're not, then you're bound for huge problems, since
replication of forms/reports, etc. almost always leads to corruption
and completely loss of the project), so the only question you need
to answer is: are there any changes to the data-only parts of the
Access 2K3 format that justify a conversion? I don't know of any
such changes.

Second, the A2K file format is native (and the default format) for
all versions of Access since A2K. This means that it doesn't matter
what post-2K version of Finale the users have -- the same A2K MDB
will work just fine for all of them.

Third, there is no replication manager for A2K3, since the one that
came out with the Office XP developer tools (i.e., A2K2) will work
with A2K3, because Jet 4.0 is still the version of Jet in Access
(replication is a Jet technology, not an Access technology). But, of
course, the A2K2 ReplMan (and the A2K) will work with A2K3.

There is no reason that I can see to convert the files to A2K3.

There is no problem with using the unconverted files with A2K3.

From my point of view, there is seldom any reason to upgrade an
Access application version, except in extreme cases (like an Access
2 application, which could definitely benefit in many ways from
being brought into the modern age of 32-bit Access). Most of my
clients continue to use Access 97 because there is no value added
for them by upgrading to a newer version of Access. When they get a
new computer, they buy it with Office Pro, and when it arrives I
uninstall the copy of Access and install A97. This is technically a
violation of the EULA, but nobody is losing any money, so it seems
completely ethical to me (I won't install A97 on a machine that
doesn't already have a legal license to use Access).

A2K is a nightmare for a developer and I've been told that A2K3 is
vastly improved for the developer. But that doesn't change anything
about the file format. And I can't confirm it because I don't have
A2K3 (though I've got clients using it), and don't really do a lot
of my development in A2K.

Signature

David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Reply to this Message

Frank Mansfield - 29 Jun 2005 00:39 GMT

I have replicated a 2003 Access database, but I have included forms, queries
and tables. If the forms and queries are not replicated how will the
replicated database have access to the forms and queries?

> > I am trying to determine if it is a good idea to convert an Access
> > 2000 database to Access 2003. The main reason we use Access is
[quoted text clipped - 43 lines]
> A2K3 (though I've got clients using it), and don't really do a lot
> of my development in A2K.

Reply to this Message

David W. Fenton - 30 Jun 2005 01:55 GMT

=?Utf-8?B?RnJhbmsgTWFuc2ZpZWxk?=
<FrankMansfield@discussions.microsoft.com> wrote in
news:A24479E6-793A-42DF-BBBE-007FF4892E6B@microsoft.com:

> I have replicated a 2003 Access database, but I have included
> forms, queries and tables. If the forms and queries are not
> replicated how will the replicated database have access to the
> forms and queries?

You send them a separate, non-replicated MDB file with the
forms/reports/queries.

How do you push out changes to those?

You send out a new one.

See http://www.granite.ab.ca/access/splitapp/index.htm for an
explanation of all the considerations in splitting an application.

It's standard practice to split -- I do it even for apps used by a
single user. The original justification was for multi-user apps,
where you put the data MDB on the server, and a copy of the front
end on each workstation.

If you're unsplit (absent a mechanism like replication, or a lot of
code that copies in new objects) how do you make changes to the
front end without overwriting the user's data?

Replicating front ends looks like a great idea, a solution for this,
but if you understand the way replication works (and especially the
changes to the way the Access project is stored that came with A2K),
you realize that it's a *very bad* idea -- it amounts to splitting
your app and having multiple people open the front end at the same
time (which is also a bad idea of another kind).

From a design point of view, forms and reports are read-only for
everybody but the developer of the application. From a user's point
of view, the form/report definitations are read-only. But in
reality, behind the scenes, some properties are actually saved in
the background (such as filters/sorts), and those end up getting
exchanged during synchs of replicated forms/reports. The result is
dueling form properties, and a lot of unnecessary synchronization of
information that has no value anywhere except for the individual
user.

The bottom line: don't replicate what is not shared.

And forms/reports are not shared in the sense that the data tables
are.

Signature

David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Reply to this Message

Frank Mansfield - 06 Jul 2005 02:23 GMT

I imported the tables, forms, queries, reports, and modules into and new
database and regenerated the tables and links to create a new unreplicated
database. Then I split the database as suggested.

When I try to replicate the data file it keeps saying the file is all ready
in exclusive use and then stops. If I am doing this wrong please explain the
right way.

> =?Utf-8?B?RnJhbmsgTWFuc2ZpZWxk?=
> <FrankMansfield@discussions.microsoft.com> wrote in
[quoted text clipped - 45 lines]
> And forms/reports are not shared in the sense that the data tables
> are.

Reply to this Message

David W. Fenton - 07 Jul 2005 01:21 GMT

=?Utf-8?B?RnJhbmsgTWFuc2ZpZWxk?=
<FrankMansfield@discussions.microsoft.com> wrote in
news:F5DE8D0B-375D-4DBD-8D12-26DC63D9962F@microsoft.com:

> I imported the tables, forms, queries, reports, and modules into
> and new database and regenerated the tables and links to create a
[quoted text clipped - 3 lines]
> all ready in exclusive use and then stops. If I am doing this
> wrong please explain the right way.

Are you trying to replicate to the same file name as the
non-replicated version?

I haven't created a replicated database in a long time so I've
forgotten the UI, but I just tried it and had no problems. You
create a replica in a different location (it should be a real
location, not something you intend to throw away), and the original
data file you started out with becomes the design master.

Because of this, I'd start out by moving the pre-replicated MDB to
the permanent location for your design master, then creating the
replica with the same name in the location where the pre-replicated
MDB was before.

Say for instance that your current data file is data.mdb in
\\Server\Database. I'd created a subfolder of \\Server\Database
called DesignMaster, then move data.mdb there. Then I'd open it and
create a replica of it called data.mdb in \\Server\Database, which
has the same name as the original data file, so it can be found by
the front end files.

Start with \\Server\Database\data.mdb

Move it to \\Server\Database\DesignMaster\data.mdb

Open it and create a replica called \\Server\Database\data.mdb

That should do it.

If you are still getting the error, exit Access, open Windows
Explorer and see if there is an LDB file still there. If there is,
try to delete it. If you can't, that's why you're getting the error
-- either somebody is actually using the database and has it open,
or Windows is maintaining an open file lock on it, in which case you
need to tell Windows to close it. If the file is on your local
workstation, reboot. If it's on a server, open up Control Panel |
Administrative Tools | Computer Management on the server console,
and go to Shared Folders. In the share that the data file lives in
you should see who has the file open. You can then right click on it
and force close it.

But it's probably a corrupted LDB file that needs to be deleted.

One possible source of that would be a custom MDW file with the same
name as your data file that is stored in the same directory as the
data file. But that's a pretty unusual scenario -- I mention it only
because that scenario came up recently in comp.databases.ms-access.

Signature

David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Reply to this Message