Access2000 & #deleted#

[Steven Schoch]

Michael Widenius wrote:

> Anyway, the real problem is that, even if one can with ODBC can use
> sql servers with auto_increment_values, there isn't any established
> protocol to find out the ID for the new row.  (Wonder who designed
> this protocol :).
>
> Access uses the following query to find out the new inserted row:
>
> SELECT .... FROM table  WHERE auto_increment_column IS NULL
>
> MySQL 3.23 tries to handle the above by converting the above query to:
>
> SELECT .... FROM table  WHERE auto_increment_column= LAST_INSERT_ID().
>
> This solves some problems, but introduces another:
>
> The problem is that, according to the logs, Access does the above
> query 7 times for each inserted row ???

I'm using Access 2000 and have seen a different behavior.  Here are some interesting lines
from myobdc.log:

| | info: Server version = '3.22.25'  capabilites: 12

One table is "company" with primary key column "company" which is auto_increment.
The other table is "contact" with primary key column "contact" auto_increment.

Here Access 2000 figures out how many rows from the sub form have a relationship to the
data in the main form:

| | query: Query = "SELECT contact_Subform.contact FROM contact contact_Subform WHERE
(company =  2 ) "

Then it grabs the data for the first record in the subform:

| | query: Query = "SELECT
contact,company,role,first,middle,last,address,city,state,post_code,phone,home_phone,fax,email,home_email,status,last_contact,modified,modified_by,title,created
FROM contact  WHERE contact = 3"

Now it seems to grab ALL the contacts by list (I don't understand this):

| | query: Query = "SELECT
contact,company,role,first,middle,last,address,city,state,post_code,phone,home_phone,fax,email,home_email,status,last_contact,modified,modified_by,title,created
FROM contact  WHERE contact = 54 OR contact = 55 OR contact = 56 OR contact = 58 OR
contact
= 58 OR contact = 58 OR contact = 58 OR contact = 58 OR contact = 58 OR contact = 58"

Now I attempt to add a contact record containing only a first name (Steven), which is a
duplicate.  The Access 2000 form will fill in the default values for "created" and
"created_by" to CurrentUserName() ("schoch") and NOW() ("19991018111838"):

| | query: Query = "INSERT INTO  contact  (company,first,modified_by,created) VALUES
(2,'Steven','schoch',19991018111838)"

Now comes the "NULL" query:

| | query: Query = "SELECT
contact,company,role,first,middle,last,address,city,state,post_code,phone,home_phone,fax,email,home_email,status,last_contact,modified,modified_by,title,created
FROM contact  WHERE contact IS NULL"

...and as expected:

| | | exit: Got 1 rows

Then it does it again:

| | query: Query = "SELECT
contact,company,role,first,middle,last,address,city,state,post_code,phone,home_phone,fax,email,home_email,status,last_contact,modified,modified_by,title,created
FROM contact  WHERE contact IS NULL"

.. and, as before:

| | | exit: Got 1 rows

Now, for some reason, Access 2000 puts a bunch of OR's in the where clause:

| | query: Query = "SELECT
contact,company,role,first,middle,last,address,city,state,post_code,phone,home_phone,fax,email,home_email,status,last_contact,modified,modified_by,title,created
FROM contact  WHERE contact IS NULL OR contact IS NULL OR contact IS NULL OR contact IS
NULL OR contact IS NULL OR contact IS NULL OR contact IS NULL OR contact IS NULL OR
contact
IS NULL OR contact IS NULL"

This also seems to return 1 row:

| | | exit: Got 1 rows

That was the last query in myodbc.log.  The record was marked #deleted".

Access 2000 is really confusing me on this one.  Can anyone help?

--
Steven Schoch
StarNet Communications Corp.
1270 Oakmead Pkwy. #301
Sunnyvale, CA 94086  [phone]