Problems reading a .mdb database.

[MPrimal]

I am using a .mdb database created in Microsoft Access to store reference data for my program. It has three sepereate titles in it which are accessed on program start up and the data stored ready for use.

When I try and access one of the titles (the first one) within the database my code starts at Row 6 and reads everything in and once it gets to the end, it then goes back and reads the first 5 rows and puts them on the end.

This is the code I use to read the information from the title (note the Debug commands are how I figured out it is reading the information wrong):

Code: Select all

TableName.s = "AccInfo" 

If DatabaseQuery("SELECT * FROM [" + TableName.s + "]") 
   
  Columns= DatabaseColumns() ; How many columns/fields (across) in the table 
EndIf     
  
; Now take the same sub-database and display it's contents!

    Count1 =1
    
   If DatabaseQuery("SELECT * FROM [" + TableName.s + "];") 
     
     While NextDatabaseRow() 
           
      For Count= 0 To 20
           
       DumpString.s = GetDatabaseString(Count)
       Debug "Count1 = "+Str(Count1)+"    Acc:"+DumpString.s
           Accessories (Count1,Count) = DumpString.s
       DumpString.s = "" 
             
                          
      Next Count
      
      For count = 21 To 28
      
      DumpString.s = GetDatabaseString(Count)
      Debug "Count1 = "+Str(Count1)+"    Var:"+DumpString.s
          Variant (Count1,Count-20) = DumpStrong.s
      DumpString.s = ""
      
      Next Count
      
        Count1 = Count1 + 1
    
     Wend 
       
   EndIf

When I view the database in Access the first entry starts "Blue" with row 6 starting "Orange". But this code always reads "Orange" as the first entry. I tried using the PreviousDatabaseRow() & FirstDatabaseRow() commands to make sure I was at the beginning of the file, but they made no difference.

Does anyone know why this is happening. When I access the other two titles I do not get this problem, only with this one for some reason.

Any thoughts would be welcomed as I am totaly lost on this at the moment.

Thanks

Andy

[ebs]

Andy,

Rows stored in a database don't have any specific order, regardless of how they may be displayed.
If you want to retrieve rows in a particular order, use an SQL query with an "ORDER BY" clause, such as:

SELECT * FROM [TableName] ORDER BY [FieldName]

This will guarantee that the rows are retrieved in the order desired.

Regards,
Eric

[MPrimal]

Andy,

Rows stored in a database don't have any specific order, regardless of how they may be displayed.
If you want to retrieve rows in a particular order, use an SQL query with an "ORDER BY" clause, such as:

SELECT * FROM [TableName] ORDER BY [FieldName]

This will guarantee that the rows are retrieved in the order desired.

Regards,
Eric

Thank you. I did not realise this was the case. I thought you always got the data in the order it was entered in unless you where applying some type of search to the data. I while I have an understanding on using Databases, I am not genned up on how they work outside of the program I use to create them. Nor do I know anything about the SQL stuff. Perhaps that is something to look into as well.

After two attempts just now (I did not know how to apply the commands properly), I have now got the data in the order it was entered. Now I can at least apply it as I need to within the program. I had a feeling there was a simple answer to this.

Thanks again Eric.

Andy