By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,090 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

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

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT xxx1, xxx2, xxx3, xxx4, 
  2. FROM table
  3. GROUP BY xxx1, xxx2, xxx3, xxx4; 
  4.  
  • 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
Dec 18 '07 #1
Share this Question
Share on Google+
3 Replies


puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT xxx1, xxx2, xxx3, xxx4, 
  2. FROM table
  3. GROUP BY xxx1, xxx2, xxx3, xxx4; 
  4.  
  • 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.
Dec 19 '07 #2

puppydogbuddy
Expert 100+
P: 1,923
Taco,

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

http://allenbrowne.com/ser-47.html#RowsShowDeleted
Dec 20 '07 #3

P: 3
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
Dec 20 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.