Board index » delphi » Gigantic field size in Union SQL (Access)

Marlon Brum

Delphi Developer
Thu, 11 Nov 2004 09:10:22 GMT

Gigantic field size in Union SQL (Access)

I 'm using Access2000 with ADO. That's the situation :
I have two tables wich I'm joining in a sql using UNION.
Like :
select id, name from Table1
union
select id, name from Table2.

The field "name" is a varchar. Even if its size is 20 in
both tables, in the result set from this sql its size is
about 1500! Does anyone know how can i set the
size in the result set to a specific type(varchar(20).

Thanks,

Marlon medeiros Brum

 
Sunil Furtad

Delphi Developer
Thu, 11 Nov 2004 16:16:02 GMT

Re:Gigantic field size in Union SQL (Access)


As a union is not updatable and can only be used for
display, you could create field definitions for the dataset
and set  the size of the name field to 20.

Sunil

Quote
"Marlon Brum" <emaildomar...@yahoo.com.br> wrote:
>I 'm using Access2000 with ADO. That's the situation :
>I have two tables wich I'm joining in a sql using UNION.
>Like :
>select id, name from Table1
>union
>select id, name from Table2.

>The field "name" is a varchar. Even if its size is 20 in
>both tables, in the result set from this sql its size is
>about 1500! Does anyone know how can i set the
>size in the result set to a specific type(varchar(20).

>Thanks,

>Marlon medeiros Brum

Marlon Bru

Delphi Developer
Thu, 11 Nov 2004 23:30:02 GMT

Re:Gigantic field size in Union SQL (Access)


The problem is that this view will be used in another
view, and this field will be the "Foreign Key", but
when I use

Select * from Table1
Left outer join myView on (table1.regnumber = myView.RegNumber)

It gives an error. It can't make a relationship between a
 10 caracter field and a 1500 one.

"Sunil Furtado" <su...@furtado.de> escreveu na mensagem
news:3cf099c2$1_2@dnews...

Quote

> As a union is not updatable and can only be used for
> display, you could create field definitions for the dataset
> and set  the size of the name field to 20.

> Sunil

> "Marlon Brum" <emaildomar...@yahoo.com.br> wrote:
> >I 'm using Access2000 with ADO. That's the situation :
> >I have two tables wich I'm joining in a sql using UNION.
> >Like :
> >select id, name from Table1
> >union
> >select id, name from Table2.

> >The field "name" is a varchar. Even if its size is 20 in
> >both tables, in the result set from this sql its size is
> >about 1500! Does anyone know how can i set the
> >size in the result set to a specific type(varchar(20).

> >Thanks,

> >Marlon medeiros Brum

Sunil Furtad

Delphi Developer
Fri, 12 Nov 2004 11:02:41 GMT

Re:Gigantic field size in Union SQL (Access)


     Can you post the fields of both the tables used
in the union, the union query statement and the join sql
statement.

     From what you have given so far, it looks like the
join field is the regnumber which is not the field with the
problem(name).

     I created a relationship between the name field of the
original table and the name field of the union query without
any problem. The size if I am not mistaken is an ADO issue
and not an Access issue.

Sunil

Quote
"Marlon Brum" <emaildomar...@yahoo.com.br> wrote:
>The problem is that this view will be used in another
>view, and this field will be the "Foreign Key", but
>when I use

>Select * from Table1
>Left outer join myView on (table1.regnumber = myView.RegNumber)

>It gives an error. It can't make a relationship between a
> 10 caracter field and a 1500 one.

Other Threads

1. Union SQL Statement Generates Access Violation

2. SQL Union access violation...

3. SQL Union access violation

4. SQL: UNION with local SQL?

5. SQL Union clause (Local SQL)

6. sort a union, view of union

7. What is difference between UNION and UNION ALL

8. TTABLE.CREATETABLE duplicates fields size in ACCESS 2000 databases

9. field size mismatch ODBC Access

10. Problems with Field size and Access 97