I do this quite a lot.

Below are two Access functions which might help.

First you need the last key which was used on the TAXON_OCCURRENCE_DATA table. Before you do this, run the R6 Addin UpdateLastKey to make sure the Last_KEY table is properly sychronised) I use this function (but there are many different ways of doing it, I do it this way for historic reasons which I won't go into (of course it assumes that your access database has the necessary tables linked into it.) The parameter TableName should be set to "TAXON_OCCURRENCE_DATA"

Public Function LastKey(ByVal TableName As String) As String

    Dim KeySet As Recordset
    Set KeySet = CurrentDb.OpenRecordset("SELECT LAST_KEY.LAST_KEY_TEXT " & _
                                            "FROM LAST_KEY " & _
                                            "WHERE (((LAST_KEY.TABLE_NAME)='" & TableName & "'));")
    KeySet.MoveFirst
    LastKey = KeySet!LAST_KEY_TEXT
   
End Function

The output from this is fed into the following function, which gives you the last eight characters of the next available key.

Public Function IncrementKey(ByVal LastKey As String) As String

    'Increments key (base 36)
    Dim ThisChar, IncChar As String, CharPlace, CarryOver As Integer
    CharPlace = 8: CarryOver = 1
    Do While CharPlace > 0
        ThisChar = Mid(LastKey, CharPlace, 1)
        If CarryOver = 1 Then
            Select Case ThisChar
                Case "9"
                    IncChar = "A"
                    CarryOver = 0
                Case "Z"
                    IncChar = "0"
                    CarryOver = 1
                Case Else
                    IncChar = Chr(Asc(ThisChar) + 1)
                    CarryOver = 0
            End Select
        Else
            IncChar = ThisChar
            CarryOver = 0
        End If
        LastKey = Left(LastKey, CharPlace - 1) & IncChar & Right(LastKey, 8 - CharPlace)
        CharPlace = CharPlace - 1
    Loop
    If LastKey <> "00000000" Then
        IncrementKey = LastKey
    Else
        IncrementKey = "#ERROR#"
    End If
   
End Function

Simply add your 8 character SiteID to the beginning of this to get the full 16 character key.

Again there are lots of ways which you can make this work as part of an Access query. My preferred one is to create a Global variable in access to hold the LastKey value (as output from the first function), then update it with the output from the second function each time it runs. This should give you sequential keys.

At the end of the process, once you have inserted all the data be sure to run UpdateLastKey again to synchronise the table.

Does that make sense, or have I just baffled you?