Access97/odbcUnicode/pgsql : unable to map text field to

am 08.06.2006 11:45:13 von igxnews

Hello,

I use odbc unicode driver to link tables from my pgsql 8.1.3 server to Access97
SR2 (and latest jet engine 4.0 SP 8).

Everything is working well, even inserting row work without #deleted on update
(like with the ansi driver despite of serial PK on tables :s)

Actualy, one thing doesn't work properly. It's a well know issue, but I didn't
find how to correct it (_and_I_read_lots_of_webpages_about_that_!_)

TEXT type in my pgsql database aren't map to memo type in Access. I don't mind
sorting, index or order on these fields, but I really need more than 255 chars
on them.

And, yes, checked the "text as LongVarChar" in the ODBC configuration.

Here the table I use for my tests :

CREATE TABLE test
(
id serial NOT NULL,
txt text NOT NULL DEFAULT ''::text,
CONSTRAINT test_pkey PRIMARY KEY (id)
)
WITH OIDS;

I think you will find the complete ODBC configuration in the HUGE logs here :
http://ioguix.free.fr/pgsqllog.log

Please....help... (or point me in the right direction)

Thanks
--
+-----------------+
| IoGuiX |
| igxnews@free.fr |
+-----------------+

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Access97/odbcUnicode/pgsql : unable to map text field to memo type

am 08.06.2006 12:06:30 von Ludek Finstrle

> I use odbc unicode driver to link tables from my pgsql 8.1.3 server
> to Access97 SR2 (and latest jet engine 4.0 SP 8).
>
> Everything is working well, even inserting row work without #deleted
> on update (like with the ansi driver despite of serial PK on tables :s)

You don't specify psqlodbc version. Does psqlodbc 08.02.0002 with
the latest dll replaced from:
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
work better?

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 08.06.2006 13:07:12 von igxnews

Hello,

Well, sorry, I forgot the odbc version, but I tried so many of them.
Yes I used
the 08.02.0002.

I just tried with the dll from inocchichichi (version as 7.3.2.75) but
it still
doesn't work...

Here the logs :
- http://ioguix.free.fr/mylog.log
- http://ioguix.free.fr/psqlodbc.log

Thanks, it's good to have some support...It's so long I am looking for some
workaround about bugs with odbc unicode or Access (w/ odbc ansi)...

Regards,
--
JGuillaume 'IoGuiX' de Rorthais

Quoting Ludek Finstrle :

>> I use odbc unicode driver to link tables from my pgsql 8.1.3 server
>> to Access97 SR2 (and latest jet engine 4.0 SP 8).
>>
>> Everything is working well, even inserting row work without #deleted
>> on update (like with the ansi driver despite of serial PK on tables :s)
>
> You don't specify psqlodbc version. Does psqlodbc 08.02.0002 with
> the latest dll replaced from:
> http://www.geocities.jp/inocchichichi/psqlodbc/index.html
> work better?
>
> Regards,
>
> Luf
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 09.06.2006 13:25:19 von Ludek Finstrle

> Thanks, it's good to have some support...It's so long I am looking for some
> workaround about bugs with odbc unicode or Access (w/ odbc ansi)...

I don't know what Access needs to recognize the data as MEMO. Has someone
an idea?
I have no Access installed on my computer. Please could you send
me sample .exe and data which reproduces the problem?

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 09.06.2006 14:53:17 von igxnews

Mh, looks impossible to join just a .exe without a part of Office pack :/
I just have my logs for you...

Anyway, indeed, mapping PgSQL Text type to Access Memo work fine with
the ANSI's
ODBC versions. But using the ANSI version require to much macro & VBA stuff to
workaround the #Deleted behaviour (that is an Access specific problem).

So, I resume :
- with Ansi version, I have to play with VBA & macro code because of
Access BUT
Text -> Memo type work fine ;
- with unicode version, Text -> Memo type is broken BUT I havn't the #Deleted
problem...

I put my log from ansi driver by inocchichichi here :
http://ioguix.free.fr/psqlodbc_ansi.log
http://ioguix.free.fr/mylog_ansi.log

Thanks for your help !

--
JGuillaume 'IoGuiX' de Rorthais

Quoting Ludek Finstrle :

>> Thanks, it's good to have some support...It's so long I am looking for some
>> workaround about bugs with odbc unicode or Access (w/ odbc ansi)...
>
> I don't know what Access needs to recognize the data as MEMO. Has someone
> an idea?
> I have no Access installed on my computer. Please could you send
> me sample .exe and data which reproduces the problem?
>
> Regards,
>
> Luf
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 09.06.2006 15:50:30 von Ludek Finstrle

> Mh, looks impossible to join just a .exe without a part of Office pack :/

It's a pity.

> I just have my logs for you...
>
> Anyway, indeed, mapping PgSQL Text type to Access Memo work fine with
> the ANSI's
> ODBC versions. But using the ANSI version require to much macro & VBA stuff
> to
> workaround the #Deleted behaviour (that is an Access specific problem).

The only difference I see is in type of 'txt' column.
ANSI: SQL_LONGVARCHAR (-1)
Unicode: SQL_WLONGVARCHAR (-10)

Let's try change Max Longvarchar size from 8190 to something higher
(at least 16380). If it doesn't help try change the Max Longvarchar
size to -4 (SQL_NO_TOTAL).

Maybe it helps or maybe it doesn't.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 09.06.2006 17:37:26 von igxnews

....and it doesn't help...I tried with 20475 and -4 in the "Max Longvarchar"
field. Text field (txt in my table) is still mapped to the Jet text type
insteed of Memo.

But mystery is going deeper...
I use Jet 4.0 on my winXP SP2. I followed the instructions describes in
the last
paragraph here :
http://support.microsoft.com/?scid=kb%3Ben-us%3B237994&x=19& y=12

....And we can read at this link :
http://support.microsoft.com/?scid=kb%3Ben-us%3B214854&x=16& y=11 that :
ODBC SQL Type:SQL_WLONGVARCHAR
Precision:N/A
Scale:N/A
Jet 3.5 Type:Unsupported
Jet 40 Type:Memo

....I still trying to find some more info about that.

Many thanks for your support.

Regards,

--
IoGuiX

Quoting Ludek Finstrle :

>> Mh, looks impossible to join just a .exe without a part of Office pack :/
>
> It's a pity.
>
>> I just have my logs for you...
>>
>> Anyway, indeed, mapping PgSQL Text type to Access Memo work fine with
>> the ANSI's
>> ODBC versions. But using the ANSI version require to much macro & VBA stuff
>> to
>> workaround the #Deleted behaviour (that is an Access specific problem).
>
> The only difference I see is in type of 'txt' column.
> ANSI: SQL_LONGVARCHAR (-1)
> Unicode: SQL_WLONGVARCHAR (-10)
>
> Let's try change Max Longvarchar size from 8190 to something higher
> (at least 16380). If it doesn't help try change the Max Longvarchar
> size to -4 (SQL_NO_TOTAL).
>
> Maybe it helps or maybe it doesn't.
>
> Regards,
>
> Luf
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 09.06.2006 17:44:29 von bronto

This is a multi-part message in MIME format.

------=_NextPart_000_00B7_01C68BF4.C29D5610
Content-Type: text/plain;
charset="windows-1250"
Content-Transfer-Encoding: 7bit

(Tested)Data type matching:
-----------------------------------------------
| ACCESS | POSTGRESQL |
-----------------------------------------------
| Text | Varchar(255) |
| Memo | Varchar(65535) |
| Number: Integer | Int 2 |
| Long Integer | Int 4 |
| Single | Float 4 |
| Double | Float 8 |
| Date/Time | TimeStamp |
| AutoNumber: Long | Serial |
| Yes/No (Boolean) | Int 2 |
| OLE Object | ByteA |
-----------------------------------------------

ODBC Driver Settings:

--------
|Page 1|
--------

- Check "Disable generic optimizer"
- Check "KSQO..."
- Check "Recognize unique indexes"
- Set "Unknown sizes" to "Maximum"
- Check "Text as LongVarChar"
- Set "Max Varchar" to 255

--------
|Page 2|
--------

- Check "LF <-> CR/LF conversion"
- Check "bytea as LO"
- Check "True is -1"
- Set "Int8 As" to "double"

Make sure you use the ANSI driver

Hope it helps.

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.3/359 - Release Date: 08.06.2006

------=_NextPart_000_00B7_01C68BF4.C29D5610
Content-Type: text/html;
charset="windows-1250"
Content-Transfer-Encoding: quoted-printable

charset=3Dwindows-1250">
6.5.7036.0">
RE: [ODBC] Access97/odbcUnicode/pgsql : unable to map text =<br /> field

New">ODBC Driver Settings:

New">--------

FACE=3D"Courier New">|Page 1|

FACE=3D"Courier New">--------

New">- Check "Disable generic optimizer"

FACE=3D"Courier New">- Check "KSQO..."

FACE=3D"Courier New">- Check "Recognize unique =
indexes"

FACE=3D"Courier New">- Set "Unknown sizes" to =
"Maximum"

FACE=3D"Courier New">- Check "Text as =
LongVarChar"

FACE=3D"Courier New">- Set "Max Varchar" to 255

New">--------

FACE=3D"Courier New">|Page 2|

FACE=3D"Courier New">--------

New">- Check "LF <-> CR/LF conversion"

FACE=3D"Courier New">- Check "bytea as LO"

FACE=3D"Courier New">- Check "True is -1"

FACE=3D"Courier New">- Set "Int8 As" to =
"double"

New">Make sure you use the ANSI driver

New">Hope it helps.

--

No virus found in this outgoing message.

Checked by AVG Free Edition.

Version: 7.1.394 / Virus Database: 268.8.3/359 - Release Date: =
08.06.2006

------=_NextPart_000_00B7_01C68BF4.C29D5610--

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 09.06.2006 17:53:07 von Ludek Finstrle

> But mystery is going deeper...
> I use Jet 4.0 on my winXP SP2. I followed the instructions describes in
> the last

What about MS Access 2000? Isn't problem in Access 97 itself?
BTW It seems I'll have access to computer with MS Access installed.
I have no experience with it. Could you post me your test app?

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 09.06.2006 18:31:23 von igxnews

The more I find/read info, the more I thnk it's yet another Access issue...

Moreover, Greg Campbell in his answer confirm that.

Unfortunately, I am in a mixed network with win98/win2k and winXP.
I don't know if Office2000 could be installed on win98, but if not, I'll gonna
have a big problem to migrate the Access database to Postgres :s

Thanks to both of you for your help, I'll try monday with Office2000
and report
here what's append.

cheers,
--
JGuillaume 'IoGuiX' de Rorthais

Quoting Ludek Finstrle :

>> But mystery is going deeper...
>> I use Jet 4.0 on my winXP SP2. I followed the instructions describes in
>> the last
>
> What about MS Access 2000? Isn't problem in Access 97 itself?
> BTW It seems I'll have access to computer with MS Access installed.
> I have no experience with it. Could you post me your test app?
>
> Regards,
>
> Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 09.06.2006 18:39:29 von Ben.Trewern

We've used Access 2000 on Win98 without to many problems.

Regards,

Ben=20

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org=20
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of=20
> JGuillaume 'IoGuiX' de Rorthais
> Sent: 09 June 2006 17:31
> To: Ludek Finstrle
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Access97/odbcUnicode/pgsql : unable to=20
> map text field
>=20
>=20
> The more I find/read info, the more I thnk it's yet another=20
> Access issue...
>=20
> Moreover, Greg Campbell in his answer confirm that.
>=20
> Unfortunately, I am in a mixed network with win98/win2k and=20
> winXP. I don't know if Office2000 could be installed on=20
> win98, but if not, I'll gonna have a big problem to migrate=20
> the Access database to Postgres :s
>=20
> Thanks to both of you for your help, I'll try monday with Office2000=20
> and report
> here what's append.
>=20
> cheers,
> --=20
> JGuillaume 'IoGuiX' de Rorthais
>=20
>=20
> Quoting Ludek Finstrle :
>=20
> >> But mystery is going deeper...
> >> I use Jet 4.0 on my winXP SP2. I followed the instructions=20
> describes=20
> >> in the last
> >
> > What about MS Access 2000? Isn't problem in Access 97=20
> itself? BTW It=20
> > seems I'll have access to computer with MS Access=20
> installed. I have no=20
> > experience with it. Could you post me your test app?
> >
> > Regards,
> >
> > Luf
> >
> > ---------------------------(end of=20
> > broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>=20
>=20
>=20
> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>=20
> http://archives.postgresql.org
>=20

------------------------------------------------------------ -------
This email and any attachments transmitted with it are confidential (and po=
tentially legally privileged) and intended solely for the use of the indivi=
dual or entity to whom they are addressed. If you have received this email =
in error please notify the sender and do not store, copy or disclose the co=
ntent to any other person.
It is the responsibility of the recipient to ensure that opening this messa=
ge and/or any of its attachments will not adversely affect its systems. No =
responsibility is accepted by the sender.
------------------------------------------------------------ -------

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 14.06.2006 14:05:21 von Ludek Finstrle

> Unfortunately, I am in a mixed network with win98/win2k and winXP.
> I don't know if Office2000 could be installed on win98, but if not, I'll
> gonna
> have a big problem to migrate the Access database to Postgres :s
>
> Thanks to both of you for your help, I'll try monday with Office2000
> and report
> here what's append.

Any progress?
BTW there is ANSI psqlodbc driver on Hiroshi page:
http://www.geocities.jp/inocchichichi/psqlodbc/index.html

Luf

> Quoting Ludek Finstrle :
>
> >>But mystery is going deeper...
> >>I use Jet 4.0 on my winXP SP2. I followed the instructions describes in
> >>the last
> >
> >What about MS Access 2000? Isn't problem in Access 97 itself?
> >BTW It seems I'll have access to computer with MS Access installed.
> >I have no experience with it. Could you post me your test app?

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 14.06.2006 15:31:10 von igxnews

Yes, I had problems...

I just had an Office2k CD today and I hadn't lot of time to make tests.

Presently, I just make a clean environnement : removed everything,
installed the psqlodbc 8.02.02 version without Hiroshi customed dll.

My test application work fine for text to memo mapping with the unicode
driver, but don't work anymore about the #deleted behaviour of Access
without vba stuffs :/

Maybe memo type arn't used by Access to check the record ?

I hope I will find some more time tomorrow to complete my tests.

Postgresql and more generaly database world really miss more
applications like Access...
Is anyone knows an application with at least easy an form design feature =
?
Do you know some project I could join to give help (If I have the skills)=
?
Does anyone would be interrested by such a project ?

Thanks.

--=20
+-----------------+
| IoGuiX |
| igxnews@free.fr |
+-----------------+

-- Schmitter, Fr=E9d=E9ric

Ludek Finstrle wrote:
>>Unfortunately, I am in a mixed network with win98/win2k and winXP.
>>I don't know if Office2000 could be installed on win98, but if not, I'l=
l=20
>>gonna
>>have a big problem to migrate the Access database to Postgres :s
>>
>>Thanks to both of you for your help, I'll try monday with Office2000=20
>>and report
>>here what's append.
>=20
>=20
> Any progress?
> BTW there is ANSI psqlodbc driver on Hiroshi page:
> http://www.geocities.jp/inocchichichi/psqlodbc/index.html
>=20
> Luf
>=20
>=20
>>Quoting Ludek Finstrle :
>>
>>
>>>>But mystery is going deeper...
>>>>I use Jet 4.0 on my winXP SP2. I followed the instructions describes =
in
>>>>the last
>>>
>>>What about MS Access 2000? Isn't problem in Access 97 itself?
>>>BTW It seems I'll have access to computer with MS Access installed.
>>>I have no experience with it. Could you post me your test app?

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 14.06.2006 15:45:38 von Ben Trewern

When I know I'm going to use MS Access as a front end to a Postgresql
database I like to keep to the following guidelines:

- Use a serial number as my primary key on each table.
- Any text fields I need to search or index use varchar(<255)
- Turn "Row Versioning" and "Text as LongVarChar" on in the dataset options.
(I also use "Bools as Char" but I'm not sure if I need to).

Hope this helps.

Ben

"ioguix" wrote in message news:44900F9E.3080402@free.fr...
> Yes, I had problems...
>
> I just had an Office2k CD today and I hadn't lot of time to make tests.
>
> Presently, I just make a clean environnement : removed everything,
> installed the psqlodbc 8.02.02 version without Hiroshi customed dll.
>
> My test application work fine for text to memo mapping with the unicode
> driver, but don't work anymore about the #deleted behaviour of Access
> without vba stuffs :/
>
> Maybe memo type arn't used by Access to check the record ?
>
> I hope I will find some more time tomorrow to complete my tests.
>
> Postgresql and more generaly database world really miss more
> applications like Access...
> Is anyone knows an application with at least easy an form design feature ?
> Do you know some project I could join to give help (If I have the skills)
> ?
> Does anyone would be interrested by such a project ?
>
> Thanks.
>
> --
> +-----------------+
> | IoGuiX |
> | igxnews@free.fr |
> +-----------------+
>
> -- Schmitter, Frédéric
>
>
> Ludek Finstrle wrote:
>>>Unfortunately, I am in a mixed network with win98/win2k and winXP.
>>>I don't know if Office2000 could be installed on win98, but if not, I'll
>>>gonna
>>>have a big problem to migrate the Access database to Postgres :s
>>>
>>>Thanks to both of you for your help, I'll try monday with Office2000
>>>and report
>>>here what's append.
>>
>>
>> Any progress?
>> BTW there is ANSI psqlodbc driver on Hiroshi page:
>> http://www.geocities.jp/inocchichichi/psqlodbc/index.html
>>
>> Luf
>>
>>
>>>Quoting Ludek Finstrle :
>>>
>>>
>>>>>But mystery is going deeper...
>>>>>I use Jet 4.0 on my winXP SP2. I followed the instructions describes in
>>>>>the last
>>>>
>>>>What about MS Access 2000? Isn't problem in Access 97 itself?
>>>>BTW It seems I'll have access to computer with MS Access installed.
>>>>I have no experience with it. Could you post me your test app?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Access97/odbcUnicode/pgsql : unable to map text field

am 16.06.2006 21:30:49 von Antoine

> Postgresql and more generaly database world really miss more
> applications like Access...
> Is anyone knows an application with at least easy an form design feature ?
> Do you know some project I could join to give help (If I have the skills)=
?
> Does anyone would be interrested by such a project ?

kexi, openoffice, pfm (?)...
There are lots. Google est ton ami :-).
Cheers
Antoine
ps. Access n'est pas une vraie base de donn=E9es !

--=20
This is where I should put some witty comment.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster