Originally Posted by NauticalGent
Anyways best of luck and if you happen to pick a few tips and tricks, please share!
Hey thanks for all your help,
I have moved the data to SharePoint and after many hours tinkering and playing around I think I may have stumbled on solution regarding the number of character restriction. As a part of the migration process I had ran the query that identified all the records that exceeded 8192 and then rather than copying the data to a spreadsheet or table, I exported that query to a SharePoint list.
Voila! that worked like a charm and it got me thinking that perhaps I could have used a query to export the entire original table to a SP list.
Now I'm having trouble maintaining up to date data as the lists are not updating frequently enough to allow the allocation of unique customer IDs. I'm using the RefreshLink code as you can see below:
Public Function NewCustID() As Long
On Error GoTo NextID_Err
Dim lngNextID As Long
'Find highest Employee ID in the tblPracticeEmployeeData table and add 1
lngNextID = DMax("[lngID]", "tblPracticeEmployeeData") + 1
'Assign function the value of the Next ID
NewCustID = lngNextID
'Exit function now after successful incrementing or after error message
'If an error occurred, display a message, then go to Exit statement
MsgBox "Error " & Err & ": " & Error$
That works for a single user but when multiple users create new records simultaneously only one is successful the others get kicked off and get the message "This item could not be inserted or updated because duplicate values were found for one or more fields in the list.". Do you have any suggestions to overcome this issue?