SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 MS Access using ODBC to MSSQL - TEXT vs. VARCHAR
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eltonwheelock
Starting Member

5 Posts

Posted - 10/14/2010 :  17:06:12  Show Profile  Reply with Quote
We have an old Access app that uses an ODBC connection to SQL for a backend. The SQL database used to be access, and was converted, but many of the fields were left as TEXT data type.

We are in the process of redeveloping the Access app, but we also need to implement some triggers on the tables in the meantime - and I cannot create a trigger on a table that has TEXT columns.

I have read that VARCHAR(MAX) is an equivalent replacement for TEXT. However, when I made the change, Access was no longer able to write to the columns. What's really strange is that no errors were thrown, the app thinks it wrote to the field, but the data wasn't there. Something's not adding up, and my question to the forum is, has anyone else had a similar issue with Access connecting to SQL via ODBC and/or with the TEXT vs. VARCHAR issue.

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 10/15/2010 :  03:20:20  Show Profile  Reply with Quote
To be hones I have no real clue about what's ging on, I'll only make some suggestions: Which version of Access are you using? Can you try upgrading to see what happens? Have you reimported the new table definitions to the access-file or are you using the old definitions? Access holds metadata of the underlaying table so an update here might be necessary.

You can also review some of these google hits:
http://www.google.no/search?hl=no&q=+site:social.msdn.microsoft.com+access+varchar(max)&sa=X&ei=OwC4TOmcA8rKswb_konHDQ&ved=0CBsQrQIwAA

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000