I apologise in advance that this is a very long post, but I hope it will prove helpful!
There are five issues I am aware of that cause problems in Rec2002 to Rec6 transfer:
1. Corrupt memo fields in nbndata.mdb
This seems to be an occasional bug in versions of the Jet engine before v3.51 that screws up memo fields. It plagued us in the early days of Rec2K, but hopefully it is now rare. Memo fields are used widely in Recorder for comments and descriptions.
You usually find out that you have this problem because compressing the database gives an error like this:
The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time
The transfer process will fail if this problem is present in the nbndata.mdb you are trying to copy across!
So, try compressing your database (Tools - Database Tools - Compact). If it works you are OK, if you get this error then there are various things you can try to fix it. Check the discussions on the old Recorder group and/or do a Google search on the problem.
2. RECORD_TYPE entries markes as "System supplied"
This is my fault and due to a bug in the Import Wizard I wrote for Rec2002 :(. When it creates RECORD_TYPE entries in the database it marks them as "System supplied". Consequently, they are not copied to Rec6 by the transfer process (anything marked as "System supplied" is assumed to be present already in Rec6). As a consequence, any TAXON_OCCURRENCE rows that rely on these RECORD_TYPEs fail to transfer and therefore any TAXON_DETERMINATION, TAXON_OCCURRENCE_DATA, etc, etc that rely on the TAXON_OCCURRENCE also fail. Just one or two RECORD_TYPEs failing to transfer can cascade - resulting in thousands of other rows failing!
This is the most likley reason you saw "thousands" of errors reported.
Easy to fix. Open nbndata.mdb in Access and look at the RECORD_TYPE table. Only records with keys starting with "NBNSYS" should be marked a System supplied (-1 in the SYSTEN_SUPPLIED_DATA column). If any other records have a -1 here, change it to 0 (zero).
3. CHANGE_DATE is 00:00
Most tables in the Recorder data model have CHANGED_BY and CHANGE_DATE fields which record when the row was last edited. CHANGE_DATE is normally empty (Null) if the row has not been changed. However, if the row has been exported to Excel or text files (e.g. CSV), null gets exported as zero. If it is then re-imported, the value 00:00 ends up in the CHANGE_DATE field. This is interpreted by Access as midnight on 31 December 1899.
In the SQL Server verison of Recorder, CHANGE_DATE is a "smalldatetime" type field. SQL Server uses a different sytem for storing dates to Access and "31/12/1899" is outside the range of dates that can be stored in a smalltimedate field. Consequently, rows with a CHANGE_DATE of 00:00 fail to transfer.
This is fairly easy, although rather tedious, to fix. Open nbndata.mdb using Access and use update queries to relace 00:00 with Null in CHANGE_DATE columns. The following VBA will do this for you in one shot:
Option Compare Database
Option Explicit
Sub zerodate()
Dim db As DAO.Database
Dim t As DAO.TableDef
Dim f As DAO.Field
Dim sql As String
Set db = CurrentDb
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
For Each f In t.Fields
If f.Type = dbDate Then
sql = "UPDATE " & t.name & " SET [" & f.name & "]=Null WHERE ([" & f.name & "]=#00:00:00#);"
db.Execute (sql)
End If
Next
End If
Next
End Sub
To use this:
- Open nbndata.mdb,
- create a new module,
- go to Tools -References and make sure "Microsoft DAO 2.5/3.5 compatibility library" is ticked,
- paste this code in
- run it.
This has been fixed in later versions of the transfer process (I tested the version on 6.8.1 STANDALONE CD). This now replaces 00:00 in a smalldatetime field automatically with Null.
4. Records transferred between copies of Recorder 3 with incorrect TAXON_LIST_ITEM_KEY
This is a pretty obscure problem ultimately due to a deficiency in the record transfer process between copies of Recorder 3 which was well known at the time. When the built in Export/Import facilities in Recorder 3 were used to transfer records between copies, any SPECIES which had been added by the user at the source copy were not automatically transferred to the destination copy. There were ways of sending the necessary SPECIES records, but this could easily get forgotten. The consequence was RECORDS in the receiving copy of Recorder 3 with a species key with no corresponding entry in the SPECIES table.
When Recorder 3 data was transferred to Rec2K using the "Bulk move" facility, user added SPECIES were handled fully and were transfered to Rec2K. But if the necessary SPECIES entry didn't exist in Rec3 (because of failure to copy them accross from another copy of Rec3 from which records had been transferred), then of course there was no SPECIES record to transfer. The consequence years down the line is that the TAXON_LIST_ITEM_KEY in the TAXON_DETERMINATION derived from Rec3 will have an integer (e.g. 723) instead of a 16-character NBN key.
Note that this integer is NOT the original Recorder 3 SPECIES key, but an arbitary running integer generated by the Bulk export process when the Rec3 data was transferred to Rec2K.
Such TAXON_DETERMINATION rows will fail when transferred to Rec6 because the TAXON_LIST_ITEM doesn't exist.
You can easily check for this problem by opening nbndata.mdb in Access and looking at the TAXON_DETERMINATION table. View the table sorted by TAXON_LIST_ITEM and any rows with such integer keys will come to the top and be obvious.
One way to fix this is to track back through your archives and establish where the original Rec3 transfer file came from. I found a few cases on trying to transfer the Hoverfly Recording Scheme to Rec6 relating to three ancient Rec3 to Rec3 transfers. I was able to go back to the originator in each case and determine what the species should be and hence fully fix the problem (by replacing the integers in TAXON_LIST_ITEM_KEY with the correct key).
Another stratergy would simply be to delete the offending records from Rec2002. Globally, nothing is being lost because the records were transferred from sombody else - who presumably still has the originals in their system.
5. Taxon and/or Biotope Dictionary items in Recorder 2002 that are not present in Rec6
This is the subject of most of the postings in this thread - so I don't need to repeat it.
NOTE: YOU WILL ALMOST CERTAINLY SEE ERRORS REPORTED BY THE TRANSFER PROCESS EVEN IF IT WORKS PERFECTLY!
There are two reasons for this:
1. Items in the dictionaries of Rec2002 that are not marked as "sytem supplied". There seem to be some TAXON_FACTS, TAXON_DESIGNATIONS, etc that are not marked a "system supplied" but should be. The transfer process attempts to copy them across but they fail because they are already present in Rec6.
2. Vice-county locations added by the Import Wizard. When you install the Rec2002 Import Wizard, it installs a set of locations representing GB and Irish vice-counties. These are used to support the import of vice-county numbers. Because the ImportWizard functionality has become part of the core of Recorder 6, these entries are now "System supplied". Consequently, the transfer process will try and copy them across, but will fail because they already exist in Rec6. This effects LOCATION, LOCATION_NAME, GRID_SQUARE and LOCATION_ADMIN_AREAS and the rows all have keys starting "JNCCIMPW".
You can safetly ignore these errors. They wont affect the transfer of your data because the necessary rows are already in Rec6.
Of course the trick is realising which you do and don't have to worry about. Hopefully, these notes will help. This has been at least partially fixed in version 6.9 of Recorder 6 that we are currently testing. This no longer reports failures to copy a record into Rec6 as an error if the record is already present as a "System supplied" row in Rec6. But things like GRID_SQUARE and LOCATION_NAMEs belonging to the ImportWizard locations do still get reported because these tables don't have a SYSTEM_SUPPLIED_DATA flag.
