Обсуждение: Access97/odbcUnicode/pgsql : unable to map text field to memo type
Access97/odbcUnicode/pgsql : unable to map text field to memo type
От
JGuillaume 'IoGuiX' de Rorthais
Дата:
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 | +-----------------+
> 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
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 <luf@pzkagis.cz>: >> 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 >
> 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
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 <luf@pzkagis.cz>: >> 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 >
> 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
...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 <luf@pzkagis.cz>: >> 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 >
> 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
(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
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 <luf@pzkagis.cz>: >> 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 >
> 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 <luf@pzkagis.cz>: > > >>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?
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 <luf@pzkagis.cz>:
>>
>>
>>>>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?
> 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ées ! -- This is where I should put some witty comment.
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" <igxnews@free.fr> 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 <luf@pzkagis.cz>: >>> >>> >>>>>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 >