?
Solved

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

Posted on 2014-07-30
11
Medium Priority
?
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 85
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 58
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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 58
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1500 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 58
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

718 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