Solved

MS Access to SQL Server online (SharePoint 2013) data conversion, Return character missing.

Posted on 2014-07-30
11
255 Views
Last Modified: 2014-10-03
I have successfully converted (uploaded) an MS Access database to a SharePoint online SQL database, database created, all tables etc. looking good.
Problem is that the 'Return' character in text fields is now missing.  E.g. a text box that used to hold an address, with a return between each address line is now one long string.
0
Comment
Question by:Paul-bbc
11 Comments
 
LVL 84
ID: 40228720
First: You really should break an address down into atomic components (i.e. City, State, Zip, etc) and store those separately, then concat the values as needed for display.

Are you sure those were Text fields, and not Memo fields? I rarely converted over to SP, since there were so many troubles after doing so, but if I recall there were issues with converting Memo fields.
0
 

Author Comment

by:Paul-bbc
ID: 40228962
The address field is Text 255, and has been deliberately designed to hold the whole address.
The Zip (UK Postcode) is held is a separate field and these two fields have been designed to match a government system data schema so have no choices as to how has been designed.  Only the Postcode is relevant for reporting.

In SharePoint 2013 Online it creates an SQL Azure db, and most of the old issues seem to have disappeared since 2013.
65,000 customer records, each with data in up to 11 related tables all created successfully.  Except for the Return issue in the address field.

The only thing different with this text field is that the bound form control for it was formatted 'New line' on Enter Key, but should not really affect it as it is at form level not data level.

If I look at the raw data in the Access tables the Returns are there.
0
 
LVL 57
ID: 40228997
<<If I look at the raw data in the Access tables the Returns are there>>

Sounds like a "by design" issue (most likely) or a bug in the generated SharePoint code.

The controls in an Access Web App are not very sophisticated.

Jim.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:Paul-bbc
ID: 40229211
Bit of a better explanation.  If I look at the raw data in the Access tables BEFORE I UPLOADED and created the online db, the returns are there.

The system is not being used with an Access web app. I have reconnected the original Access front end to the SharePoint SQL online db with ODBC connection.

When I look at the data either in the form control or at the raw data through the SQL linked Access table all the returns have gone.

There's something in the back of my mind from years ago when importing Excel data into Access, where users had used 'Ctrl', 'Enter' in Excel for a pseudo return character that never came across in Access.  Wondering if similar issue, trouble is I have 65,000 client records!
0
 
LVL 57
ID: 40229410
So SharePoint is not really in the picture at all other than having an Azure database sitting on the site, which your connecting to with a regular Access DB.

<<When I look at the data either in the form control or at the raw data through the SQL linked Access table all the returns have gone.>>

 That's something else entirely then.   What did you use to migrate the data?

Jim.
0
 

Author Comment

by:Paul-bbc
ID: 40229500
That's correct, just using Azure database, cheeky, cheap hosting!
Used desktop Access 2013 which has a SharePoint Online db creator wizard, and one of the steps allows you to select an existing database to upload as a start point.  Have SharePoint 2013 account with Access Services running so then simply allow external connections and get ODBC connection details.  So really it has not gone anywhere outside MS-Access desktop.  Data tables started life as 2003 Access but have been merrily following me along my journey in each new format.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40229839
<<SharePoint Online db creator wizard>>

That started life with 2007 and SharePoint lists, which don't support new line characters in a single text field.

What I would do is download the SSMA (SQL Server Migration Assistant):

http://www.microsoft.com/en-us/download/details.aspx?id=42656

 and upload one table as a test and you should find the line feeds are retained.

 This page:

Migrating Access Databases to SQL Server/Azure SQL DB (AccessToSQL)
http://msdn.microsoft.com/en-us/library/hh313051%28v=sql.110%29.aspx

 Walks you through a number of things.   There's a lot there, however there are only two things that you really need to be aware of:

1. The data type mappings in the project

2. Making sure you add a Timestamp column to the new tables.  This is turned off by default.  You want it on.

Jim.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40230742
Problem is that the 'Return' character in text fields is now missing.
How do you know that is the case.  If the answer is:  I inspected the values  using SSMS in grid mode, Then that would certainly explain it.  In order to see the return characters you need to use text mode.
0
 

Author Comment

by:Paul-bbc
ID: 40231209
Thank you both for your comments, I am going to be doing some testing over the next couple of days.
All comments above what I saw or did not see only relate to me eyeballing the data in the table or form, no technical inspection, I was hoping that it would not get this technical.
I was hopefully looking for a Replace Function that I could use on import, replacing the char value of what Access thought it had with one that SQL would certainly use as a Return value.
0
 

Author Comment

by:Paul-bbc
ID: 40231565
Not sure if I am talking about Return or Line Feed, or both.

21 High Street
Bournemouth
Dorset

ends up as:   21 High StreetBournemouthDorset

Whether viewed in the form or in the SQL table.
0
 
LVL 57
ID: 40360134
Did you ever get to the bottom of this?

If not, I think it would be best to un-accept this and simply delete it if you don't want to continue working on it and have moved on.

 There's no real answer here.

Jim.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question