Thread: MS Access and #deleted due to timeouts

MS Access and #deleted due to timeouts

From
"Greg Campbell"
Date:
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.





Attachment

Re: MS Access and #deleted due to timeouts

From
"Zlatko Matic"
Date:
Hi!

There is an article on MSDN about #deleted (see below), maybe it will help
you to better understand the problem.

I experienced a lot of pain with my tables caused by #deleted, but I have
solved it by simple adding numeric bigserial field as primary keys in all my
tables.
There is no #deleted anymore.
http://support.microsoft.com/default.aspx?scid=kb;en-us;128809
ACC: "#Deleted" Errors with Linked ODBC Tables
View products that this article applies to.
      Article ID : 128809
      Last Review : May 6, 2003
      Revision : 1.0

This article was previously published under Q128809
On This Page
     SYMPTOMS
     CAUSE
     RESOLUTION
     MORE INFORMATION
         Steps to Reproduce Behavior
     APPLIES TO

SYMPTOMS
When you retrieve, insert, or update records in a linked ODBC table, each
field in a record contains the "#Deleted" error message. When you retrieve,
insert, or update records using code, you receive the error message "Record
is deleted."
     Back to the top

CAUSE
The Microsoft Jet database engine is designed around a keyset-driven model.
This means that data is retrieved, inserted, and updated based on key values
(in the case of a linked ODBC table, the unique index of a table).

After Microsoft Access performs an insert or an update of a linked ODBC
table, it uses a Where criteria to select the record again to verify the
insert or update. The Where criteria is based on the unique index. Although
numerous factors can cause the select not to return any records, most often
the cause is that the key value Microsoft Access has cached is not the same
as the actual key value on the ODBC table. Other possible causes are as
follows:
      . Having an update or insert trigger on the table, modifying the key
value.
      . Basing the unique index on a float value.
      . Using a fixed-length text field that may be padded on the server
with the correct amount of spaces.
      . Having a linked ODBC table containing Null values in any of the
fields making up the unique index.
These factors do not directly cause the "#Deleted" error message. Instead,
they cause Microsoft Access to go to the next step in maintaining the key
values, which is to select the record again, this time with the criteria
based on all the other fields in the record. If this step returns more than
one record, Microsoft Access returns the "#Deleted" message because it does
not have a reliable key value to work with. If you close and re-open the
table or choose Show All Records from the Records menu, the "#Deleted"
errors are removed.

Microsoft Access uses a similar process to retrieve records from an linked
ODBC table. First, it retrieves the key values and then the rest of the
fields that match the key values. If Microsoft Access is not able to find
that value again when it tries to find the rest of the record, it assumes
that the record is deleted.
     Back to the top

RESOLUTION
The following are some strategies that you can use to avoid this behavior:
      . Avoid entering records that are exactly the same except for the
unique index.
      . Avoid an update that triggers updates of both the unique index and
another field.
      . Do not use a Float field as a unique index or as part of a unique
index because of the inherent rounding problems of this data type.
      . Do all the updates and inserts by using SQL pass-through queries so
that you know exactly what is sent to the ODBC data source.
      . Retrieve records with an SQL pass-through query. An SQL pass-through
query is not updateable, and therefore does not cause "#Delete" errors.
      . Avoid storing Null values within any field making up the unique
index of your linked ODBC table.

     Back to the top

MORE INFORMATION
Note: In Microsoft Access 2.0, linked tables were called attached tables.
     Back to the top

Steps to Reproduce Behavior

      1. Open the sample database Northwind.mdb (or NWIND.MDB. in Microsoft
Access 2.0)
      2. Use the Upsizing Tools to upsize the Shippers table.

      NOTE: This table contains an AutoNumber field (or Counter field in
Microsoft Access 2.0) that is translated on SQL Server by the Upsizing Tools
into a trigger that emulates a counter.
      3. Open the linked Shippers table and enter a new record. Make sure
that the record you enter has the same data in the Company Name field as the
previous record.
      4. Press TAB to move to a new record. Note that the "#Deleted" error
fills the record you entered.
      5. Close and re-open the table. Note that the record is correct.

     Back to the top


--------------------------------------------------------------------------------

APPLIES TO
      . Microsoft Access 2.0 Standard Edition
      . Microsoft Access 95 Standard Edition
      . Microsoft Access 97 Standard Edition

     Back to the top

      Keywords:  kberrmsg kbinterop kbprb KB128809

----- Original Message -----
From: "Greg Campbell" <greg.campbell@us.michelin.com>
To: "Pgsql-Odbc (E-mail)" <pgsql-odbc@postgresql.org>
Sent: Wednesday, July 13, 2005 12:16 AM
Subject: [ODBC] MS Access and #deleted due to timeouts


>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
>

Attachment

Re: MS Access and #deleted due to timeouts

From
"Greg Campbell"
Date:
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

Attachment