Go Back   Access World Forums > Microsoft Access Discussion > Access Web

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-22-2017, 09:20 PM   #1
ram_rtg
Newly Registered User
 
Join Date: Jun 2015
Location: Australia
Posts: 46
Thanks: 30
Thanked 0 Times in 0 Posts
ram_rtg is on a distinguished road
Incorrect number of characters during move from access table to SharePoint lists

Hi everyone,

I want to convert the back end data tables of my split database (Access 2013) into SharePoint 2013 lists.

The first step I did was to make those tables local to the database then build the database sub-site on SharePoint.

I then proceeded to export those tables to SharePoint by using the Database Tools >SharePoint button and pointed the target lists location to the newly created sub-site.

The process runs OK but when I take a look under the tables in the database, I see a local table named "Move to SharePoint Site Issues" that contains a single record with the following fields:
Issue: Some data will not be moved.
Reason: "SharePoint only supports 8192 characters in memo fields.
Object Type: tblMyData
Field Name: Notes

The Notes field is "Long Text" Data Type and when I exported the table to a Spreadsheet the largest has a character count of only 4000.

When I examine the data further I noticed that there are a few carriage returns where the user has hit "Enter" key to separate their notes by a date.

Does anyone know what might be causing this error?

Thanks in advance.

__________________
Newbie using Access 2013

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ram_rtg is offline   Reply With Quote
Old 08-23-2017, 01:23 AM   #2
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 4,013
Thanks: 47
Thanked 1,363 Times in 1,291 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about arnelgp has a spectacular aura about
Re: Incorrect number of characters during move from access table to SharePoint lists

You already has the answer.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-30-2017, 06:57 PM   #3
ram_rtg
Newly Registered User
 
Join Date: Jun 2015
Location: Australia
Posts: 46
Thanks: 30
Thanked 0 Times in 0 Posts
ram_rtg is on a distinguished road
Re: Incorrect number of characters during move from access table to SharePoint lists

Quote:
Originally Posted by arnelgp View Post
You already has the answer.
Do you mean that the "Enter" keys are the equivalent of 4192 characters?
There wouldn't be more than 20 of them in each memo field.

__________________
Newbie using Access 2013

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ram_rtg is offline   Reply With Quote
Old 08-30-2017, 07:14 PM   #4
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 941
Thanks: 207
Thanked 114 Times in 105 Posts
NauticalGent will become famous soon enough
Re: Incorrect number of characters during move from access table to SharePoint lists

I had similar issues with my move. Did you do a compact and repair before your migration?

Also, to identify which records were the culprit, I did a query on my memo fields. For example in my query grid I used FieldLength:Len([myMemoField]). The results were a character count which I was able to sort from high to low. Turns out I had a few records with a count of over 10k.

Paring those records down is easy too if you need too. Good luck and let me know if you need to alter some of your records.
__________________
...see, what had happened was...
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
ram_rtg (08-31-2017)
Old 08-30-2017, 08:32 PM   #5
ram_rtg
Newly Registered User
 
Join Date: Jun 2015
Location: Australia
Posts: 46
Thanks: 30
Thanked 0 Times in 0 Posts
ram_rtg is on a distinguished road
Re: Incorrect number of characters during move from access table to SharePoint lists

Quote:
Originally Posted by NauticalGent View Post
I had similar issues with my move. Did you do a compact and repair before your migration?

Also, to identify which records were the culprit, I did a query on my memo fields. For example in my query grid I used FieldLength:Len([myMemoField]). The results were a character count which I was able to sort from high to low. Turns out I had a few records with a count of over 10k.

Paring those records down is easy too if you need too. Good luck and let me know if you need to alter some of your records.
Thanks for your help. I have just tried a compact and repair however the error still persists. I'm thinking that perhaps I need to get to the data tables whilst there are no users accessing the database to ensure just in case that is affecting something.

In order to identify which records had more the 8192 characters in the memo field I exported the table to excel then did a LEN(cell#) then sorted by greatest value in a similar way you used a query.

I'll try the query method just in case that works better/differently.

[EDIT]Hey NauticalGent, I tried using the LEN function inside a query and that returned 11 records with values over 8192 characters so I guess there is a difference between how ACCESS and Excel identifies characters. Thank you very much for your help with this.
Now all I have to do is figure out how to get the database to display the carriage return in the text box on the form. It seems to have dropped off and sentences are no longer separated by a carriage return or a space even.
[/EDIT]
__________________
Newbie using Access 2013

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by ram_rtg; 08-30-2017 at 10:25 PM.
ram_rtg is offline   Reply With Quote
Old 08-31-2017, 07:17 PM   #6
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 941
Thanks: 207
Thanked 114 Times in 105 Posts
NauticalGent will become famous soon enough
Re: Incorrect number of characters during move from access table to SharePoint lists

Quote:
Originally Posted by ram_rtg View Post
Now all I have to do is figure out how to get the database to display the carriage return in the text box on the form. It seems to have dropped off and sentences are no longer separated by a carriage return or a space even.
If there were there before, SP must have removed them during the migration process. I seem to remember something similar and decided I would change them as I encounter them and live with it.

One thing you should be aware of is that when you "make that move" is that SP makes the default length of each text filed 255. You can go in and edit them so that your lists are not so large. This will increase performance significantly.

Here is a BLOG I stumbled across a couple of months back that has helped me a bit:

https://accessexperts.com/blog/2011/...-fit-together/

Great info that I just could not find anywhere else. The author co-wrote a book which I have ordered but not received yet, hopefully soon.

Anyways best of luck and if you happen to pick a few tips and tricks, please share!
__________________
...see, what had happened was...
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
ram_rtg (10-12-2017)
Old 10-12-2017, 04:46 PM   #7
ram_rtg
Newly Registered User
 
Join Date: Jun 2015
Location: Australia
Posts: 46
Thanks: 30
Thanked 0 Times in 0 Posts
ram_rtg is on a distinguished road
Re: Incorrect number of characters during move from access table to SharePoint lists

Quote:
Originally Posted by NauticalGent View Post
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

CurrentDb.TableDefs("tblPracticeEmployeeData").Ref reshLink

'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
Exit_NewCustID:
Exit Function

'If an error occurred, display a message, then go to Exit statement
NextID_Err:
MsgBox "Error " & Err & ": " & Error$

Resume Exit_NewCustID

End Function

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?

__________________
Newbie using Access 2013

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ram_rtg is offline   Reply With Quote
Old 10-12-2017, 07:41 PM   #8
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 941
Thanks: 207
Thanked 114 Times in 105 Posts
NauticalGent will become famous soon enough
Re: Incorrect number of characters during move from access table to SharePoint lists

Just to be clear, once you exported the table to SP with a query, you linked the list, correct?

If I have that right, then you should be using the ID field that SP uses (equivalent to Access’s PK/Autonumber) as your Customer ID. This will alleviate the need to do the Dmax+1 bit.

Also, be sure to Cache option that is addressed in the same article I provided. The performance boost is phenomenal - I have remote users in the Middle East and Spain that are able to work in real time mode with virtually zero latency.
__________________
...see, what had happened was...
NauticalGent is offline   Reply With Quote
Old 10-12-2017, 09:09 PM   #9
ram_rtg
Newly Registered User
 
Join Date: Jun 2015
Location: Australia
Posts: 46
Thanks: 30
Thanked 0 Times in 0 Posts
ram_rtg is on a distinguished road
Re: Incorrect number of characters during move from access table to SharePoint lists

Quote:
Originally Posted by NauticalGent View Post
Just to be clear, once you exported the table to SP with a query, you linked the list, correct?

If I have that right, then you should be using the ID field that SP uses (equivalent to Accessís PK/Autonumber) as your Customer ID. This will alleviate the need to do the Dmax+1 bit.

Also, be sure to Cache option that is addressed in the same article I provided. The performance boost is phenomenal - I have remote users in the Middle East and Spain that are able to work in real time mode with virtually zero latency.

Yes that is correct. I had to link the list afterwards.

I was using the autonumber PK ID field however I have had to remove that as I was advised it was not good practice to have an autonumber as a meaningful field. So I had to programatically create autonumbering using Dmax+1.

Also if I have a PK Autonumber field with the name "ID" that is reserved for SP what implications would that have on the data once "OLD_ID" is created during migration?
__________________
Newbie using Access 2013

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by ram_rtg; 10-12-2017 at 09:52 PM.
ram_rtg is offline   Reply With Quote
Old 10-12-2017, 10:12 PM   #10
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 941
Thanks: 207
Thanked 114 Times in 105 Posts
NauticalGent will become famous soon enough
Re: Incorrect number of characters during move from access table to SharePoint lists

Quote:
Originally Posted by ram_rtg View Post
I was using the autonumber PK ID field however I have had to remove that as I was advised it was not good practice to have an autonumber as a meaningful field. So I had to programatically create autonumbering using Dmax+1.
I appears you have experienced the exception to that "advice" first-hand!
__________________
...see, what had happened was...
NauticalGent is offline   Reply With Quote
Old 10-13-2017, 12:05 PM   #11
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 941
Thanks: 207
Thanked 114 Times in 105 Posts
NauticalGent will become famous soon enough
Re: Incorrect number of characters during move from access table to SharePoint lists

Quote:
Originally Posted by ram_rtg View Post
Also if I have a PK Autonumber field with the name "ID" that is reserved for SP what implications would that have on the data once "OLD_ID" is created during migration?
Sorry, I missed this bit completely. SP will keep your PK field as long as it is an Autonumber...however...it will re-index it on migration and start over from 1. If you donít have any gaps (deleted records) then it should synch. But even so, SP will make a new field and name it _oldID and store the prior values there.

If you have your relationships set up correctly, by that in mean one-to-many with enforced integrity, then it will move all those tables as well and ensure you FK on related tables match up with the master table.

Just be sure to make a backup of everything before you migrate- just in case!

__________________
...see, what had happened was...
NauticalGent is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Microsoft Access with Sharepoint lists JamesN Theory and practice of database design 2 03-03-2017 10:20 AM
Using Access tables linked to Sharepoint lists JamesN Tables 2 02-16-2017 11:40 PM
Access tables to sharepoint lists bsells General 1 08-24-2015 12:41 PM
Can not link from MS Access to SharePoint admin lists modelist Modules & VBA 0 07-23-2010 12:28 PM




All times are GMT -8. The time now is 08:56 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World