Re: MS Access and #deleted due to timeouts

Further research shows that I there is definitely a timeout issue. The
issue seems to be with Access and its technique for implementing Dynaset
by having keysets.

Short solution:
Use regedit to set
\\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\JET\4.0\Engines\ODBC
\\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\JET\3.5\Engines\ODBC
key ConnectionTimeout to value of 0. (default is 600 decimal = 10 minutes)

A value of 0 means that connections will never timeout for being idle.

Long answer:
For chuckles..you can try set ConnectionTimeout to 120 (2 minutes)or
less and see how frustrating it is when #delete shows up after brief
inactivity.

My best understanding is that in most datasheet and form views, Access
want to establish a keyset. So it does a fetch pass to just get the keys
values... and then another fetch pass to get the values. The key values
allow it to pull individual records and act as a dynaset, that is issue
UPDATE commands behind the scenes for individual records as mere mortals
scroll up and down while editing a datasheet.

Well after the connection hits timeout or inactivity, the first passes
still work (to get the keys -- the number of records), but somehow the
second fetch passes (to get the actual data) return SQL_NO_DATA_FOUND.
All keys and no data equals a screen full of #deleted, but with the
correct number of records.

If the connection has timed out why don't both fetch passes fail, or
connection(s) for both passes get re-established? I do not know. I am
pretty sure it is a matter of ODBC calls Access is passing to the pgodbc
driver, and so somewhat beyond our control.

I am guessing that setting ConnectionTimeout to 0 should be standard
fare for workstations with Access linked to PostgreSQL.

Greg Campbell wrote:

> I have distinctly noticed that when I leave my Access database with
> PostgreSQL linked tables for any considerable length of time, all fields
> in ALL linked tables say #deleted in data table view (and I'd assume
> form view as well).
>
>
> Notes:
> I have OIDS and Primary keys on the tables. Access is aware of this. And
> I have row versioning turned on.
>
> Troubleshooting:
> I turned on the ODBC trace.
> What I see is a simple select query does a SQLExecDirectW (e.g. "SELECT
> "public"."employees"."first_name" from "public"."employees" /0").
> Then it does an SQLFetch and SQLGetData for each row. (No obvious data
> returned).
>
> Then it does a SQLPrepareW that look like
> "SELECT "public"."employees"."first_name" FROM "public"."employees"
> WHERE "emp_id"=? or "emp_id"=? or "emp_id"=? or "emp_id"=? or
> "emp_id"=? or "emp_id"=? or "emp_id"=? or "emp_id"=? or "emp_id"=? or
> "emp_id"=? \0"
>
> Then it does a SQLBindParamter for each of the 10 instance of emp_id.
> Why it enumerates the same paramter 10 times is a mystery to me.
>
> Then it does a fetch and three (3) SQLGetDatas for each row.
> I don't know what the first ones doing. The 2nd returns data,data_type,
> data_length. The 3rd,...well I'm not sure what that one's doing.
>
> In data view I see my data.
>
> Then I let 20 or 30 minutes pass.
>
> Same as above, except after binding parameters, my first fetch returns a
> code 100 (SQL_NO_DATA_FOUND).
> In the data view I see a #deleted for each record.
>
> Here are my question?
> 1. Are two passes of fetches absolutely necessary each time I run my query?
> 2. What's up with all the parameters to my primary key.
> 3. I noticed that my connection handle (HSTMT) was the same before and
> after my 30 minute time-out. Could the Postgresql server have timed out
> my connection while my ODBC driver still thinks it is a viable
> connection, and tries to re-use it?
> 4. What the best way to handle this open connection pooling? My general
> practice has been that very short lived connections are good (say a
> minute or so), but I am not sure if that applies with a thick client
> like Access.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org