Access source Codes for GMS Setup

From ICISWiki

Contents 1 Check Local Database integrity 2 Prepare local update environment 3 Update Locations 4 Extract all local germplasm without local references 5 Update all remaining local germplasm references 6 Check update 7 Execute central changes and restore the local database

Check Local Database integrity

Check GNPGS for derivatives

SELECT GERMPLSM.GID, GERMPLSM.METHN, METHODS1.MTYPE AS Expr1, GERMPLSM.GNPGS, GERMPLSM.GRPLCE
FROM GERMPLSM, METHODS1
WHERE (((GERMPLSM.GNPGS)<>-1) AND (([METHODS1].[MTYPE])="DER"));

Check GNPGS for generatives

SELECT GERMPLSM.GID, GERMPLSM.METHN, METHODS1.MTYPE AS Expr1, GERMPLSM.GNPGS, GERMPLSM.GRPLCE
FROM GERMPLSM, METHODS1
WHERE (((GERMPLSM.GNPGS)<0) AND (([METHODS1].[MTYPE])="GEN"));

Check GPID1 <> GID for any record

SELECT GERMPLSM.GID
FROM GERMPLSM
WHERE (((GERMPLSM.GID)=[GERMPLSM].[GPID1]));

Check GPID2 <> GID for any record

SELECT GERMPLSM.GID
FROM GERMPLSM
WHERE (((GERMPLSM.GID)=[GERMPLSM].[GPID2]));

Check that all derivatives with known source (GPID2 <> 0) have known group (GPID1 <>0)

SELECT GERMPLSM.GID, GERMPLSM.GNPGS, GERMPLSM.GPID1, GERMPLSM.GPID2
FROM GERMPLSM
WHERE (((GERMPLSM.GNPGS)=-1) AND ((GERMPLSM.GPID1)=0) AND ((GERMPLSM.GPID2)<>0));

SELECT GERMPLSM.GID, GERMPLSM.GPID1, GERMPLSM.GPID2
FROM GERMPLSM
WHERE (((GERMPLSM.GNPGS)=-1) AND ((GERMPLSM.GRPLCE)=0));

SELECT CHKL01F1.GID, GERMPLSM.GID, GERMPLSM.GPID1, GERMPLSM.GPID2
FROM CHKL01F1 
INNER JOIN GERMPLSM ON CHKL01F1.GPID2 = GERMPLSM.GID
WHERE (((CHKL01F1.GID)=[GERMPLSM].[GID]));

SELECT CHKL01F1.GID, GERMPLSM.GID, GERMPLSM.GPID1, GERMPLSM.GPID2
FROM CHKL01F1 
INNER JOIN GERMPLSM ON CHKL01F1.GPID2 = GERMPLSM.GID;

SELECT CHKL01F3.CHKL01F1.GID, GERMPLSM.GID, GERMPLSM.GPID1, GERMPLSM.GPID2
FROM CHKL01F3 
INNER JOIN GERMPLSM ON CHKL01F3.GPID2 = GERMPLSM.GID
WHERE (((CHKL01F3.CHKL01F1.GID)=[GERMPLSM].[GID]));

Check no replaced central GID should be referenced in the local GMS

SELECT GERMPLSM.GID, GERMPLSM1.GID, GERMPLSM1.GRPLCE
FROM GERMPLSM 
INNER JOIN GERMPLSM1 ON GERMPLSM.GPID1 = GERMPLSM1.GID
WHERE (((GERMPLSM1.GRPLCE)<>0));

Check no replaced central GID should be referenced in the local GMS

SELECT GERMPLSM.GID, GERMPLSM1.GID, GERMPLSM1.GRPLCE
FROM GERMPLSM 
INNER JOIN GERMPLSM1 ON GERMPLSM.GPID2 = GERMPLSM1.GID
WHERE (((GERMPLSM1.GRPLCE)<>0));

Check that no replacements in the CHANGES table have already been replaced or deleted in the central database

SELECT CHANGES.CTABLE, CHANGES.CFIELD, GERMPLSM1.GRPLCE, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES 
INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GID") 
AND ((GERMPLSM1.GRPLCE)<>0) AND ((CHANGES.CSTATUS)=0));

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES 
INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLASM") AND ((CHANGES.CFIELD)="GID") 
AND ((CHANGES.CTO)<>[GERMPLSM].[GRPLCE]) AND ((CHANGES.CSTATUS)=0));

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES 
INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GPID1") 
AND ((CHANGES.CTO)<>[GERMPLSM].[GPID1]) AND ((CHANGES.CSTATUS)=0));

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES 
INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GPID2") 
AND ((CHANGES.CTO)<>[GERMPLSM].[GPID2]) AND ((CHANGES.CSTATUS)=0));

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES 
INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GDATE") 
AND ((CHANGES.CTO)<>[GERMPLSM].[GDATE]) AND ((CHANGES.CSTATUS)=0));

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES 
INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GLOCN") 
AND ((CHANGES.CTO)<>[GERMPLSM].[GLOCN]) AND ((CHANGES.CSTATUS)=0));

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES 
INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="METHN") 
AND ((CHANGES.CTO)<>[GERMPLSM].[METHN]) AND ((CHANGES.CSTATUS)=0));

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES 
INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GNPGS") 
AND ((CHANGES.CTO)<>[GERMPLSM].[GNPGS]) AND ((CHANGES.CSTATUS)=0));

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES 
INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GREF") 
AND ((CHANGES.CTO)<>[GERMPLSM].[GREF]) AND ((CHANGES.CSTATUS)=0));

SELECT GERMPLSM.GID, GERMPLSM.GPID1 AS GPID1, GERMPLSM.MGID, 
GERMPLSM1.GPID1 AS MGPID1, GERMPLSM1.GRPLCE AS Expr1 
FROM GERMPLSM, GERMPLSM1 
WHERE (((GERMPLSM.GPID1)<>[GERMPLSM1].[GPID1]) AND ((GERMPLSM.GRPLCE)=0) 
AND (([GERMPLSM1].[GPID1])<>0) AND (([GERMPLSM1].[GNPGS])=-1));

SELECT GERMPLSM.GID, GERMPLSM.GPID1 AS GPID1, GERMPLSM.MGID, 
GERMPLSM1.GPID1 AS MGPID1, GERMPLSM1.GRPLCE AS Expr1 
FROM GERMPLSM, GERMPLSM1 
WHERE (((GERMPLSM.GPID1)<>[GERMPLSM1].[GID]) AND ((GERMPLSM.GRPLCE)=0) 
AND (([GERMPLSM1].[GPID1])=0) AND (([GERMPLSM1].[GNPGS])=-1)); 

SELECT GERMPLSM.GID, GERMPLSM.GPID1 AS GPID1, GERMPLSM.MGID, 
GERMPLSM1.GNPGS AS Expr1, GERMPLSM1.GRPLCE AS Expr2 
FROM GERMPLSM, GERMPLSM1 
WHERE (((GERMPLSM.GPID1)<>[GERMPLSM1].[GID]) AND ((GERMPLSM.GRPLCE)=0) 
AND (([GERMPLSM1].[GNPGS])>0));

Prepare local update environment

Set GERMPLSM.LGID = GERMPLSM.GID

UPDATE GERMPLSM SET GERMPLSM.LGID = [germplsm].[gid];

Create Table MAXTABLE

CREATE TABLE MAXTABLE (MAXID LONG,GID LONG, NID LONG,  
LOCID LONG, AID LONG, CID LONG, LDID LONG, LISTID LONG, 
INSTALID LONG, UID LONG, ADMIN LONG, REFID LONG, 
LRECID LONG, FLDNO LONG);

INSERT INTO MAXTABLE VALUES (1,0,0,0,0,0,0,0,0,0,0,0,0,0);

Set Max GID

UPDATE MAXTABLE 
SET MAXTABLE.GID = (Select MAX(GERMPLSM1.GID) FROM GERMPLSM1) 
WHERE MAXID=1;

Set Max NID

UPDATE MAXTABLE 
SET MAXTABLE.NID = (Select MAX(NAMES1.NID) FROM NAMES1) 
WHERE MAXID=1;

Set Max LOCID

UPDATE MAXTABLE 
SET MAXTABLE.LOCID = (Select MAX(LOCATION1.LOCID) FROM LOCATION1) 
WHERE MAXID=1;

Set Max AID

UPDATE MAXTABLE 
SET MAXTABLE.AID = (Select MAX(ATRIBUTS1.AID) FROM ATRIBUTS1) 
WHERE MAXID=1;

Set Max CID

UPDATE MAXTABLE 
SET MAXTABLE.CID = (Select MAX(CHANGES1.CID) FROM CHANGES1) 
WHERE MAXID=1;

Set Max LDID

UPDATE MAXTABLE 
SET MAXTABLE.LDID = (Select MAX(LOCDES1.LDID) FROM LOCDES1) 
WHERE MAXID=1;

Set Max LISTID

UPDATE MAXTABLE 
SET MAXTABLE. LISTID = (SELECT MAX(LISTNMS1.LISTID) AS MAXLISTID FROM LISTNMS1) 
WHERE MAXID=1;

Set Max INSTALID

UPDATE MAXTABLE 
SET MAXTABLE. instalid= (SELECT MAX(instln1.instalid) AS MAXinstalid FROM instln1) 
WHERE MAXID=1;

Set Max UID

UPDATE MAXTABLE 
SET MAXTABLE. UID = (SELECT MAX(users1.userid) AS MAXuserid FROM users1) 
WHERE MAXID=1;

Set Max ADMIN

UPDATE MAXTABLE 
SET MAXTABLE. admin= (SELECT MAX(instln1.admin) AS MAXadmin FROM instln1) 
WHERE MAXID=1;

Set Max REFERENCE ID

UPDATE MAXTABLE 
SET MAXTABLE. refid= (SELECT MAX(bibrefs1.refid) AS MAXrefid FROM bibrefs1) 
WHERE MAXID=1;

Set Max LRECID

UPDATE MAXTABLE 
SET MAXTABLE. lrecid=(SELECT MAX(listdata1.lrecid) AS MAXlrecid FROM listdata1) 
WHERE MAXID=1;

Set Max FLDNO

UPDATE MAXTABLE 
SET MAXTABLE.FLDNO=(SELECT MAX(udflds1.fldno) AS MAXfldno FROM udflds1) 
WHERE MAXID=1;

Create New Table UPDATEGID

Create Table [UPDATEGID] (GID Long, LGID Long)

Populate Table UPDATEGID

INSERT INTO [UPDATEGID] ( GID, LGID )
SELECT MaxTable.GID, MaxTable.GID
FROM MaxTable

Create New Table UpdateLOC

CREATE TABLE UpdateLOC (LOCID LONG, LLOCID LONG)

Populate Table UpdateLOC

INSERT INTO UpdateLOC ( LOCID, LLOCID )
SELECT MaxTable.LOCID, MaxTable.LOCID
FROM MaxTable

Create New Table UpdCHNG

Create Table [UPDCHNG] (CID Counter, LCID Long);

Populate Table UpdCHNG

INSERT INTO UPDCHNG (CID, LCID)
SELECT MAXTABLE.CID, MAXTABLE.CID
FROM MAXTABLE;

Create New Table UpdNAME

Create Table [UPDNAME] (NID Long, LNID Long);

Populate Table UpdNAME

INSERT INTO UPDNAME (NID, LNID)
SELECT MAXTABLE.NID, MAXTABLE.NID
FROM MAXTABLE;

Create New Table UpdATRIBUTS

Create Table [UPDATRIBUTS]  (AID Long, LAID Long);

Populate Table UpdATRIBUTS

INSERT INTO UPDATRIBUTS (AID, LAID)
SELECT MAXTABLE.AID, MAXTABLE.AID
FROM MAXTABLE;

Create New Table UPDLISTNMS

Create Table [UPDLISTNMS] (LISTID Counter primary key, LLISTID Long);

Populate Table UPDLISTNMS

INSERT INTO UPDLISTNMS ( LISTID, LLISTID ) 
SELECT MAXTABLE.LISTID, MAXTABLE.LISTID FROM MAXTABLE; 

Create New Table UPDinstln

Create Table [UPDinstln] (instalid Counter primary key, Linstalid Long); 

Populate Table UPDinstln

INSERT INTO UPDinstln ( instalid, Linstalid ) 
SELECT MAXTABLE.instalid, MAXTABLE.instalid FROM MAXTABLE;

Create New Table UPDusers

Create Table [UPDusers] (userid Counter primary key, Luserid Long); 

Populate Table UPDusers

INSERT INTO UPDusers ( userid, Luserid ) 
SELECT MAXTABLE.uid, MAXTABLE.uid FROM MAXTABLE; 

Create New Table UPDadmin

Create Table [UPDadmin] (admin Counter primary key, Ladmin Long);

Populate Table UPDadmin

INSERT INTO UPDadmin (admin, Ladmin) 
SELECT MAXTABLE.admin, MAXTABLE.admin FROM MAXTABLE; 

Create New Table UPDbibref

Create Table [UPDbibref] (refid Counter primary key, Lrefid Long); 

Populate Table UPDbibref

INSERT INTO UPDbibref ( refid, Lrefid ) 
SELECT MAXTABLE.refid, MAXTABLE.refid FROM MAXTABLE; 

Create New Table UPDlistdata

Create Table [UPDlistdata] (lrecid Counter primary key, Llrecid Long);

Populate Table UPDlistdata

INSERT INTO UPDlistdata ( lrecid, Llrecid ) 
SELECT MAXTABLE.lrecid, MAXTABLE.lrecid FROM MAXTABLE; 

Create New Table UPDudflds

Create Table [UPDudflds] (fldno Counter primary key, Lfldno Long);

Populate Table UPDudflds

INSERT INTO UPDudflds ( fldno, Lfldno ) 
SELECT MAXTABLE.fldno, MAXTABLE.fldno FROM MAXTABLE;

Update Locations

Get location IDs from local to central

INSERT INTO UpdateLOC ( LLOCID )
SELECT LOCATION.LOCID AS Expr1
FROM LOCATION;

Transfer new positive LOCID back to LOCATION table

UPDATE LOCATION, UpdateLOC SET LOCATION.LOCID = [UpdateLOC].[LOCID]
WHERE (((LOCATION.LOCID)=[UpdateLOC].[LLOCID]));

Transfer new positive LOCID back to LOCDES table

UPDATE UpdateLOC, LOCDES SET LOCDES.LOCID = [UpdateLOC].[LOCID]
WHERE (((LOCDES.LOCID)=[UpdateLOC].[LLOCID]));

Transfer new positive LOCID back to GERMPLSM table

UPDATE UpdateLOC, GERMPLSM SET GERMPLSM.GLOCN = [UpdateLOC].[LOCID]
WHERE (((GERMPLSM.GLOCN)=[UpdateLOC].[LLOCID]));

Transfer new positive LOCID back to NAMES table

UPDATE UpdateLOC, [NAMES] SET [NAMES].NLOCN = [UpdateLOC].[LOCID]
WHERE (((NAMES.NLOCN)=[UpdateLOC].[LLOCID]));

Transfer new positive LOCID back to ATRIBUTS table

UPDATE UpdateLOC, ATRIBUTS SET ATRIBUTS.ALOCN = [UpdateLOC].[LOCID]
WHERE (((ATRIBUTS.ALOCN)=[UpdateLOC].[LLOCID]));

Transfer new positive LOCID back to LOCATION table for field SNL3ID

UPDATE LOCATION, UpdateLOC SET LOCATION.SNL3ID = [updateloc].[locid]
WHERE (((LOCATION.SNL3ID)=[updateloc].[llocid]));

Transfer new positive LOCID back to LOCATION table for field SNL2ID

UPDATE LOCATION, UpdateLOC SET LOCATION.SNL2ID = [updateloc].[locid]
WHERE (((LOCATION.SNL2ID)=[updateloc].[llocid]));

Transfer new positive LOCID back to LOCATION table for field SNL1ID

UPDATE LOCATION, UpdateLOC SET LOCATION.SNL1ID = [updateloc].[locid]
WHERE (((LOCATION.SNL1ID)=[updateloc].[llocid]));

INSERT INTO UPDLISTNMS ( LLISTID ) 
SELECT LISTNMS.LISTID FROM LISTNMS 
WHERE LISTNMS.LISTID < 0;

UPDATE LISTNMS, UPDLISTNMS 
SET LISTNMS.LISTID = UPDLISTNMS.LISTID 
WHERE LISTNMS.LISTID = UPDLISTNMS.LLISTID;

UPDATE LISTDATA, UPDLISTNMS 
SET LISTDATA.LISTID = UPDLISTNMS.LISTID 
WHERE LISTDATA.LISTID = UPDLISTNMS.LLISTID;

INSERT INTO UPDUSERS (LUSERID) 
SELECT USERS.USERID FROM USERS WHERE USERS.USERID < 0;

UPDATE USERS, UPDUSERS 
SET USERS.USERID = UPDUSERS.USERID 
WHERE USERS.USERID = UPDUSERS.LUSERID; 

UPDATE LISTNMS, UPDUSERS 
SET LISTNMS.LISTUID = UPDUSERS.USERID 
WHERE LISTNMS.LISTUID = UPDUSERS.LUSERID;

Extract all local germplasm without local references

Generate positive GIDs for GERMPLSM table into table UPDATEGID

INSERT INTO [UPDATEGID] (LGID)
SELECT GERMPLSM.GID
FROM GERMPLSM
WHERE (((GERMPLSM.GID)<0) AND ((GERMPLSM.GPID1)>=0) AND ((GERMPLSM.GPID2)>=0) 
AND ((GERMPLSM.GRPLCE)=0));

Put positive GIDs in GERMPLSM table according to UPDATEGID table

UPDATE GERMPLSM, [UPDATEGID] SET GERMPLSM.GID = [updategid].[gid]
WHERE (((GERMPLSM.GID)=[updategid].[lgid]));

Put positive GIDs in PROGNTRS table according to UPDATEGID table

UPDATE [UPDATEGID], PROGNTRS SET PROGNTRS.GID = [updategid].[gid]
WHERE (((PROGNTRS.GID)=[updategid].[lgid]));

Put positive GIDs in GERMPLSM table for GPID1s according to UPDATEGID table

UPDATE GERMPLSM, [UPDATEGID] SET GERMPLSM.GPID1 = [updategid].[gid]
WHERE (((GERMPLSM.GPID1)=[updategid].[lgid]));

Put positive GIDs in GERMPLSM table for GPID2s according to UPDATEGID table:

UPDATE GERMPLSM, [UPDATEGID] SET GERMPLSM.GPID2 = [updategid].[gid]
WHERE (((GERMPLSM.GPID2)=[updategid].[lgid]));

Note that first query of "Extract all local germplasm without local references" *must* be run again after running the second to last query

Put positive GIDs in GERMPLSM table for GPID2s according to UPDATEGID table

UPDATE [UPDATEGID], PROGNTRS 
SET PROGNTRS.PID = [updategid].[gid]
WHERE (((PROGNTRS.PID)=[updategid].[lgid]));

Update all remaining local germplasm references

Update GIDs in NAMES according to UPDATEGID table

UPDATE [NAMES], [UPDATEGID] 
SET [NAMES].GID = [updategid].[gid]
WHERE (((NAMES.GID)=[updategid].[lgid]));

Update GIDs in ATRIBUTS according to UPDATEGID table

UPDATE [UPDATEGID], ATRIBUTS 
SET ATRIBUTS.GID = [updategid].[gid]
WHERE (((ATRIBUTS.GID)=[updategid].[lgid]) AND ((ATRIBUTS.ATYPE)<>101 Or (ATRIBUTS.ATYPE)<>102));

Update GIDs in LISTDATA according to UPDATEGID table

UPDATE LISTDATA, [UPDATEGID] SET LISTDATA.GID = [updategid].[gid]
WHERE (((LISTDATA.GID)=[updategid].[lgid]));

INSERT INTO UPDNAMES (lnid) 
SELECT names.nid FROM [names] WHERE names.nid<0;

UPDATE [names], UPDNAMES 
SET [names].NID = [UPDNAMES].[nid] 
WHERE (((names.NID)=[UPDNAMES].[lnid]));

INSERT INTO UPDATRIB (laid) 
SELECT atributs.aid FROM atributs WHERE atributs.aid<0;

UPDATE atributs, UPDATRIB 
SET atributs.aid = UPDATRIB.aid WHERE atributs.aid = UPDATRIB.laid;

INSERT INTO UPDinstln (linstalid) 
SELECT instln.instalid FROM instln WHERE instln.instalid<0;

UPDATE instln, UPDinstln 
SET instln.instalid = UPDinstln.instalid WHERE instln.instalid = UPDinstln.linstalid;

UPDATE users, UPDinstln 
SET users.instalid = UPDinstln.instalid WHERE users.instalid = UPDinstln.linstalid;

INSERT INTO UPDadmin (ladmin) 
SELECT instln.admin FROM instln WHERE instln.admin<0;

UPDATE instln, UPDadmin 
SET instln.admin = UPDadmin.admin WHERE instln.admin = UPDadmin.ladmin;

Check update

Check if all these 3 requirements are fulfilled (all 3 must return 0 records). There should be no records in GERMPLSM with negative GIDs and GRPLCE = 0.

Select * From GERMPLSM Where GRPLCE=0 And GID < 0

The only negative GIDs in the NAMES and ATRIBUTS table should belong to replaced or deleted records.

Select * From NAMES a Where GID < 0 And NSTAT <> 9 And Not Exists
(Select * From GERMPLSM b Where a.GID = b.GID and b.GRPLCE<>0)

Select * From ATRIBUTS a Where a.GID < 0 And Not Exists
(Select * From GERMPLSM b Where a.GID = b.GID and b.GRPLCE<>0)

All GIDs and PIDs in PROGENITORS table should be positive.

Select * From PROGNTRS Where (GID < 0 Or PID < 0)

Execute central changes and restore the local database

Insert locations into Access after local field LOCID has been changed to central LOCID

INSERT INTO LOCATION1 (LOCID, LTYPE, NLLP, LNAME, LABBR, SNL3ID, SNL2ID, SNL1ID, CNTRYID, LRPLCE)
SELECT LOCATION.LOCID, LOCATION.LTYPE, LOCATION.NLLP, LOCATION.LNAME, LOCATION.LABBR, 
LOCATION.SNL3ID, LOCATION.SNL2ID, LOCATION.SNL1ID, LOCATION.CNTRYID, LOCATION.LRPLCE
FROM LOCATION
WHERE (((LOCATION.LOCID)>0));

Insert LOCDES1 locations into Access after local field LOCID has been changed to central LOCID

INSERT INTO LOCDES1 (LOCID, DTYPE, DUID, DVAL, DDATE, DREF)
SELECT LOCDES.LOCID, LOCDES.DTYPE, LOCDES.DUID, LOCDES.DVAL, LOCDES.DDATE, LOCDES.DREF
FROM LOCDES;

Insert NAMES1 names using the autonumeric quality of the central NID

INSERT INTO NAMES1 (GID, NTYPE, NSTAT, NUID, NVAL, NLOCN, NDATE, NREF)
SELECT NAMES.GID, NAMES.NTYPE, NAMES.NSTAT, NAMES.NUID, NAMES.NVAL, 
NAMES.NLOCN, NAMES.NDATE,  NAMES.NREF
FROM [NAMES]
WHERE (((NAMES.GID)>0));

Insert ATRIBUTS1 attributes using the autonumeric quality of the central AID

INSERT INTO ATRIBUTS1 (GID, ATYPE, AUID, AVAL, ALOCN, AREF, ADATE)
SELECT ATRIBUTS.GID, ATRIBUTS.ATYPE, ATRIBUTS.AUID, ATRIBUTS.AVAL, 
ATRIBUTS.ALOCN, ATRIBUTS.AREF, ATRIBUTS.ADATE
FROM ATRIBUTS
WHERE (((ATRIBUTS.GID)>0) AND ((ATRIBUTS.ATYPE)<>101 And (ATRIBUTS.ATYPE)<>102))
ORDER BY ATRIBUTS.AID DESC;

Insert GERMPLSM1 records after the local field GID has been changed to central GID

INSERT INTO GERMPLSM1 (GID, METHN, GNPGS, GPID1, GPID2, GERMUID, LGID, GLOCN, GDATE, GREF, GRPLCE)
SELECT GERMPLSM.GID, GERMPLSM.METHN, GERMPLSM.GNPGS, GERMPLSM.GPID1, 
GERMPLSM.GPID2, GERMPLSM.GERMUID AS Expr1, GERMPLSM.LGID, 
GERMPLSM.GLOCN, GERMPLSM.GDATE, GERMPLSM.GREF, GERMPLSM.GRPLCE
FROM GERMPLSM
WHERE (((GERMPLSM.GID)>0) AND ((GERMPLSM.GRPLCE)=0));

Insert PROGNTRS1 records after the local fields GID and PID have been changed to central equivalents

INSERT INTO PROGNTRS1 (GID, PNO, PID)
SELECT PROGNTRS.GID, PROGNTRS.PNO, PROGNTRS.PID
FROM PROGNTRS
WHERE (((PROGNTRS.GID)>0) AND ((PROGNTRS.PID)>0));

INSERT INTO LISTDATA1 (LISTID, GID, ENTRYID, ENTRYCD, SOURCE, DESIG, GRPNAME, LRECID, LRSTATUS)
SELECT DISTINCT LISTID, GID, ENTRYID, ENTRYCD, SOURCE, DESIG, GRPNAME, LRECID, LRSTATUS
FROM LISTDATA WHERE GID>0;

INSERT INTO LISTNMS1 (LISTID, LISTNAME, LISTDATE, LISTTYPE, listuid, LISTDESC, 
LHIERARCHY, LISTSTATUS)
SELECT DISTINCT LISTID, LISTNAME, LISTDATE, LISTTYPE, listuid, LISTDESC, LHIERARCHY, LISTSTATUS
FROM LISTNMS WHERE LISTID>0;

INSERT INTO instln1 (INSTALID, ADMIN, UDATE, UGID, ULOCN, UCID, UNID, UAID, ULDID, 
umethn, ufldno, UREFNO, UPID, ULISTID, ULRECID, IDESC, DMS_STATUS)
SELECT DISTINCT INSTALID, ADMIN, UDATE, UGID, ULOCN, UCID, UNID, UAID, ULDID, umethn, 
ufldno, UREFNO, UPID, ULISTID, ULRECID, IDESC, DMS_STATUS
FROM instln
WHERE instalid>0; 

INSERT INTO USERS1 (userid, instalid, ustatus, uaccess, utype, UNAME, UPSWD, PERSONID, 
ADATE, CDATE)
SELECT DISTINCT userid, instalid, ustatus, uaccess, utype, UNAME, UPSWD, PERSONID, ADATE, CDATE
FROM USERS
WHERE userid>0;

Change all references to GIDs in the Changes table (GID,GPID1,GPID2,PID) to their positive equivalents

UPDATE CHANGES, [UPDATE] 
SET CHANGES.CTO = [UPDATE].[GID]
WHERE (((CHANGES.CTO)=[UPDATE].[LGID]) 
AND ((CHANGES.CFIELD)="GID" Or (CHANGES.CFIELD)="GPID1" 
Or (CHANGES.CFIELD)="GPID2" Or (CHANGES.CFIELD)="PID") 
AND ((CHANGES.CRECORD)>0) AND ((CHANGES.CSTATUS)=0));

Change all GRPLCE fields for central GERMPLSM records replaced by other germplasm

UPDATE CHANGES 
INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID 
SET GERMPLSM1.GRPLCE = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GRPLCE)=0) AND ((CHANGES.CTABLE)="GERMPLSM") 
AND ((CHANGES.CFIELD)="GID") AND ((CHANGES.CTO)>0));

Change all GRPLCE fields for central GERMPLSM records deleted by themselves

UPDATE CHANGES 
INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID 
SET GERMPLSM1.GRPLCE = [CHANGES].[CRECORD], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GRPLCE)=0) AND ((CHANGES.CTABLE)="GERMPLSM") 
AND ((CHANGES.CFIELD)="GID") AND ((CHANGES.CTO)=0));

GPID1

UPDATE CHANGES 
INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID 
SET GERMPLSM1.GPID1 = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GPID1)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) 
AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GPID1") 
AND ((CHANGES.CTO)>=0));

GPID2

UPDATE CHANGES 
INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID 
SET GERMPLSM1.GPID2 = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GPID2)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) 
AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GPID2") 
AND ((CHANGES.CTO)>=0));

GDATE

UPDATE CHANGES 
INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID 
SET GERMPLSM1.GDATE = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) 
AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GDATE") 
AND ((CHANGES.CTO)>=0));

GLOCN

UPDATE CHANGES 
INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID 
SET GERMPLSM1.GLOCN = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) 
AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GLOCN") 
AND ((CHANGES.CTO)>=0));

METHN

UPDATE CHANGES 
INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID 
SET GERMPLSM1.METHN = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.METHN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) 
AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="METHN") 
AND ((CHANGES.CTO)>=0));

GNPGS

UPDATE CHANGES 
INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID 
SET GERMPLSM1.GNPGS = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GNPGS)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) 
AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GNPGS") 
AND ((CHANGES.CTO)>=-1));

NSTAT

UPDATE CHANGES 
INNER JOIN NAMES1 ON CHANGES.CRECORD = NAMES1.NID 
SET NAMES1.NSTAT = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((NAMES1.NSTAT)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) 
AND ((CHANGES.CTABLE)="NAMES") AND ((CHANGES.CFIELD)="NSTAT") 
AND ((CHANGES.CTO)>=0));

NDATE

UPDATE CHANGES 
INNER JOIN NAMES1 ON CHANGES.CRECORD = NAMES1.NID 
SET NAMES1.NDATE = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((NAMES1.NDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) 
AND ((CHANGES.CTABLE)="NAMES") AND ((CHANGES.CFIELD)="NDATE"));

NLOCN

UPDATE CHANGES 
INNER JOIN NAMES1 ON CHANGES.CRECORD = NAMES1.NID 
SET NAMES1.NLOCN = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((NAMES1.NLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) 
AND ((CHANGES.CTABLE)="NAMES") AND ((CHANGES.CFIELD)="NLOCN") AND ((CHANGES.CTO)>=0));

NTYPE

UPDATE CHANGES 
INNER JOIN NAMES1 ON CHANGES.CRECORD = NAMES1.NID 
SET NAMES1.NTYPE = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((NAMES1.NDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) 
AND ((CHANGES.CTABLE)="NAMES") AND ((CHANGES.CFIELD)="NTYPE"));

UPDATE CHANGES 
INNER JOIN ATRIBUTS1 ON CHANGES.CRECORD = ATRIBUTS1.AID 
SET ATRIBUTS1.ATYPE = [changes].[cto], CHANGES.CSTATUS = 2
WHERE (((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="atributs") 
AND ((CHANGES.CFIELD)="atype") AND ((CHANGES.CTO)>=0));

UPDATE CHANGES 
INNER JOIN ATRIBUTS1 ON CHANGES.CRECORD = ATRIBUTS1.AID 
SET ATRIBUTS1.ALOCN = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((ATRIBUTS1.ALOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) 
AND ((CHANGES.CTABLE)="ATRIBUTS") AND ((CHANGES.CFIELD)="ALOCN"));

INSERT INTO UPDCHNG (LCID)
SELECT CHANGES.CID
FROM CHANGES
WHERE (((CHANGES.CSTATUS)=2) AND ((CHANGES.CRECORD)>0))
ORDER BY CHANGES.CID DESC;

UPDATE CHANGES 
INNER JOIN UPDCHNG ON CHANGES.CID = UPDCHNG.LCID 
SET CHANGES.CID = [UPDCHNG].[CID], CHANGES.CSTATUS = [UPDCHNG].[LCID];

INSERT INTO CHANGES1 (CID, CTABLE, CFIELD, CRECORD, CFROM, CTO, CDATE, CTIME, 
CGROUP, CREF, CSTATUS, CDESC)
SELECT CHANGES.CID, CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CRECORD, 
CHANGES.CFROM, CHANGES.CTO, CHANGES.CDATE, CHANGES.CTIME, CHANGES.CGROUP, 
CHANGES.CREF, CHANGES.CSTATUS, CHANGES.CDESC
FROM CHANGES
WHERE (((CHANGES.CID)>0));