I know that the correct odbc usage (on windows) is with a "Application
Server" on only one machine, but now we only have the capability to use
direct connection.

Further more in the future we will implement a server application. Now I
have another question:

My clients are Fat32 and I don't meant to change all clients to NTFS so my
Security TAB doesn't appears (I consider it occurs becouse the Filesystem).

Did I correct ?

Thank you so much for the explanations.

Regards

2007/1/22, Ludek Finstrle :

Mon, Jan 22, 2007 at 12:48:17PM -0200, Ezequias Rodrigues da Rocha napsal(a):

Yes. You're right. All users are admins when you use Fat32. So you're
not able to store the password in the secure way on local machines.
You have to specify the password all the time or maybe some network
share from server should work?

Regards,

Luf

Hello,

I've read a post [1] about how MS Access is enjoyable and efficient when tables are connected to MS SQL.

But I have MS Access front end containing linked tables to Postgres via ODBC. And I'm struggling with slow performance. When I want to open a table which contains about 8000 records it takes more then 20 sec. When I want to move cursor at the last record it takes more than 60 extra seconds.
I'm not able to bind comboboxes directly to larger (more than 60 records) linked table because unrolling takes 20 sec.
All tables has defined primary key, of course.
I was trying to change indexes without any result.

I've already checked Postgres server log and MS Access queries are executed quickly (<500 ms).

Is this normal behavior? Does MS Access cooperates with MS SQL such better than with other DBMS via odbc?

Does anybody have better experience?

I was trying to communicate from ASP.NET with Postgres via OLE DB driver and it was without any performance problems.

Thank you

Jirka

[1]
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=53&Number=1321886&Zf=&Zw...=#Post1321886&Zp=

Jiří Nouza wrote:

Aren't you turning on ODBC trace ?

regards,
Hiroshi Inoue

I see no description of your configuration.
What's your ODBC settings? What ODBC driver do you use? What OS?
I hope you haven't enabled the mylog output.
How long takes it the psql to show 8000 records?

BTW let's try play with "use declare/fetch" and "cache size" at first.
And mylog output could help you to see where's the problem.

Regards,

Luf

pgsql-odbc-owner@postgresql.org wrote:

Philippe Lang a écrit :

I also use Access as a FrontEnd to PostgreSQL (we are migrating the backend).
The main problem is that all queries have to be written in PostgreSQL.
When joining linked tables in Access, performance is terrible ! I think I understand why, but some of my power-users would like to be able to create queries in Access and have no knowledge of SQL (so creating them in PostgreSQL is not an option).

Good to know !
I might give MSAccess2003 a try then !

Oh, I'm still feeling like a BFU :(

I'm using the last one version of odbc driver (08_02_0200) on Windows XP, 2000. Access is 2000 and 2003.

Of course I had commlog and mylog turned on. But I've never thought that writing some kb (ok, few megabytes) of text could have such big affect :(
When I was trying common windows odbc trace the disk worked very hard but postgres log didn't make full use of hard disk.

It seems that turning off helps very well. Access work by now commonly fast.

8000 rows query is prosessed in 0.8 s, see log:
2007-01-23 00:01:42 odbcuser SELECT LOG: 00000: duration: 813.000 ms statement: select * from osoby
It could be adequate I suppose. Postgres is on laptop.

I will try to change "use declare/fetch" and "cache size" but it is hard to debug via mylog because it is unbelievable slow.

And try to convert Access file to 2003 version further.

Thank you very much I haven't to forget to check logging settings.

Jirka

Tue, Jan 23, 2007 at 11:21:58AM +0100, Jiří Nouza napsal(a):

mylog is very detailed log (all SQL statement all getted values, ...)
and it's called very often in the code.
psqlodbc.log should be ok (there are few informations).

You don't need it if it's ok now for you. Use declare/fetch convert
SELECT statements to cursor so the result is fetched in "cache size"
blocks. Sometime it helps sometime it kills the performance.

Regards,

Luf