Linked table not displaying as MEMO

Topic

New Reply

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=;UID=;PWD=" & Password & ";Database=;"

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?

Reply | Quote

Replies

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.)

Reply | Quote

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’.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote