Ms Access linking table with nvarchar(max)

Question

I'm having problems seeing the correct data type when linking my Access tables to MS SQL Server 2005. My varchar(max) fields are showing as text(255). I'm using the SQL Native Client.Has anyone else ssen this issue?

Answer 1

The fundamental data type for this column is SQL_VARCHAR which, I'm assuming, is being mapped to text(255) internally in Access...this seemed reasonable at the time since this data type in MS SQL Server 2000 could not be larger than 255 characters.

SQL_LONGVARCHAR is the server type that is mapped to the Memo field, which I assume to be the Access data type that you want for this data. If that assumption is correct, it looks like you'll need to change the server data type to SQL_LONGVARCHAR (e.g. create the column as type 'text' instead of 'varchar(max)').

Alternatively, you may want to take a look at a beta build of Office 2007 (NOTE: the typical disclaimer around beta software applies...do not install on a business critical machine, etc.). One would presume that Access 2007 would make the type translation that you were looking for...unfortunately, I left my laptop with the Office 2007 beta install at home.

Answer 2

First, make sure you are using VARCHAR and not NVARCHAR.

Second, do not use the SQL Native client. VARCHAR works fine using the regular ODBC SQL client.

 

In the test I did, only nvarchar(max) did this. When I was using the ODBC SQL Client.