February 16, 2012 at 8:10 am
Hi everybody!
1. My front end is Access 2003.
2. My backend - SS2K8
Problem:
When I open ADO recordset using stored procedure + ADO.command, fields nvarchar(max) DO NOT return data. Intellisense in VBA recordset field shows just a set of little squares.
The problem can be solved if I cast nvarchar to Text inside the stored procedure.
HOWEVER, they say that TEXT datatype is obsolete and is not going to be supporterde in the future.
Can anybody suggest another way?
Thanks
February 16, 2012 at 8:32 am
What driver are you using for the ODBC datasource? Try using SQL Native Client (SQLNCLI10). If it doesn't show up in your ODBC applet you'll need to install it.
The probability of survival is inversely proportional to the angle of arrival.
February 16, 2012 at 9:55 am
This is the driver I use
SQL Server Native Client 10.0 (SQLNCLI10.DLL)
February 16, 2012 at 9:58 am
I don't believe Access 2003 will handle nvarchar(max)
The SQL Backend is fine with it, the Access front end is not. So in this case I'd make the client do a cast to nvarchar(max) from ntext when saving, and anothet cast from nvarchar(max) to ntext when retrieving it.
But you said you're using SQL Server 2k... SQL 2K doesn't support nvarchar(max) either.
February 16, 2012 at 10:13 am
It handles perfectly the other way - when I insert into SQL table and set the parameter
cmd.CreateParameter("@CaseSummary", adLongVarWChar, adParamInput, -1, IIf(IsNull(txtCaseSummary.Value), Null, txtCaseSummary.Value))
So Access 2003 handles something at least upstream
February 16, 2012 at 10:15 am
By the way I'm sorry, I am using Sql Server 2008
February 16, 2012 at 10:20 am
I have been able to seamlessly handle varchar(max) with older ASP and vb-script code that expect 'text' by using "DataTypeCompatibility=80" in the connection string. There might be a setting in the ODBC applet that lets you specify this.
Or, you can set up pass through query on the Access side that uses a connection string instead of a DSN.
The probability of survival is inversely proportional to the angle of arrival.
February 16, 2012 at 10:20 am
valeryk2000 (2/16/2012)
It handles perfectly the other way - when I insert into SQL table and set the parameter
cmd.CreateParameter("@CaseSummary", adLongVarWChar, adParamInput, -1, IIf(IsNull(txtCaseSummary.Value), Null, txtCaseSummary.Value))
So Access 2003 handles something at least upstream
Access internally uses Memo to store those values. nvarchar(max) was introduced with SQL 2005, which came out after Access 2003. ergo Access 2003 doesn't understand nvarchar(max) when the type is returned from SQL. I've run into the same problems using Excel 2003 to query databases for managers who want dashboards.
Upgrade the Access application to Access 2007 or 2010 and you might have better luck, or cast the data from a value that SQL 2008 understands to one that Access 2003 understands is a representation of it.
Or try what sturner has above. It's basically the same thing, except SQL will return that the type of the field is ntext. And then Access will handle it properly.
February 16, 2012 at 10:26 am
Right. I suspect that normal vb/vb script would handle it ok, but Access has problems
February 16, 2012 at 10:28 am
So casting to text/ntext (what I am currently doing) is the only way to do it?
February 16, 2012 at 10:37 am
valeryk2000 (2/16/2012)
So casting to text/ntext (what I am currently doing) is the only way to do it?
AFAIK unless you can configure the driver somehow to map that for you that is the only way.
I am no access expert, but I'd look to see if there were a way to customize the connections string it uses for its linked table feature to specify the DataTypeCompatibility option.
I did not see any way to set that option in the DSN itself, but Access still would need to use a connection string to make the connection even though it is using a DSN and it would be nice it had a way for your app to sett additional options for this string.
The other options is, as someone else mentioned, is to upgrade your version of Access.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply