Access 2003 - Remove Replication

[JPL]

I have been asked to remove the replication from an Access 2003 db that no
longer uses replication, but reports replication errors on being opened.

There were wizards to do this for Access 95 & 97, but I cannot find one for
Access 2003. There are concerns about the possibility of errors and the time
that will be taken (i.e the time during which the db will not be available)
if the conversion is performed manually.

Does anyone know of any available tools for this process, or have any best
practice advice? All I have found is
http://office.microsoft.com/en-gb/access/HP052167901033.aspx, which describes
a somewhat long winded manual process.

JPL

[David W. Fenton]

=?Utf-8?B?SlBM?= <J...@discussions.microsoft.com> wrote in
news:039182D3-4ECE-44BF...@microsoft.com:

See question 10 of:

http://dfenton.com/DFA/Replication/index.php?title=FAQ

Either of the two Jet 4 options listed last should make the process
a lot easier than the manual process.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[JPL]

David,

Thanks for your help.

I have tried Graham Search's Unreplicate.mdb via your FAQ page, but on
attempting to open it with Access 2003 SP3 on XP SP2 it produces the error
message "There isn't enough memory to perform this operation. Close unneeded
programs and try the operation again." The form cannot be opened, either to
use or in design mode.

JPL

[David W. Fenton]

=?Utf-8?B?SlBM?= <J...@discussions.microsoft.com> wrote in
news:72C0BB3D-1ACC-4BF6...@microsoft.com:

> I have tried Graham Search's Unreplicate.mdb via your FAQ page,
> but on attempting to open it with Access 2003 SP3 on XP SP2 it
> produces the error message "There isn't enough memory to perform
> this operation. Close unneeded programs and try the operation
> again." The form cannot be opened, either to use or in design
> mode.

Ah -- someone replicated forms. That's possibly an unrecoverable
error.

Jet Replication works reliably only for pure Jet objects (tables and
queries). Despite Microsoft's advice, it is inadvisable to ever
replicate Access objects (forms, reports, macros, modules), because
those do not replicate well, particularly since the switch with A2K
to the monolithic save model, where the entire VBA project is stored
in a single record, instead of one record per object.

Unreplicating forms and reports is easy -- just import them into an
unreplicated MDB.

In any event, you should also have a split setup, with tables in one
MDB and the front-end objects (queries, forms/reports/etc.) in the
front end, with linked tables from front end to the back end.

Last of all, the replicated project may be corrupt, Possibly you
could recover them with the undocumented Application.SaveAsText and
Application.LoadFromText commands.

[JPL]

Apologies for the confusing explanation.

The db that had the error on opening was the Unreplicate.mdb utility
recommended on your FAQ page, (not the db I have been asked to unreplicate).
Similarly, there is an error when I try to LoadFromText the other option,
frmUnReplicate_2002.txt:
"Compile error:
Expected: line number or label or statement or end of statement"

I entirely agree about split dbs and have always done this since I first
learned about it in Access 97 days.

JPL

[David W. Fenton]

=?Utf-8?B?SlBM?= <J...@discussions.microsoft.com> wrote in
news:FC29D1DD-697A-4FC3...@microsoft.com:

> The db that had the error on opening was the Unreplicate.mdb
> utility recommended on your FAQ page, (not the db I have been
> asked to unreplicate). Similarly, there is an error when I try to
> LoadFromText the other option, frmUnReplicate_2002.txt:
> "Compile error:
> Expected: line number or label or statement or end of statement"

Well, you're right. I can't get that utility to work -- I'd never
tried, I'd just seen others recommend it. But the TSI untility works
just fine and dandy. It's a DLL that you register and that adds an
unreplicate option to the Replication menu in all versions of Access
that support Jet 4. It's a much better designed utility, in my
opinion.

[JPL]

I reported the problems with UnReplicate.mdb and its LoadFromText version to
the author. The suggestions in his reply changed the error, but still no
success. I have reported back to him and shall report back here on
concluding the matter.

Meanwhile I plan to try the TSI utility when I get my hands on the
replicated db this Thursday.

JPL

[JPL]

I used the TSI utility at the client (a locally based charity) today, and
successfully unreplicated an Access 2002 - 2003 file format db. It just
worked, simply and quickly.

I should not have been put off by the "2000" in the product description
("Removes replication from an Access 2000 database").

Many thanks for your help, David.

JPL

[David W. Fenton]

=?Utf-8?B?SlBM?= <J...@discussions.microsoft.com> wrote in
news:FECA494E-90A8-4DFD...@microsoft.com:

> I used the TSI utility at the client (a locally based charity)
> today, and successfully unreplicated an Access 2002 - 2003 file
> format db. It just worked, simply and quickly.
>
> I should not have been put off by the "2000" in the product
> description ("Removes replication from an Access 2000 database").

Replication is a Jet technology, so the version of Access is
irrelevant. What matters is the version of JET, and all versions of
Access from A2K through A2K3 user Jet 4 as the file format. Access
2007 has a new format that doesn't support replication, and uses Jet
4 as a native format. Any A2K7 MDB would also be a Jet 4 MDB and you
could use the TSI Unreplicator on it.