Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text/Nvarchar(MAX) datatypes (Access XP)

    Have a database with an Access front and SQL Server 2005 back end. I have a field in SQL server properties set to nvarchar(MAX) that should allow 4000 characters for data entry, but the field is restricted to the 255 of the text datatype of Access. You can look at the SQL tables in access format and you can see the 255 character restriction in field properties, but you cannot make it larger, delete it, or change the datatype in Access to memo as it is not supported in SQL Server. So my question is how do you get a field that will store comments longer than 255 characters?

    Carla

  2. #2
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text/Nvarchar(MAX) datatypes (Access XP)

    OK, I have found a way to force it to allow more characters by using ntext as the SQL datatype. I am thinking this is a very inefficient datatype as I recall that this datatype is not stored with the table itself. Is there a better solution. I also noted in the Access interface the datatype changes to Memo.

    Carla

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text/Nvarchar(MAX) datatypes (Access XP)

    Hi Carla,
    You can still use the sql datatype of varchar(8000) and yes the linking changes the access datatype to memo. See Sql Server Performance for some recommended sql tuning tips. Be careful when you do design this way as "unforeseen" problems can arise. See <post:=556,923>post 556,923</post:> for an example of some of them, and what to watch out for.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text/Nvarchar(MAX) datatypes (Access XP)

    Thank you for the additional information about "unforseen" complications. There have been more than a few unforseen complications migrating from Access to SQL Server backend databases. We are finding solutions with the help of the lounge and a lot of reasearch.

    After a little more trial and error we have found it is the (MAX) designation that is not translating. I think the MAX is new for SQL Server 2005 and I have Access 2002. If you use varchar(MAX) or nvarchar(MAX) you get a text field in the Access front limited to 255 characters. If you use varchar(4000) or nvarchar(4000) the Access front shows a Memo datatype and you can enter up to 4000 characters.

    Carla

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •