Thread: Re: Can't get more than 255 chars out of memo field in Acc
Re: Can't get more than 255 chars out of memo field in Acc
From
"Henshall, Stuart - Design & Print"
Date:
Benjamin Scherrey wrote:
> I'm using ODBC driver PostgreSQL + Unicode (Beta) 7.02.00.04
> to talk to a (linux) Postgres 7.3b5
> from Access 97 under WinXP. Using ODBC linked tables from the
> Access front end - I am unable to
> get a text field (which was formerlly a memo field when it
> was local to Access) to allow me to enter
> more than 255 characters into the database. I've tried
> altering the DataType options and increasing
> sizes about every iteration I can think of with no luck. Am I
> running into an ODBC driver issue or
> Access? Any ideas how I can get around this?
>
> thanx & later,
>
> Ben Scherrey
>
>
>
>
Ensure that text as long varchar is checked
hth,
- Stuart
This raises an issue for Access developers which, in spite of searching archives, FAQs etc. I have not seen explicitly stated anywhere. I have found that, to be able to use both "text" and "memo" types within Access, linking to tables in a PostgreSQL database, the following must be done: * table definitions in PostgreSQL must use "varchar" for fields desired to be "text" in Access, and "text" for fields desired to be "memo" in Access, and: * "Text As LongVarChar" must be checked in the DSN. This conflicts somewhat with the usual advice given on the use of PostgreSQL data types, which summarizes as: "varchar has no advantage over text, unless you have a reason to limit the size of the field". Since "text" is so easy to use (one size fits all), that can lead to a blithe use of "text" fields, which for Access developers can be a trap waiting to happen. I can see at least one major dump-and-edit session in my future. Since I am discussing Access development issues, I would be interested in comments on the boolean issue, which I see has caused much discussion in the past. I have found that with the "Bools As Char" option checked, all of my check boxes failed to work. They could be unchecked successfully, but when attempting to check an unchecked box, Access would raise an error like: "The data that you attempted to insert is too large for the field". Looking at the table design window in Access, I saw that all of the boolean fields were rendered as "text". After unchecking the "Bools As Char" option, and running the "booleqint4" operator creation code that has been posted to this list in the past, my problem went away. After relinking the tables, they now all show "Yes/No" for all of the boolean fields, and check boxes work as expected. I know that others like different solutions (using int2 has been mentioned), but I like the "fix once and forget" nature of my solution. Is there some pitfall that I am missing here? --- "Henshall, Stuart - Design & Print" <SHenshall@westcountry-design-print.co.uk> wrote: > Benjamin Scherrey wrote: > > I am unable to > > get a text field (which was formerlly a memo field > when it > > was local to Access) to allow me to enter > > more than 255 characters into the database. > > > Ensure that text as long varchar is checked > hth, > - Stuart > __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
11/27/2002 10:46:51 AM, Jeff Eckermann <jeff_eckermann@yahoo.com> wrote:
>This raises an issue for Access developers which, in
>spite of searching archives, FAQs etc. I have not seen
>explicitly stated anywhere.
>
>I have found that, to be able to use both "text" and
>"memo" types within Access, linking to tables in a
>PostgreSQL database, the following must be done:
>* table definitions in PostgreSQL must use "varchar"
>for fields desired to be "text" in Access, and "text"
>for fields desired to be "memo" in Access, and:
>* "Text As LongVarChar" must be checked in the DSN.
This turns out to be what my problem is I think. I do have the attribute defined as a 'text'
type in my postgres table definition but when linked to Access it still appears as 'text' in the table
definition. When I try to alter this setting to memo in Access it complains that it "can't save property
changes for linked tables". How to I get Access to recognize my Postgres 'text' attributes as memo
fields?
thanx & later,
Ben Scherrey
Where can we document this information? I don't see it on the techdocs site. --------------------------------------------------------------------------- Benjamin Scherrey wrote: > 11/27/2002 10:46:51 AM, Jeff Eckermann <jeff_eckermann@yahoo.com> wrote: > > >This raises an issue for Access developers which, in > >spite of searching archives, FAQs etc. I have not seen > >explicitly stated anywhere. > > > >I have found that, to be able to use both "text" and > >"memo" types within Access, linking to tables in a > >PostgreSQL database, the following must be done: > >* table definitions in PostgreSQL must use "varchar" > >for fields desired to be "text" in Access, and "text" > >for fields desired to be "memo" in Access, and: > >* "Text As LongVarChar" must be checked in the DSN. > > This turns out to be what my problem is I think. I do have the attribute defined as a 'text' > type in my postgres table definition but when linked to Access it still appears as 'text' in the table > definition. When I try to alter this setting to memo in Access it complains that it "can't save property > changes for linked tables". How to I get Access to recognize my Postgres 'text' attributes as memo > fields? > > thanx & later, > > Ben Scherrey > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
--- Benjamin Scherrey <scherrey@proteus-tech.com> wrote: > 11/27/2002 10:46:51 AM, Jeff Eckermann > <jeff_eckermann@yahoo.com> wrote: > > >This raises an issue for Access developers which, > in > >spite of searching archives, FAQs etc. I have not > seen > >explicitly stated anywhere. > > > >I have found that, to be able to use both "text" > and > >"memo" types within Access, linking to tables in a > >PostgreSQL database, the following must be done: > >* table definitions in PostgreSQL must use > "varchar" > >for fields desired to be "text" in Access, and > "text" > >for fields desired to be "memo" in Access, and: > >* "Text As LongVarChar" must be checked in the DSN. > > This turns out to be what my problem is I think. I > do have the attribute defined as a 'text' > type in my postgres table definition but when linked > to Access it still appears as 'text' in the table > definition. When I try to alter this setting to memo > in Access it complains that it "can't save property > changes for linked tables". How to I get Access to > recognize my Postgres 'text' attributes as memo > fields? > > thanx & later, > > Ben Scherrey > The implication is, there is no workaround. You will need to change the table definitions in your PostgreSQL database to use varchar(n) datatypes whereever you want Access to see text. It is possible to hack the system tables to achieve this (search the archives to get instructions), but the cleanest way definitely is to dump the database, edit the dump file and then restore it (I will be doing this myself quite soon :-)). Set "Text As LongVarChar" to on, relink your tables, and you should be good to go. __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
Jeff Eckermann <jeff_eckermann@yahoo.com> writes:
> The implication is, there is no workaround. You will
> need to change the table definitions in your
> PostgreSQL database to use varchar(n) datatypes
> whereever you want Access to see text.
I wonder whether we could/should hack our ODBC driver to report "text"
columns as "varchar(n)" (and if so, what n should we use)? Seems like
enough people want to use Access that this would be a useful option.
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Jeff Eckermann <jeff_eckermann@yahoo.com> writes: > > The implication is, there is no workaround. You > will > > need to change the table definitions in your > > PostgreSQL database to use varchar(n) datatypes > > whereever you want Access to see text. > > I wonder whether we could/should hack our ODBC > driver to report "text" > columns as "varchar(n)" (and if so, what n should we > use)? Seems like > enough people want to use Access that this would be > a useful option. > > regards, tom lane The problem lies in the Access distinction between "text" type, which is fully functional but limited in size (the OP's problem), and "memo", which allows much more storage, but is limited in functionality: "memo" fields cannot be indexed, sorted, grouped by, joined against etc. The driver can presently render text as "text" or as "memo", depending on the chosen configuration. I don't see how it could reasonably distinguish between two text fields so as to call one "text" and the other "memo". I would say that this is just one of those quirks that an Access user/developer will need to be aware of. Which is to be discussed in another thread ... __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
--- Bruce Momjian <pgman@candle.pha.pa.us> wrote: > > Where can we document this information? I don't see > it on the techdocs > site. > Good question. The available stuff is piecemeal, and split between techdocs and the odbc site. Joel Burton's FAQ is the largest, but is incomplete and appears not to have been worked on much for the last couple of years. There is the makings of a good FAQ on the odbc site: perhaps that could be moved to techdocs, and expanded using reader contributions? I would be happy to put in some pieces, although I am sure that there are plenty whose knowledge is much greater than mine. Justin? > > --------------------------------------------------------------------------- > > Benjamin Scherrey wrote: > > 11/27/2002 10:46:51 AM, Jeff Eckermann > <jeff_eckermann@yahoo.com> wrote: > > > > >This raises an issue for Access developers which, > in > > >spite of searching archives, FAQs etc. I have not > seen > > >explicitly stated anywhere. > > > > > >I have found that, to be able to use both "text" > and > > >"memo" types within Access, linking to tables in > a > > >PostgreSQL database, the following must be done: > > >* table definitions in PostgreSQL must use > "varchar" > > >for fields desired to be "text" in Access, and > "text" > > >for fields desired to be "memo" in Access, and: > > >* "Text As LongVarChar" must be checked in the > DSN. > > > > This turns out to be what my problem is I think. > I do have the attribute defined as a 'text' > > type in my postgres table definition but when > linked to Access it still appears as 'text' in the > table > > definition. When I try to alter this setting to > memo in Access it complains that it "can't save > property > > changes for linked tables". How to I get Access to > recognize my Postgres 'text' attributes as memo > > fields? > > > > thanx & later, > > > > Ben Scherrey > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > -- > Bruce Momjian | > http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) > 359-1001 > + If your life is a hard drive, | 13 Roberts > Road > + Christ can be your backup. | Newtown > Square, Pennsylvania 19073 __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
11/27/2002 3:08:35 PM, Jeff Eckermann <jeff_eckermann@yahoo.com> wrote:
>
>--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
<snip>
>> I wonder whether we could/should hack our ODBC
>> driver to report "text"
>> columns as "varchar(n)" (and if so, what n should we
>> use)? Seems like
>> enough people want to use Access that this would be
>> a useful option.
Access only allows text FieldSize properties to be between 0 and 255 so this would be of
limited or no use.
>>
>> regards, tom lane
>
>The problem lies in the Access distinction between
>"text" type, which is fully functional but limited in
>size (the OP's problem), and "memo", which allows much
>more storage, but is limited in functionality: "memo"
>fields cannot be indexed, sorted, grouped by, joined
>against etc.
>
>The driver can presently render text as "text" or as
>"memo", depending on the chosen configuration. I
>don't see how it could reasonably distinguish between
>two text fields so as to call one "text" and the other
>"memo".
It can?!?!? I can't seem to get this to work then. Event with 'Text as LongVarChar' turned
off it still insists on treating my (formerlly) memo field as a text field with resulting limitations. I don't
want this data indexed or anything - just a plain old free form text memo field. I can't seem to figure
out what settings are necessary to get Access to recognize this as a memo field.
create table "Table-MCMProgram 2000" (
"ID" serial unique not null ,
"Date Opened" date null ,
"Name" varchar ( 50 ) not null ,
"Account#" integer not null ,
"Date of Birth" varchar ( 50 ) null ,
"Attending MD" varchar ( 50 ) null ,
"Health Plan Category" varchar ( 50 ) not null ,
"County" varchar ( 50 ) null ,
"Case Status" varchar ( 50 ) not null ,
"UR Notes" text null )
Access treats all the varchar and text fields as text in my ODBC link. I just need "UR
Notes" to act like a good old memo field once again so I can get more than 255 chars in it.
thanx & later,
Ben Scherrey
PS: I dislike the table/attribute names as well but it is outside of my scope of work to alter the
Access forms significantly so we're using the old names.
Jeff Eckermann <jeff_eckermann@yahoo.com> writes:
> The driver can presently render text as "text" or as
> "memo", depending on the chosen configuration. I
> don't see how it could reasonably distinguish between
> two text fields so as to call one "text" and the other
> "memo".
I was thinking of offering an option to have it render *all* text fields
as "varchar(n)", as a third alternative to the above two.
regards, tom lane
Jeff Eckermann wrote: > > --- Bruce Momjian <pgman@candle.pha.pa.us> wrote: > > > > Where can we document this information? I don't see > > it on the techdocs > > site. > > > > Good question. The available stuff is piecemeal, and > split between techdocs and the odbc site. Joel > Burton's FAQ is the largest, but is incomplete and > appears not to have been worked on much for the last > couple of years. There is the makings of a good FAQ > on the odbc site: perhaps that could be moved to > techdocs, and expanded using reader contributions? I > would be happy to put in some pieces, although I am > sure that there are plenty whose knowledge is much > greater than mine. Definitely sounds like a good idea. Anyone feel comfortable doing it in the new PostgreSQL Guides section of Techdocs? http://techdocs.postgresql.org/guides/ Some functionality is still being added to this area as time permits, but all the basics are in place. To create a new page, just go to that above URL but also add the name of the page. i.e.: http://techdocs.postgresql.org/guides/ODBCFaq As the page doesn't already exist, it will give you the option of creating it on the spot and editing it, etc. :-) Regards and best wishes, Justin Clift > Justin? -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
On Wed, Nov 27, 2002 at 12:15:57PM -0800, Jeff Eckermann wrote: > > --- Bruce Momjian <pgman@candle.pha.pa.us> wrote: > > > > Where can we document this information? I don't see > > it on the techdocs > > site. > > > > Good question. The available stuff is piecemeal, and > split between techdocs and the odbc site. Joel > Burton's FAQ is the largest, but is incomplete and > appears not to have been worked on much for the last > couple of years. There is the makings of a good FAQ > on the odbc site: perhaps that could be moved to > techdocs, and expanded using reader contributions? I > would be happy to put in some pieces, although I am > sure that there are plenty whose knowledge is much > greater than mine. Yep. I use Access very little nowadays (yipee!), so I don't really have much to add. I'd be happy to turn this over to anyone who wants to clean it up/add to it, etc. - J. -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant
--- Joel Burton <joel@joelburton.com> wrote: > Yep. I use Access very little nowadays (yipee!), so > I don't really have > much to add. I'd be happy to turn this over to > anyone who wants to clean > it up/add to it, etc. > (Crunching sound from biting of bullet) Ok Joel, will you donate your stuff to techdocs, assuming Justin is happy to host it? I would be willing to apply my limited knowledge to fleshing it out, and others could then easily do the same. __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
On Tue, Dec 03, 2002 at 11:03:59AM -0800, Jeff Eckermann wrote: > > --- Joel Burton <joel@joelburton.com> wrote: > > Yep. I use Access very little nowadays (yipee!), so > > I don't really have > > much to add. I'd be happy to turn this over to > > anyone who wants to clean > > it up/add to it, etc. > > > > (Crunching sound from biting of bullet) > Ok Joel, will you donate your stuff to techdocs, > assuming Justin is happy to host it? I would be > willing to apply my limited knowledge to fleshing it > out, and others could then easily do the same. Sounds good. May you enjoy it. You can simply download it from the joelburton.com/resources/pgaccess location. If I can explain anything in it (I wrote about 90% of it originally), let me know. - J. -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant