Solved

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

Posted on 2014-07-30
11
250 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now