Thread: Re: [GENERAL] Access 'field too long' error
I have succesfully linked the following table and view
in both MS Access 97 and 2000.
create table num_tbl(pk serial,n numeric(7,2),primary key (pk));
create view num_vw1 sa select * from num_tbl;
I have had problems wiht indexes on 97 when used on fields it
couldn't have indexed.
If you post your table & view definition (as well as Access ver)
I'll see if it works for me or not.
- Stuart
> -----Original Message-----
> From: Randall Perry [mailto:rgp@systame.com]
> Sent: 29 August 2002 18:31
> To: pgsql-general@postgresql.org
> Cc: pgsql-admin@postgresql.org
> Subject: [GENERAL] Access 'field too long' error
>
>
> Getting the error:
> The size of a field is too long
>
> From access when trying to link to a view. Discovered the
> problem field is
> of type numeric(7,2). But, if I try linking to the table the
> view is based
> on, I don't get the error. So, somehow this numeric field in
> a view looks
> different to Access than the same numeric field in a table.
>
> Any ideas?
>
>
> --
> Randy Perry
> sysTame
> Mac Consulting/Sales
>
> http://www.systame.com/
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Here's my view definition:
View "v_cust_rev_by_month"
Column | Type | Modifiers
---------------+-----------------------+-----------
id | integer |
cust_code | character varying(25) |
date | date |
month | text |
total_uploads | integer |
revenues | numeric |
balance | numeric |
In testing, if I create a new table from this view and don't include the
numeric fields, it links to access ok.
If include either of the numeric fields I get the 'field is too long' error.
If I link the original table the view is derived from (the view is 3 steps
removed from the original because it calls on views that call on views) I
don't get the error.
Here's the original table def. The revenues and balance fields above are
derived from the price and amount fields.
Column | Type | Modifiers
------------+------------------------+--------------------------------------
--------------
id | integer | not null default
nextval('"invoice_id_seq"'::text)
cust_code | character varying(25) | not null
inv_no | character varying(15) | not null
date | date | not null
due | date | not null
no_of_dist | integer | not null
quantity | numeric(7,2) | not null
descript | character varying(300) | not null
price | numeric(7,2) | not null
amount | numeric(7,2) | not null
x_period | integer | not null
x_no | integer | not null
I can use ADO to grab the view data, but I need the recordset for a report
-- and you can't set a report's recordset to an ADO source. Bummer.
> I have succesfully linked the following table and view
> in both MS Access 97 and 2000.
> create table num_tbl(pk serial,n numeric(7,2),primary key (pk));
> create view num_vw1 sa select * from num_tbl;
> I have had problems wiht indexes on 97 when used on fields it
> couldn't have indexed.
> If you post your table & view definition (as well as Access ver)
> I'll see if it works for me or not.
> - Stuart
>
>> -----Original Message-----
>> From: Randall Perry [mailto:rgp@systame.com]
>> Sent: 29 August 2002 18:31
>> To: pgsql-general@postgresql.org
>> Cc: pgsql-admin@postgresql.org
>> Subject: [GENERAL] Access 'field too long' error
>>
>>
>> Getting the error:
>> The size of a field is too long
>>
>> From access when trying to link to a view. Discovered the
>> problem field is
>> of type numeric(7,2). But, if I try linking to the table the
>> view is based
>> on, I don't get the error. So, somehow this numeric field in
>> a view looks
>> different to Access than the same numeric field in a table.
>>
>> Any ideas?
>>
>>
>> --
>> Randy Perry
>> sysTame
>> Mac Consulting/Sales
>>
>> http://www.systame.com/
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>
--
Randy Perry
sysTame
Mac Consulting/Sales
http://www.systame.com/
Randall Perry <rgp@systame.com> writes:
> Here's my view definition:
> View "v_cust_rev_by_month"
> Column | Type | Modifiers
> ---------------+-----------------------+-----------
> id | integer |
> cust_code | character varying(25) |
> date | date |
> month | text |
> total_uploads | integer |
> revenues | numeric |
> balance | numeric |
> In testing, if I create a new table from this view and don't include the
> numeric fields, it links to access ok.
> If include either of the numeric fields I get the 'field is too long' error.
> If I link the original table the view is derived from (the view is 3 steps
> removed from the original because it calls on views that call on views) I
> don't get the error.
My bet is that something on the client side is getting confused by the
lack of precision specification for the numeric columns in the view. It
works on the original table because that has a precision spec.
You can probably work around this by redefining the view with explicit
casts. Note the difference in the following examples:
regression=# create table foo(f1 numeric(7,2));
CREATE
regression=# create view v as select f1, f1+1 from foo;
CREATE
regression=# create view v2 as select f1, (f1+1)::numeric(7,2) from foo;
CREATE
regression=# \d v
View "v"
Column | Type | Modifiers
----------+--------------+-----------
f1 | numeric(7,2) |
?column? | numeric |
View definition: SELECT foo.f1, (foo.f1 + '1'::"numeric") FROM foo;
regression=# \d v2
View "v2"
Column | Type | Modifiers
---------+--------------+-----------
f1 | numeric(7,2) |
numeric | numeric(7,2) |
View definition: SELECT foo.f1, ((foo.f1 + '1'::"numeric"))::numeric(7,2) AS "numeric" FROM foo;
In the longer run it might be possible to tweak the ODBC driver to
prevent this failure --- I'm not sure what ODBC does when it sees a
-1 typmod for a numeric column, but perhaps it could do something
different than it does now.
regards, tom lane
Just tried a SQL Pass Thru query and got the same 'field too long' error. >> >>> -----Original Message----- >>> From: Randall Perry [mailto:rgp@systame.com] >>> Sent: 29 August 2002 18:31 >>> To: pgsql-general@postgresql.org >>> Cc: pgsql-admin@postgresql.org >>> Subject: [GENERAL] Access 'field too long' error >>> >>> >>> Getting the error: >>> The size of a field is too long >>> >>> From access when trying to link to a view. Discovered the >>> problem field is >>> of type numeric(7,2). But, if I try linking to the table the >>> view is based >>> on, I don't get the error. So, somehow this numeric field in >>> a view looks >>> different to Access than the same numeric field in a table. >>> >>> Any ideas? >>> >>> >>> -- >>> Randy Perry >>> sysTame >>> Mac Consulting/Sales >>> >>> http://www.systame.com/ >>> >>> >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 6: Have you searched our list archives? >>> >>> http://archives.postgresql.org >>> >> > -- Randy Perry sysTame Mac Consulting/Sales http://www.systame.com/