How do I get to LOOP through my access 97 table

Question

1.00/5 (1 vote)

See more:

When I click a button with a letter on, say M I cannot loop through my table to get results to display in a Listview. I am using VB6 and Access 97

What I have tried:

Numerous hours searching the internet and various coding ways, from previous applications I have done. This is my first using a connection string. The code for the button is as follows:

Private Sub cmd_SelectByLetter_Click(Index As Integer)
    Dim Kga_RS As Recordset
    Dim sql, myLetter As String
    Dim i As Long 'use long if the database has a huge amount of records; other wise use integer
    ' clear the listview
    lstv_AllKgaMembers.ListItems.Clear
    Set Kga_RS = New Recordset
'    Kga_RS.LockType = adLockOptimistic
'    Kga_RS.CursorType = adOpenKeyset
    myLetter = Chr(CLng(Index) + 65)
    sql = "SELECT * FROM [tbl_KGA_MEMBER_DETAILS] WHERE [F_Name] LIKE '" & myLetter _
        & "*' ORDER BY [F_Name] ASC"
    Kga_RS.Open sql, dbCon, , adLockOptimistic
    i = 0
    With lstv_AllKgaMembers
        Do Until Kga_RS.EOF
            FullName = Kga_RS!F_Name & " " & Kga_RS!L_Name
            ' "i" will be used to point to the row number for the items being added
            ' to the listview increment the i each time it loops past it
            i = i + 1
            ' now write the records from the recordset to the listview fields
            .ListItems.Add , , Kga_RS!KgaNo 'control id
            ' you can short cut this, but it is important not to do so check for blank fields,
            ' otherwise you'll get errors when there is a field that is blank (for example, if there is no address)
            If Not FullName = "" Then .ListItems(i).ListSubItems.Add , , FullName Else .ListItems(i).ListSubItems.Add , , ""
            Kga_RS.MoveNext
        Loop
    End With
    Kga_RS.Close
    cmd_SelectAllMembers.Enabled = True
    cmd_ClearAll.Visible = True
End Sub

Posted 16-Sep-18 16:46pm

Mick_of_Goju

Updated 7-Mar-19 2:11am

Add a Solution

Comments

Maciej Los 17-Sep-18 4:12am

Unclear! Please, be more specific and provide more details about your issue.
Note: i do not see any connection string.

Mick_of_Goju 17-Sep-18 19:43pm

I have a form with a Listview control and several buttons. One button when clicked fills the listview with all the members from my DB that is Membership No and Name no issues when this button is clicked. I have buttons which are indexed and the buttons have the first letter of the alphabet that is A.....Z if I want to list members with the first name staring with M I click the M button but the code does not loop but goes from "With lstv_AllKgaMembers" then "Do Until Kga_RS.EOF" then straight to "End with" but does not Loop through the record to display all name starting with M. Also the code for my connection is.
dbFile = App.Path & "\KgaDB.mdb"
Set dbCon = New Connection
' for Access below version 2007 I am using Access’97
dbCon.Provider = "Microsoft.Jet.OLEDB.4.0"
' dbCon.Provider = "Microsoft.ACE.OLEDB.12.0" 'this string also works OK
dbCon.Mode = adModeReadWrite
dbCon.Open (dbFile)
I also point out that all my other forms I use do not have any issues. I thank you for your time and hope I have provided enough info for you to assist.

Richard Deeming 18-Sep-18 15:50pm

VB6 and Access 97 - a language that's been "dead" for over 15 years, talking to a database that's been "dead" for 18 years.

You'll struggle to find anyone who still has either of those running, or can remember enough about how they work to help you. :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-03-07T02:12:00

Assuming this is still an issue here are a few things to try

sql = "SELECT * FROM [tbl_KGA_MEMBER_DETAILS] WHERE [F_Name] LIKE '" & myLetter _
        & "%' ORDER BY [F_Name] ASC"

Note using string concatenation is a huge sql injection risk, but I don't remember if you can use parameterised queries with VB6 (and I haven't had it installed for years).

If you were writing the query in Access you would use * for the wildcard but using OleDb I think you need to use the sql-like '%' for the wildcard. You could always removed the WHERE altogether until you track down the problem.

If that doesn't work try putting

VB

Kga_RS.MoveFirst

before the loop

If that still doesn't work then look again at your connection string. Try

Set DBcON = New Connection
Dim strConn As String
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbFile & ";"
    conn.Open (strConn)

Finally, you have not said where App.Path is - if that is (e.g.) Program Files (x86) then any data added to the database will probably be in the users AppData folder and not in the App.Path (Assuming you are using a version of Windows >= Vista) - try moving the database to an explicit folder (like C:\temp for example)