Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-07-30
11
Medium Priority
?
263 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 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
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.

 

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

773 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