October 27, 2011 at 9:11 am
Some records in this field have len = 22000. When I run the query (Select Summary From MyTbl Where ID=SomeiD) in ss2k8 - everything is ok. When I run this query from Access form intellisense above the MyRs(0) shows a set of small squares with occasional alfanumerics, and nothing can be seen in the appropriate textbox. When I change the query - Select Cast(Summary as varchar(8000)) From MyTbl Where ID=SomeiD everything is ok, if you do not count that the result would be truncated to 8000 characters (alas ....). Interestingly, if I use cast(Summary as text) - it works, but they say text datatype is being eliminated ...
Any idea?
October 27, 2011 at 9:35 pm
Hi
From the description you have given, I suspect that you don't have just plain alphnumeric characters in your text. There may be carriage return, or similar, characters. Copy the contents of the output for one of the problem results and paste it into a text editor which either allows you to view all characters (including the non-printing and print-control characters such as carriage returns, line feeds, tabs, etc) or provides a hex editor mode.
My suspicion is that those little squares you are seeing will be one of these characters.
I hope this helps.
October 28, 2011 at 8:42 am
A couple of questions that might have bearing on your issue:
1 - What version of Access are you using?
2 - If you actually link to a view of the underlying SQL Server table, what data type does Access show the field as?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 28, 2011 at 11:00 am
Thanks guys,
ProofOfLife ==> this is not the case, when I cast(Summary as text) everything's ok - no squares
WendellB:
1. Access 2003
2. I do not link SQL server tables to Access, I'm using ado recordset instead
I suspect that the problem is the oledb driver - Microsoft added (n)varchar(MAX) lately (~ it's like Access Memo - and kept outside the table), before they were using (n)text, however the oledb driver does not 'know' how to handle this new datatype.
October 30, 2011 at 2:39 pm
A question I should have asked in my first post - what is the source of the data in this column? Are users able to copy and paste text from different sources? For the rows which are retruning this troublesome data, can you determine where the data has come from?
Cheers
October 30, 2011 at 5:11 pm
Part of the issue is Access 2003 - it is an 8 year old product, and support for newer features in SQL Server is a bit suspect as you deduced. If you can, try the same thing with Access 2010 and see what result you get. My suggestion about trying to link to the table is to see what kind of field type Access thinks it is - that might help in deciding what CAST in SQL Server works best. In general I avoid using unbound forms in Access unless there are compelling reasons for doing so - which rarely happens. You might find that ODBC linked tables based on a SQL view or stored procedure behave better than ADO recordsets. Finally, Access is not terribly adept at handling memo type fields - they tned to be a source of corruption when stored in Access tables, and any sorting or filtering that is done in Access is likely to truncate them at 256 characters.
Wendell
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 31, 2011 at 8:55 am
Thank you guys!
WendellB, you are right 100%. However 1. I must use Access 2003 (corporate policy), 2. The information in the base tables is highly sensitive (confidential) so I cannot use linked tables or even queries on the Access front end - only stored procedures. 3. Regrtetfully I need to keep the design of the existing Access application forms and reports design that current users are used to - only the data should be moved to SQL Server. I would rather use VB or Java to build a compilable front end, but they do not allow ...
When I link the SQL table with the varchar(MAX) field - Access handles it as Text, however i do not see that this matters, as I already mentioned the recordset from cast(Summary as text) solved the problem, and as far as we continue to use SQL Server 2008 we are not going to have a problem with that issue.
ProofOfLife - I hope that this reply answers your questions as well.
Thanks a lot
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply