01-11-08, 13:10 #1Registered User
- Join Date
- Aug 2005
Unanswered: Removal of Replication from Access 2003
I made the mistake of using a briefcase on a folder that had about 20 Access files on it. The briefcase created replication on all of my databases. I am trying to reverse the process and remove replication on the Access 2003 database.
I found this on MS site, but the wizard they refer to is only for Access 97 and I am using 2003. If you don't use their wizard it is a very messy task. Here is what they say:
If you are just starting out and haven't made many changes to your data and objects, chances are that you created a backup copy of your original database. Search for it in the folder where the replicated database is – it will have the same base file name as the replicated database with a "BAK" extension.
If you've gone beyond that point, you can download the Replica To Regular wizard from the Microsoft Web site, which will take care of the drudgery for you.
If you do it manually, you need to follow these steps:
1. Create a new database and import all the objects from the replicated database, except tables.
2. Close the new database and open the replicated database. Create a new query and select the first table in the Show Table dialog box. Add all the fields except for the replication fields (s_Generation, s_Guid, s_Lineage), unless they are used in your application. If so, then add them.
3. Select the Make Table query option and use the current database name and the database name of the database you just created in step 1. Run the query. Repeat this process for every table in the database.
4. You will then need to recreate all indexes, properties, and relationships that existed in the original replicated database.
5. When you're done, make sure to compact and repair the new database.
If you would like to avoid the overhead of having to re-create all of the properties and indexes on your tables, and you don’t mind having the s_Guid field but would like the other fields to be removed, you can do the following:
1. Either delete all of the relationships in your database or make them unenforced. You must do this step because the Jet database engine will not allow you to have a relationship that enforces referential integrity between a replicated table and a local one.
2. Right-click each replicated table, choose “Properties” and uncheck the “replicated” check box.
3. Import the tables from the replicated database as you did the other objects.
4. Recreate the relationships in your database (or change their type back to enforce referential integrity). All of the properties (such as InputMask, Format, Caption, and so on.) and all of the indexes will still be there.
01-11-08, 23:04 #2L33t Helpa Munky
- Join Date
- Nov 2007
- Adelaide, South Australia
I never install Briefcase and I've never touched replication either, so sorry, but I can't help
However, this should emphasise why it's important to maintain regular and reliable backups. Then you'd have a nice easy fixOwner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!
"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
01-12-08, 01:27 #3Moderator
- Join Date
- Dec 2004
- Madison, WI
Replication - yuch. Anyone who has a yearning for severe torture and pain (and record identifiers such as -342,332,112) uses replication and I don't blame you for wanting to make it non-replication. I've had to fix many mdb's which used replication and couldn't get their totals to work on relational tables. To add the synch process into the works just makes it a poor type of method in my opinion. Generating a random number between -999,999,999 and 999,999,999 to try and not duplicate another identifier number just didn't seem efficient and relational friendly to me (although maybe Microsoft changed replication in 2003+ versions.) If you accidently instigated replication on an mdb, you may want to check on how it now handles new autonumbers (does it assign a random large number?)
I might recommend using something like Citrix for external users. You should be able to just import the tables into a fresh mdb (without replication) and then recreate the new tables as Microsoft suggests by running make table queries. I'd recommend creating new tables (which I think you need to do anyway.) Make sure to check your autonumbers in the replication tables. If replication has accidently been applied, check on your relational tables linking by the record identifier - you may have fun replacing identifiers like -233,234,002 back to a more static record identifier/autonumber.
You can import the tables into a new mdb and then do make table queries (or append queries) to get the data into the new tables or you can just export the data out of the replication mdb into csv files and then import into the new mdb. You will need to recreate relationships, indexes, etc (check default values and lookup tables if you have them on any field.) I think I did it both ways.
I do recall having to run some (making sure the linked fields are correct) type queries to re-write autonumbers in the main tables to the relational tables. What I did was create a double field for the replication autonumber field and added a new autonumber field in the main data table and other tables, and used the large old autonumber field to link to the relational tables, writing in the new autonumber field from the main data table. Hopefully you don't have any large autonumbers. If you do, you'll need to create a new autonumber for them. Double-check your relational tables to make sure the correct autonumber has been populated.
We had some external users where it took 20+ minutes to synch the tables every time the got into the mdb using replication (those users utilized a slow modem to connect.) It just wasn't worth the hassle and having them connect via Citrix (combined with unbound forms in the mdb) was a LOT quicker, more friendly and easier to work with.
And make sure to make backups at every step. It's not to difficult to mess it up and get wrong autonumbers populated in the relational tables, especially multi-linked sub tables (which will give you nightmares for a few nights if you populate the wrong autonumbers.) I kept the old large autonumbers in the data tables for a while just to make sure I could double-check the data if the users questioned any of the totals. I'm not sure how many relational tables you're dealing with (hopefully not a lot of them.) For me, I had up to 30 or so relational tables to deal with in each replicated mdb.
Last edited by pkstormy; 01-12-08 at 05:42.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)