#deleted 9 of 10. Theories and workarounds that raise more questions...

[Taco]

Dear forum,

First, this is my first post here, so please be kind. I have been taking a look over this place for a while now and it seems like the right place to be.

Alright the issue at hand:

• There is an Informix DB that was on a Sun system. It worked just fine.
• Due to issues with the server, it was moved to an IBM (AIX) system.
• Users access the DB through Microsoft Access, via linked tables.
• Every 10 rows the first record is received, followed by 9 rows of #deleted.
• On occasion, when scrolling through the DB you can see places where the pattern seems to ‘reset’ skipping only one or two rows, then resuming the 1/9 pattern. This does not seem to be consistent
• The records that are shown as #deleted change depending on how the table is displayed (e. g. if you dump the table, add/delete a few records, dump the table again it displays in the same 1/9 pattern with whatever data is shown)
• If you look at the database directly, all records are there.
• The primary key for the database is correctly defined, is unique, and is != null in Access as well as on the system itself.
• Creating a new database, creating a new table, creating a new link does not fix this section
• Access version(s) 97-03 experience the same issue
• Database driver for the connection has not changed

My theories:

• Multifetch is failing to receive all records (can I set Multifetch to 1 instead of 10 somewhere?)
• Driver is having issues with new connection
• Access is having difficulty displaying records

Workaround:

SELECT xxx1, xxx2, xxx3, xxx4, 
FROM table
GROUP BY xxx1, xxx2, xxx3, xxx4; 
 

• Something like this, as long as you list all columns in the select and group by clause, you are able to pull the entire table.

Issue with workaround:

• If I tie the workaround to the form, I loose the ability to update the records
• I have no way of append to the end of the table inside a form (that I have found)
• It takes 1:22 to move to the end of the record set instead of customary 0:06 average
• I would have to do this for every table, for every form, and ‘undo’ it once the real solution is found

Any help would be appreciated.

Thank you,
-Taco

[puppydogbuddy]

Dear forum,

First, this is my first post here, so please be kind. I have been taking a look over this place for a while now and it seems like the right place to be.

Alright the issue at hand:

• There is an Informix DB that was on a Sun system. It worked just fine.
• Due to issues with the server, it was moved to an IBM (AIX) system.
• Users access the DB through Microsoft Access, via linked tables.
• Every 10 rows the first record is received, followed by 9 rows of #deleted.
• On occasion, when scrolling through the DB you can see places where the pattern seems to ‘reset’ skipping only one or two rows, then resuming the 1/9 pattern. This does not seem to be consistent
• The records that are shown as #deleted change depending on how the table is displayed (e. g. if you dump the table, add/delete a few records, dump the table again it displays in the same 1/9 pattern with whatever data is shown)
• If you look at the database directly, all records are there.
• The primary key for the database is correctly defined, is unique, and is != null in Access as well as on the system itself.
• Creating a new database, creating a new table, creating a new link does not fix this section
• Access version(s) 97-03 experience the same issue
• Database driver for the connection has not changed

My theories:

• Multifetch is failing to receive all records (can I set Multifetch to 1 instead of 10 somewhere?)
• Driver is having issues with new connection
• Access is having difficulty displaying records

Workaround:

SELECT xxx1, xxx2, xxx3, xxx4, 
FROM table
GROUP BY xxx1, xxx2, xxx3, xxx4; 
 

• Something like this, as long as you list all columns in the select and group by clause, you are able to pull the entire table.

Issue with workaround:

• If I tie the workaround to the form, I loose the ability to update the records
• I have no way of append to the end of the table inside a form (that I have found)
• It takes 1:22 to move to the end of the record set instead of customary 0:06 average
• I would have to do this for every table, for every form, and ‘undo’ it once the real solution is found

Any help would be appreciated.

Thank you,
-Taco

Taco,
It is my guess your problem is an outdated connection string or an outdated odbc driver, or both. For connection string, see this link:

http://www.connectionstrings.com/default.aspx

For driver, go to IBM website for latest compatible driver.

[puppydogbuddy]

Taco,

Here is another possibility....Corruption....see this link:

http://allenbrowne.com/ser-47.html#RowsShowDeleted

[Taco]

Taco,
It is my guess your problem is an outdated connection string or an outdated odbc driver, or both. For connection string, see this link:

http://www.connectionstrings.com/default.aspx

For driver, go to IBM website for latest compatible driver.

Taco,

Here is another possibility....Corruption....see this link:

http://allenbrowne.com/ser-47.html#RowsShowDeleted

Puppy,

Thanks for the ideas! I have some new info, but much of it is uninspiring so far.



-Connection String: I do not believe I am using a connection string that is 'editable' since it is an access linked table rather than a something written in VBA or other language. Please let me know if I can play around with it somewhere! I did take a look at one of the tables 'links' in the table properties description:
ODBC;
DSN=c.aix;
DB=proper_db_name;
HOST=proper_host_name;
SRVR=smprd01_tcp.1;
SERV=smprd01_tcp_1;
PRO=onsoctcp;
CLOC=en_US.CP1252;
DLOC=en_US.CP1252;
VMB=1;
CURB=1;
OPT=;
DESC=c.aix;
SCUR=0;
ICUR=0;
OAC=1;
OPTOFC=1;
ODTYP=0;
;
TABLE=database.oldbrokentable



-Corruption: I tried a make table query similar to:
SELECT xxx1, xxx2, xxx3
INTO [_TEST-CRE]
FROM oldbrokentable;
This created a local table that displayed all the rows correctly (without need of playing the this row works this one doesn’t). An import of the table works the same way.
I'm working on setting up a test table on the server that I can try and 'make-table' onto itself so I don't loose any relationships when it overwrites itself. Is there to 'make-table' on an ODBC db?



-Driver: I have not looked at a new driver as of yet, as it was working previously when the database was on the old system. This is going to take a bit more research.


-Taco