Solved

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

Posted on 2014-07-30
11
258 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

630 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