Re: Altering Identity Columns for Bidirectional Transasctional Replication

Here is how I ended up doing:

- Backup the original Database containing all the data:
- Create a new database - name it backup_db
- Destroy the orginal database and recreate the database only.
- Take the scripts that created the original database (if none is
available then export the scripts from the backup_db)
- Edit the script and alter all the IDENTITY(1,1) to the desired
seed and step.
- In the original db (now empty) create only the tables - don't
worry about the views, procedures and any other objects.
- Create a snapshot replication using the backup_db as the
publishing database: ensure that the published articles are marked to
only "Delete all the data in the existing table" - this will ensure
that the subscriber's tables are not destroyed (hence keeping your new
identity definition"
- Subscribe the original db to the publication and sync it.
- Once the replication has completed we now need to update all the
tables with an identity to reseed with the latest highest value - you
can do this by defining the following script (in my case, I need the
seed to be even):

BEGIN
DECLARE @new_ident as int
SET @new_ident=(SELECT IDENT_CURRENT('your_table')) + 1
IF (@new_ident % 2) <> 0
SET @new_ident = @new_ident + 1
DBCC CHECKIDENT('ACCOUNT', RESEED, @new_ident)
END
.

Relevant Pages

  • Re: The amazing disappearing VBA code
    ... Windows Explorer, and make a copy of your database, as Access is about to ... the original database wasn't deleted because it is read-only. ... might be an issue with mapped drives like when were the last updates from MS ... resource management in the OS or any other application is hoarding RAM. ...
    (microsoft.public.access.modulesdaovba)
  • Re: copying ? Record #isnt working
    ... If you have copied to old database there is no need to put the ... If you wish to keep the old database simply rename it "MyDbOLD.mdb". ... Then re-name the new updated database to the original database name ... ' Rename original Db to a new name in the same folder ...
    (microsoft.public.access.gettingstarted)
  • Re: Run-time error 2465 after database import
    ... The original database is in Access 2000 and the ... and importing everything from the original database. ... Visual Basic Editor - Tools / database Properties ... If I go into debug at this point and try using PaddedLotNumber ...
    (microsoft.public.access.formscoding)
  • Re: autonummering in table gives problems
    ... manually recreate the "bad" records in the new table, ... sequence of AutoNumber identifications may be regenerated (but don't count ... I made the new database with all the ... > the original database). ...
    (comp.databases.ms-access)
  • [NEWS] Granite Software ZMerge Administration Database Insecure Default ACLs
    ... the ZMerge administration database grants ... Note that while anonymous web users can read and modify all scripts, ... The ZMerge administration database contains the data import/export scripts ... server, allowing scripts to read and write arbitrary files on the server. ...
    (Securiteam)
Loading