Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trouble linking a SQL Server varchar field to memo field

Status
Not open for further replies.
TerryDASNY

TerryDASNY

Programmer
Sep 29, 2003
3
US
Hello,

I am linking a SQL Server view that has a varchar (500) field containing a comment. When I link this view to MS Access, the varchar field converts to a text (255) and truncates the data. I tried modifing the data type in the linked view in Access to a memo data type, but Access will not allow me to change properties on a linked table or view. Is there a way to link a large varchar field (500 characters)in SQL Server to a MS Access memo field?
 
Sort by date Sort by votes
Varchar does not match to memo; varchar matches to text and so the incompatibility is that SQL varchar can have 8000(?) characters and Access text can only have 255.
SQLServer Text fields convert to Access memo fields.

Don't blame me - take it up with Billy G.

 
Upvote 0 Downvote
Thanks for your suggestions lupins46. I changed the SQL Server data type for that field to text ntext and nchar relinked the view each time and all of those SQL Server data types converted to Access data type of text (255). I still wind up with my truncated comment field. Bummer.
 
Upvote 0 Downvote
Can't explain that.
I've just run a test here and got memo fileds in Access when linking to Text fields in SQL Server.

Don't know what to suggest.
 
Upvote 0 Downvote
Thanks again lupins46. I did get your suggestion to work once I changed the data type of the field in the view to a type of ntext(CAST(Comments as ntext)). Initially I was changing the Comments field data type in the table that was the data source for the view I was linking to MS Access. Once I changed the field in the view it worked like a charm. Thanks again for your help. You helped make a couple of users very happy in our organization.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

breezett93
  • Locked
  • Question
Field is being rounded in the export process
Replies
17
Views
649
strongm
strongm
JayRo71
  • Locked
  • Question
Unable to sort date field
Replies
4
Views
264
JayRo71
JayRo71
serino
  • Question
Help with a Query. 1
Replies
13
Views
318
strongm
strongm
mhshane
  • Locked
  • Question
Query to find keywords in description field and give count
Replies
4
Views
268
Andrzejek
Andrzejek
Swi
  • Locked
  • Question
Jet Sql Statement - Select Distinct Multiple Columns 1
Replies
4
Views
563
Swi
Swi

Part and Inventory Search

Sponsor

Back
Top