borderAndreaVB free resources for Visual Basic developersborder
borderAndreaVB Visual Basic and VB.NET source code resources - Copyright © 1999-2014 Andrea Tincaniborder

AndreaVB | Forum | News | Downloads | Register | Help | Member List | Statistics | Search | PM | Profile

Print This Topic
Previous Topic (\"can\'t find project or library\" error)Next Topic (Can I use ADO in VBA) New Topic New Poll Post Reply
AndreaVB Forum : VB General : How can I loop through the records of an Access file ? Solved Topic
Poster Message
iliekater
Level: Wizard

Registered: 04-02-2005
Posts: 148

icon How can I loop through the records of an Access file ?

Thanks to almighty Yronium , I have managed to succesfully use Access data bases in my Vb applications , that is I can add , delete or modify the contents of such a file . And that's really great . However , I decided to go one step further . Now I want to loop through the contents of a table in a data base file (practically to compare a given number with those stored in a field of a table) . How can I do that ?
I started using something like :


  Dim DataBaseELECTRICAL As ADODB.Connection
  Dim RecordSetELECTRICAL As ADODB.Recordset
  ' open the connection
  Set DataBaseELECTRICAL = New ADODB.Connection
  Dim DataBaseString As String
  DataBaseString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\AutoCAD\Kater\KaterWARE.mdb;Mode=ReadWrite;Persist Security Info=False"
  DataBaseELECTRICAL.Open (DataBaseString)
  ' open the recordset
  Set RecordSetELECTRICAL = New ADODB.Recordset
  SQL = "SELECT * FROM Electrical_Blocks ORDER BY Name;"
  RecordSetELECTRICAL.CursorLocation = adUseClient
  ' looping through the records
  For Each RecordSetELECTRICAL In DataBaseELECTRICAL
           If RecordSetELECTRICAL.Fields("Name") = ElectricalName Then
              Main_Dialog_Box.LabelDESCRIPTION_LINE.Caption = RecordSetELECTRICAL.Fields("Description")
           End If
  Next
  ' closing the recordset and database
  RecordSetELECTRICAL.Close
  Set RecordSetELECTRICAL = Nothing
  DataBaseELECTRICAL.Close
  Set DataBaseELECTRICAL = Nothing

However , this doesn't work , becouse simply I can't just use :

For Each RecordSetELECTRICAL In DataBaseELECTRICAL

So , there must another way to search through all the records . Can someone help me ?

25-11-2006 at 09:28 PM
View Profile Send Email to User Show All Posts | Quote Reply
iliekater
Level: Wizard

Registered: 04-02-2005
Posts: 148
icon Re: How can I loop through the records of an Access file ?

Well , I think I foynd something : I used the MoveFirst and MoveNext methods of RecordSet . The code looks like this :


  RecordSetELECTRICAL.MoveFirst
  For i = -1 To RecordSetELECTRICAL.RecordCount
      i = i + 1
      If RecordSetELECTRICAL.Fields("Name") = ElectricalName Then
         Main_Dialog_Box.LabelDESCRIPTION_LINE.Caption = RecordSetELECTRICAL.Fields("Description")
         Exit For
      Else
         RecordSetELECTRICAL.MoveNext
      End If
  Next

However , I have a new problem ... For some strange reason , the above code looks only in the first 89 rfecords . Can anyone imagine why ?

26-11-2006 at 09:56 AM
View Profile Send Email to User Show All Posts | Quote Reply
iliekater
Level: Wizard

Registered: 04-02-2005
Posts: 148
icon Re: How can I loop through the records of an Access file ?

Hey , psst . Yeah you .  Keep it quite . Sssssss ! If someone asks who posted the reply above , don't say it was me . I don't want to lough at me !
The line :
      i = i + 1
was not only useless , but also damaged the whole precces ( ita catually doubled the value of the counter , leading thus , to a premature end of the loop .

Sssss . Hey ! I said stop loughing !

26-11-2006 at 10:06 AM
View Profile Send Email to User Show All Posts | Quote Reply
AndreaVB Forum : VB General : How can I loop through the records of an Access file ? Solved Topic
Previous Topic (\"can\'t find project or library\" error)Next Topic (Can I use ADO in VBA) New Topic New Poll Post Reply
Surf To:


Not Logged In? Username: Password: Lost your password?
borderAndreaVB free resources for Visual Basic developersborder
borderAndreaVB Visual Basic and VB.NET source code resources - Copyright © 1999-2014 Andrea Tincaniborder