Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    15

    Red face Unanswered: Not all records are showing on the List View form

    Hi All,

    We have an ERP system whose front end is in Access 97. Everytime I modify forms and reports, i only convert the .mdb file to .mde file then roll-out to end-users who are using Access 2003. It works fine for the past 2 years but one of my end-users suddenly noticed that some records were missing while viewing them in List View Form. I've checked it on my development pc, and it all shows the records (using my Access 97). I've check also our tables (which are in Oracle 10g), all the records my end-user is claiming that it's missing is existing in the database. But if they viewing it from Access 2003, the records were not showing on the form view, and if i will run the query source itself all records were showing. This is a little bit odd.

    Hope somebody out there can help me ASAP...

    Best Regards,

    Joseph B. Corpuz

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Perhaps refreshing the tables on the mde with the missing records. Otherwise, perhaps criteria based on a specific user nane, or other "default" value specific to that interface.

    I might copy them a new mde file and see if still persists. If so, then look at any coding you have that is based on the getuser() routine (if you're using it).

    Since you're using an Oracle backend, could it perhaps be a permissions problem for that user on Oracle itself?

    You can always start with opening the tables and see if all records are there that should be. Then open the form and troublehsoot there. Then apply criteria and see if that's the problem. Since it sounds limited to 1 user, my guess is that there's some field which based on a different username (if you're using the getuser routine) or other logic which is causing the missing records. It'd be interesting to see if you want to upload it.
    Last edited by pkstormy; 09-15-09 at 23:45.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2009
    Posts
    15
    Thanks for the quick reply.

    I tried creating the same list view format in Access 2003. then use the same record source as in my Access 97. It shows all the records. Could it be, some of the activeX controls (.ocx) in 97 were not already supported by Access 2003?

    Or could it be a problem on the ODBC link?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Remember for ODBC DSN Names - they ALL must be EXACTLY (word for word) the same on ALL computers.

    If an ODBC dsn name is: LTVCustomersDB on one machine and it's called LTVCustomersXPDB on another machine (to the same database), you'll drive yourself crazy as you'll refresh one dataset and then get errors on the other. Once you refresh the linked tables on thd other interface, then the previous mdb no longer shows the correct records.

    Rule #1 - any ODBC DSN names that access a database MUST be consistently named for all machines.

    That is, it's it's ODBC DSN name related and each computer has a different ODBC name to access the same database. This is low for causing the problems but you should check them just to make sure.

    Worst case, simply delete all the linked tables and re-link them in again in both mdb files (noting the ODBC name to do the linking.)
    Last edited by pkstormy; 09-16-09 at 00:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Sep 2009
    Posts
    15
    My tsnames.ora were typed like this...

    Q4PAG.WORLD =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = phpagap01)(PORT = 1528))
    (CONNECT_DATA = (SID = Q4PG))

    My ODBC DSN name were type in small caps. I tried relinking the tables on the .mde file but still doesnt solve the problem.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I re-read the last line in your first topic "While viewing them in a List View......But if they viewing it from Access 2003, the records were not showing on the form view, and if i will run the query source itself all records were showing."

    Since running the rowsource query itself produces all the records compared to viewing them in a List View - assuming this is a listbox, I've only seen this happen where a daisy-chained hub was used in the network setup (versus a switchbox) and the network card for that user was an older slow network card. For some of the users on the older network card, the comboboxes/listboxes would often return only half the number of records. This was especially prevelant in those with litle memory. Yet running the query itself produced all the records.

    You may want to compare memory/hardware/nic card of that user's computer versus the others.

    Once the nic card was replaced things improved, and when we went to a switchbox or full pc computer, there were no more problems. Not sure if this is similar to your case but it's something you could check.

    I don't deal much with Oracle, just SQL Server, so I couldn't say if it could be something Oracle-wise (sounds doubtful since it works for everyone else).

    If you're using a switchbox and am sure the nic card is good, (Can you run any speed tests on the nic card itself to see how it's speed compared to others), my only thoughts are that something else using that port (for Oracle) is limited by another application using it also for that user. You could even try a new network cable and plugging it into another network jack.

    With MSAccess 2003 working/not working where 97 worked, this could be the way MSAccess 2003 was re-constructed (behind the scenes) and the aditional toll it takes on the server. I can't give you an answer on the *.ocx without seeing how that comes into play though.

    You could also try a test by creating a form and put multiple listboxes on them (which returns several records) and then see how they compare to other users. If most/all of them return only a few records for the one user, I'd start looking at the nic card, memory, and daisy-chain setup if you have one. It's also a good check for the other users.

    Are these by chance Terminal Server boxes or full PC boxes?
    Last edited by pkstormy; 09-16-09 at 04:28.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •