Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oops! Sorry, I fixed it.

    I have a project where one requirement is to allow users to enter up to 3000 characters in an Access field. I then run a routine to update the data into SQL Server using the following:

    ODBC connection in VBA:

    Code:
    CONSQLSVR = "ODBC;Driver=SQL SERVER;SERVER=<MY SERVER>;UID=<MY USERID>;PWD=" & Password & ";Database=<MY DB>;"
    Create a linked table and run a simple select into statement on the linked table from the Access table:

    Code:
        Set tdf = CurrentDb.CreateTableDef("dbo_cr_DataFromReview")
        tdf.Connect = CONSQLSVR
        tdf.SourceTableName = "dbo.cr_DataFromReview"
        
        CurrentDb.TableDefs.Append tdf
               
        'Update CR with Review RUG data
        strSQL = "INSERT INTO dbo_cr_DataFromReview SELECT * FROM qryEditRUGItems;"
        DoCmd.RunSQL strSQL
    this all works fine, but unfortunately data is being truncated. I link the table into Access from SQL Server and the linked table refuses to allow more than 255 characters in the target column. I have tried several different datatypes in SQL Server:

    nvarchar(3500)
    nvarchar(MAX)
    varchar(MAX)
    ntext

    none of this helps. Access just refuses to believe me.

    Any ideas?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Since developers need to do this fairly regularly with SQL Server tables, what was the solution? (I've done it previously with no issues in Access 2000/2002/2003 and SQL Server 2000, but haven't had a need to recently.)
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    Since developers need to do this fairly regularly with SQL Server tables, what was the solution? (I've done it previously with no issues in Access 2000/2002/2003 and SQL Server 2000, but haven't had a need to recently.)

    The solution was, 'remember to do your testing with the correct db.' i was erroneously changing the column type in development and testing with a linked table on production... sheesh! anyhow, if you change the type to Varchar(MAX) or NVarchar(3500) in SQL Server, the linked table does display the type as 'Memo'.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Sorry - I didn't mean to embarass you - we've all done things like that. (I was trying to find something in a database a couple of hours ago and discovered I was in the database for another state. ) And the choice of data type is useful to folks. Thanks for filling in the gaps.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    Sorry - I didn't mean to embarass you - we've all done things like that. (I was trying to find something in a database a couple of hours ago and discovered I was in the database for another state. ) And the choice of data type is useful to folks. Thanks for filling in the gaps.
    no problem. and, yes, a little embarrassing... I am happy with the fact that Nvarchar(3500) is sufficient rather than Varchar(MAX) or something like that.

Posting Permissions

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