[RESOLVED] Anomaly with display of MS Access database table in a DATAGRID

I am working with a MS Access database table of some 1600 rows by 8 columns. The data is numerical.

A path is established from an ADODC control to the Access table, and a DATAGRID on the form is linked to that ADODC.

This more or less works fine. The Access table data is shown in the DATAGRID.

HOWEVER, a problem variously arises after row 1000. For the most part all the data looks good, but chunks of row groups appear out of sequence with respect to their column 1 (ID Field) numerical value when viewed in the DATAGRID. The ID values are correct, but in the DATAGRID they appear out of sequence. Yet when the Access table is viewed using the MS Access program, all rows appear in their correct numerical sequence and the table looks fine.

Hence when I use such commands as ADODC3.Recordset.Movenext (or Moveprevious etc.) I can not be certain which row no. will be read. It will be the next row as displayed in the DATAGRID and not necessarily the next row in ID. number sequence.

This problem only arises (so far) for row ID numbers greater than 1000.

Ideas requested please. Is there some limit as to the number of Access table rows which VB6 ADODC / DATAGRID can correctly handle?

camoore

Wales, UK

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)


It seems to me that the DataGrid is doing exactly what it should do, and that the issue is a bit of database theory that is common for programmers to have problems with... the data is not stored in any particular sequence, it is stored as a Set that can be returned in any order that the database system decides at that moment.

If you want the data to be in a certain order, add an ORDER BY clause to your SQL statement.

I note your transfer of thread to Database Development Forum. I was not sure whether this was a VB6 issue or a database one.

I am not using SQL (as far as I know) and my knowledge does not extend to this.

What I have found is this : If I open my Access table using MS Access then go to Tools > Database Utilities > Compact and Repair Database then the problem gets resolved and everything "comes good". Upon returning to my VB6 program, all data in the DATAGRID is in the correct order of row ID numbers.

Is there please a way of automating from VB6 this MS Access routine, or its equivalent, without my having to open up MS Access and do it manually?

camoore

Wales, UK

That 'fix' is at best temporary, and is not actually guaranteed to work... it will also take time to run.

The proper solution is to use an Order By, which should have no noticeable speed loss - and is guaranteed to work.


While you may not be aware of SQL being used, it is somewhere. I presume that in the properties of the data control (:eek: Why is using bound controls a bad thing? ) you currently have the name of the database table - which automatically gets changed to a basic SQL statement ("SELECT * FROM tablename").

All you should need to do is change that to a full SQL statement with an Order By, eg: "SELECT * FROM tablename ORDER BY fieldname"
You may also need to change another property to say that you are using an SQL statement rather than a table name.

The ADODC I am using has a RECORDSOURCE as simply "Table 9".

How please should I alter this so as to make table 9 be "ordered" as you suggest?


camoore

Wales, UK

It seems that "Table 9" is the name of your table, which is a very bad idea - you should only use alphanumeric characters for field and table names (no spaces or punctuation etc), because you will get problems otherwise. I strongly recommend you change it.


Use the table name in "SELECT * FROM tablename ORDER BY fieldname". If the name still has spaces in, put [] around it.

As for what fieldname should be, you should be able to work that out yourself.

Thank you Si.

The tablename was TABLE9 with no space. My typo in last post.

I believe I have worked out how to do what you suggested now :

Go to the properties of the ADODC. Then go to RecordSource and click on the builder button. This brings up the RecordSource Property Pages.

In Command Type select 1-adCmdText

In Command Text (SQL) type SELECT*FROM Table9 ORDER BY ID

Then click Apply.

This now seems to work fine and all the rows of data appearing in the DATAGRID which is linked to that ADODC are now correctly ordered by their ID column value - which was what was needed. Not having (knowingly) used SQL statements before, it took me a while to work out what to type where. Hence I have spelled out what I did above in case it proves of use to others.

camoore

Wales, UK