#deleted in Access

I have MySQL 3.21.x and MySQL Connector/ODBC 3.51 and for one of my tables, all values show up as #Deleted. I've followed the directions found under Support on mysql.com, but to no avail. I'm using Access 2003.



Here is a definition of the table:



CREATE TABLE `device_responses` (

`id` bigint(20) unsigned NOT NULL auto_increment,

`time_tag` bigint(10) unsigned default NULL,

`system` enum('D') NOT NULL default 'STE',

`device` enum('A', 'B', 'C') NOT NULL default 'HEARTBEAT',

`subunit` enum('0','1','2','3','4','5','6','7','8','9','10','11','12', '13','14','15','16','17','18','19','20','21','22','23') NOT NULL default '0',

`sender` enum('0','1','2','3','4','5','6','7','8') default '0',

`command` varchar(8) default NULL,

`response` varchar(8) default NULL,

PRIMARY KEY (`id`),

KEY `(command, response)` (`command`,`response`),

KEY `(response, command)` (`response`,`command`),

KEY `device` (`device`),

KEY `system` (`system`),

KEY `sender` (`sender`)

) TYPE=InnoDB AUTO_INCREMENT=1 ;





Other tables have similar properties, and they do not have this problem. This is the only InnoDB table, but even after dropping it, and creating new as MyISAM, I had no luck. Other tables have bigint() and they are okay.



Any help would be appreciated.



Thanks



_______________________________________________
No banners. No pop-ups. No kidding.
Make My Way your home on the Web - http://www.myway.com



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: #deleted in Access

I'm not sure about MyODBC but from memory this occurs in Access because it
attempts to retrieve a row with the primary key value (it previously retrieved)
and couldn't find the row (indicating to Access the row has been deleted).

i.e. it does
select primary_key_column from table then retrieves the data with
select a,b,c,d from table where primary_key_column = ?
and fails to retrieve a row.

In actual fact, it tends to retrieve 10 rows at a time with

select a,b,c,d from table where pcc = ? or pcc=? or pcc=? (10 of these).

Not sure if this helps you but it may give you or someone else somewhere to
look.

Martin

On 03-Feb-2006 Rigov wrote:
>
>
> I have MySQL 3.21.x and MySQL Connector/ODBC 3.51 and for one of my tables,
> all values show up as #Deleted. I've followed the directions found under
> Support on mysql.com, but to no avail. I'm using Access 2003.
>
> Here is a definition of the table:
>
> CREATE TABLE `device_responses` (
> `id` bigint(20) unsigned NOT NULL auto_increment,
> `time_tag` bigint(10) unsigned default NULL,
> `system` enum('D') NOT NULL default 'STE',
> `device` enum('A', 'B', 'C') NOT NULL default 'HEARTBEAT',
> `subunit`
> enum('0','1','2','3','4','5','6','7','8','9','10','11','12', '13','14','15','16
> ','17','18','19','20','21','22','23') NOT NULL default '0',
> `sender` enum('0','1','2','3','4','5','6','7','8') default '0',
> `command` varchar(8) default NULL,
> `response` varchar(8) default NULL,
> PRIMARY KEY (`id`),
> KEY `(command, response)` (`command`,`response`),
> KEY `(response, command)` (`response`,`command`),
> KEY `device` (`device`),
> KEY `system` (`system`),
> KEY `sender` (`sender`)
> ) TYPE=InnoDB AUTO_INCREMENT=1 ;
>
>
> Other tables have similar properties, and they do not have this problem.
> This is the only InnoDB table, but even after dropping it, and creating new
> as MyISAM, I had no luck. Other tables have bigint() and they are okay.
>
> Any help would be appreciated.
>
> Thanks
>
>
> _______________________________________________
> No banners. No pop-ups. No kidding.
> Make My Way your home on the Web - http://www.myway.com
>
>
>
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe:
> http://lists.mysql.com/myodbc?unsub=martin.evans@easysoft.co m

--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: #deleted in Access

I have found that adding a TIMESTAMP column to the table in MySQL solves
most #deleted problems with MyODBC and Access. Take a look at
http://dev.mysql.com/doc/refman/5.1/en/deleted-problem.html. When =
you've
added the timestamp column, remember to refresh the table definition in
Access so that Access can see the additional column.

Martin King-Turner
-----Original Message-----
From: Martin J. Evans [mailto:martin.evans@easysoft.com]=20
Sent: 03 February 2006 18:06
To: myodbc@lists.mysql.com
Subject: RE: #deleted in Access


I'm not sure about MyODBC but from memory this occurs in Access because =
it
attempts to retrieve a row with the primary key value (it previously
retrieved) and couldn't find the row (indicating to Access the row has =
been
deleted).

i.e. it does
select primary_key_column from table then retrieves the data with select
a,b,c,d from table where primary_key_column =3D ? and fails to retrieve =
a row.

In actual fact, it tends to retrieve 10 rows at a time with

select a,b,c,d from table where pcc =3D ? or pcc=3D? or pcc=3D? (10 of =
these).

Not sure if this helps you but it may give you or someone else somewhere =
to
look.

Martin

On 03-Feb-2006 Rigov wrote:
>=20
>=20
> I have MySQL 3.21.x and MySQL Connector/ODBC 3.51 and for one of my=20
> tables, all values show up as #Deleted. I've followed the directions=20
> found under Support on mysql.com, but to no avail. I'm using Access =
2003.
>=20
> Here is a definition of the table:
>=20
> CREATE TABLE `device_responses` (
> `id` bigint(20) unsigned NOT NULL auto_increment,
> `time_tag` bigint(10) unsigned default NULL,
> `system` enum('D') NOT NULL default 'STE',
> `device` enum('A', 'B', 'C') NOT NULL default 'HEARTBEAT',
> `subunit`=20
> enum('0','1','2','3','4','5','6','7','8','9','10','11','12', '13','14',
> '15','16
> ','17','18','19','20','21','22','23') NOT NULL default '0',
> `sender` enum('0','1','2','3','4','5','6','7','8') default '0',
> `command` varchar(8) default NULL,
> `response` varchar(8) default NULL,
> PRIMARY KEY (`id`),
> KEY `(command, response)` (`command`,`response`),
> KEY `(response, command)` (`response`,`command`),
> KEY `device` (`device`),
> KEY `system` (`system`),
> KEY `sender` (`sender`)
> ) TYPE=3DInnoDB AUTO_INCREMENT=3D1 ;
>=20
>=20
> Other tables have similar properties, and they do not have this=20
> problem.
> This is the only InnoDB table, but even after dropping it, and =
creating
new
> as MyISAM, I had no luck. Other tables have bigint() and they are =
okay.
>=20
> Any help would be appreciated.
>=20
> Thanks
>=20
>=20
> _______________________________________________
> No banners. No pop-ups. No kidding.
> Make My Way your home on the Web - http://www.myway.com
>=20
>=20
>=20
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe: =20
> http://lists.mysql.com/myodbc?unsub=3Dmartin.evans@easysoft. com

--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com


--=20
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: =
http://lists.mysql.com/myodbc?unsub=3Dmartin.kt@nb2bc.co.uk




--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

RE: #deleted in Access

MySQL table that are linked in Access must have two thing in every
single liked table. Those two items are a TIMESTAMP column and a Primary
key (PK).=20

I had this issue a few years ago when I first set up Access with linked
table to MySQL.

-----Original Message-----
From: Martin King-Turner [mailto:martin.kt@nb2bc.co.uk]=20
Sent: Friday, February 03, 2006 2:55 PM
To: 'Rigov'
Cc: 'Martin J. Evans'; myodbc@lists.mysql.com
Subject: RE: #deleted in Access

I have found that adding a TIMESTAMP column to the table in MySQL solves
most #deleted problems with MyODBC and Access. Take a look at
http://dev.mysql.com/doc/refman/5.1/en/deleted-problem.html. When
you've
added the timestamp column, remember to refresh the table definition in
Access so that Access can see the additional column.

Martin King-Turner
-----Original Message-----
From: Martin J. Evans [mailto:martin.evans@easysoft.com]=20
Sent: 03 February 2006 18:06
To: myodbc@lists.mysql.com
Subject: RE: #deleted in Access


I'm not sure about MyODBC but from memory this occurs in Access because
it
attempts to retrieve a row with the primary key value (it previously
retrieved) and couldn't find the row (indicating to Access the row has
been
deleted).

i.e. it does
select primary_key_column from table then retrieves the data with select
a,b,c,d from table where primary_key_column =3D ? and fails to retrieve =
a
row.

In actual fact, it tends to retrieve 10 rows at a time with

select a,b,c,d from table where pcc =3D ? or pcc=3D? or pcc=3D? (10 of =
these).

Not sure if this helps you but it may give you or someone else somewhere
to
look.

Martin

On 03-Feb-2006 Rigov wrote:
>=20
>=20
> I have MySQL 3.21.x and MySQL Connector/ODBC 3.51 and for one of my=20
> tables, all values show up as #Deleted. I've followed the directions=20
> found under Support on mysql.com, but to no avail. I'm using Access
2003.
>=20
> Here is a definition of the table:
>=20
> CREATE TABLE `device_responses` (
> `id` bigint(20) unsigned NOT NULL auto_increment,
> `time_tag` bigint(10) unsigned default NULL,
> `system` enum('D') NOT NULL default 'STE',
> `device` enum('A', 'B', 'C') NOT NULL default 'HEARTBEAT',
> `subunit`=20
> enum('0','1','2','3','4','5','6','7','8','9','10','11','12', '13','14',
> '15','16
> ','17','18','19','20','21','22','23') NOT NULL default '0',
> `sender` enum('0','1','2','3','4','5','6','7','8') default '0',
> `command` varchar(8) default NULL,
> `response` varchar(8) default NULL,
> PRIMARY KEY (`id`),
> KEY `(command, response)` (`command`,`response`),
> KEY `(response, command)` (`response`,`command`),
> KEY `device` (`device`),
> KEY `system` (`system`),
> KEY `sender` (`sender`)
> ) TYPE=3DInnoDB AUTO_INCREMENT=3D1 ;
>=20
>=20
> Other tables have similar properties, and they do not have this=20
> problem.
> This is the only InnoDB table, but even after dropping it, and
creating
new
> as MyISAM, I had no luck. Other tables have bigint() and they are
okay.
>=20
> Any help would be appreciated.
>=20
> Thanks
>=20
>=20
> _______________________________________________
> No banners. No pop-ups. No kidding.
> Make My Way your home on the Web - http://www.myway.com
>=20
>=20
>=20
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe: =20
> http://lists.mysql.com/myodbc?unsub=3Dmartin.evans@easysoft. com

--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com


--=20
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:
http://lists.mysql.com/myodbc?unsub=3Dmartin.kt@nb2bc.co.uk




--=20
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:
http://lists.mysql.com/myodbc?unsub=3Dralexander@csmresearch .com


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

RE: #deleted in Access

I just had this problem using ODBC 3.51 and I solved it by converting BIGINT
data types to INT. I had the flag in the ODBC data source set to convert
BIGINT to INT but that didn't seem to correct the issue.

Vic

-----Original Message-----
From: Rigov [mailto:imlistening@myway.com]
Sent: Friday, February 03, 2006 9:39 AM
To: myodbc@lists.mysql.com
Subject: #deleted in Access



I have MySQL 3.21.x and MySQL Connector/ODBC 3.51 and for one of my tables,
all values show up as #Deleted. I've followed the directions found under
Support on mysql.com, but to no avail. I'm using Access 2003.

Here is a definition of the table:

CREATE TABLE `device_responses` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`time_tag` bigint(10) unsigned default NULL,
`system` enum('D') NOT NULL default 'STE',
`device` enum('A', 'B', 'C') NOT NULL default 'HEARTBEAT',
`subunit`
enum('0','1','2','3','4','5','6','7','8','9','10','11','12', '13','14','15','
16','17','18','19','20','21','22','23') NOT NULL default '0',
`sender` enum('0','1','2','3','4','5','6','7','8') default '0',
`command` varchar(8) default NULL,
`response` varchar(8) default NULL,
PRIMARY KEY (`id`),
KEY `(command, response)` (`command`,`response`),
KEY `(response, command)` (`response`,`command`),
KEY `device` (`device`),
KEY `system` (`system`),
KEY `sender` (`sender`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;


Other tables have similar properties, and they do not have this problem.
This is the only InnoDB table, but even after dropping it, and creating new
as MyISAM, I had no luck. Other tables have bigint() and they are okay.

Any help would be appreciated.

Thanks


_______________________________________________
No banners. No pop-ups. No kidding.
Make My Way your home on the Web - http://www.myway.com



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=vic@perfected.com


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org