Convert MS Access Memo Field to SQL Server

Posted on 2009-02-19
Last Modified: 2012-08-14
I have a database that I am moving to SQL server and one of the tables is using a Memo field.  I have been told that we have limited space on the Server so I am trying to be mindful of the data types in SQL server.  I am looking for suggestions on what the data type should be for an Access Memo field.

It has been suggested to use either text or varchar for the field in SQL server.  Is there a way that I can query the current table to figure out the longest number of characters in the MEMO field.  If so can you please provide assistance with it?
Question by:bluefeet10
    LVL 4

    Accepted Solution

    Build a query on the table, and add this column:  MemoFieldLength: Len([MemoTypeField]).  When you run the query, that column will tell you how many characters there are in the memo field for each record.  If you sort the query by that column, descending, the highest number will be at the top, and you'll know how big your VarChar field needs to be.

    LVL 4

    Expert Comment

    BTW, your SQL field type should be VarChar, not Text... Varchar will use only the amount of space necessary to contain the characters in your field;  Text will use the specified amount of space, whether it's used or not.

    LVL 51

    Expert Comment

    by:Mark Wills
    It translates to IMAGE or TEXT or if not really binary and just textual, then can use varchar - in fact could be varchar(max) which allows up to 2 gig, but not so sure it will convert "cleanly" (dependant on content).

    Author Comment

    Thanks for the help!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    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.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    779 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

    9 Experts available now in Live!

    Get 1:1 Help Now