Comparison of two texts does not work

[ZikO]

Hi,

I have an application in ACCESS 2010 that works on database that I made based on a guild in WoW. I have written a procedure that is supposed to updated a guild roster and add new members.
I cannot work out the text comparison. The procedure always duplicates records. I compare guild names from current database (Text a field) with a string extracted from a JSON object that collect information from internet stored in JSON format. The JSON functions work because I tested them in Excel. I have tried trivial comparisons such as str1 = str2. It did not work. Therefore, I look for a function and found

StrComp(str1, str2, vbTextCompare)

but it also did not work. Both text may have Unicode non standard characters, though. When I used vbBinaryCompary, I got error "No current record". I spent the whole week to make it work but I am running out of ideas how to test the procedure and get rid of faulty pieces of codes.

If someone would have a look at the code - specially UpdateRoster(), I would be really grateful. I am sure the code is far from ideal but I am learning. The code is somehow collection of solutions I've found in internet.

The application.
The form has a button "Update" that runs "UpdateRoster" procedure. This procedure originally updates the tables: tblMembers, tblMemberPrimary, and tblMemberSecondary but at the moment I operate on their copies.

I am open to all criticisms regarding the code. I am sure it can be done much better.

Thanks

Attachment:
Phoenix_Rising.accdb

 

[ZikO]

I looked at my code once again and I have also used debugger. I can see that the portion of code that does not work is this one:

    Dim db As DAO.Database
    Dim recSet As DAO.Recordset
    Dim guildObj As Object
    state = guildObj() ' object that collects data from internet
    ....
    strSQL = "SELECT * FROM [Copy of tblMembers];"
    Set recSet = db.OpenRecordset(strSQL)
    For Each member In guildObj.Item("members")
        With member.Item("character")
            Do While Not recSet.EOF
                If StrComp(recSet.Fields("member_name"), .Item("name"), vbBinaryCompare) = 0 Then
                    Exit Do
                End If
                recSet.MoveNext
            Loop

I am 100% sure guildObj works. It always returns 400 names as expected--the exact number of members is in the guild. For some reason, recSet does not collect all records from the table [Copy of tblMembers]. There is no condition WHERE. I also tried

Set recSet = db.OpenRecordset("Copy of tblMembers")

but the result is the same.

Is it possible that the Recordset could not contain all records from a table / query?

 

[mdlueck]

1) Have you tried opening table [Copy of tblMembers] in the Access UI and verifued it has the number of records you are expecting it to contain?

If that is correct then...

2) Set a break point in this code, place a watch on object recSet, allow it to execute the SQL, then after it has executed in the Watches window dig into the members of that object and see what it has for the number of records value.

How to: Count the Number of Records in a DAO Recordset
http://msdn.microsoft.com/en-us/library/office/bb243791(v=office.12).aspx

 

[ZikO]

Hi mdlueck,

I am even more confused. First of all, thanks for the clues. I managed to watch recSet after opening Recordset and I have got (1) 400 records which is correct number when used the code

Set recSet = db.OpenRecordset("Copy of tblMembers");

and (2) only 1 record when used the expression below

Set recSet = db.OpenRecordset("SELECT * FROM [Copy of tblMembers];")

Nonetheless, I have noticed all records are still doubled when I used the first version of the code, unfortunately. I am glad I can watch the object during run-time. Perhaps I can find out a little more about the problem. If you have any other suggestions, please, I'd be very happy to know them

Many thanks.

 

[spikepl]

Show entire code. In #2, if you do not go back to the beginning of the record set at the end of the loop, you do only get one round.

 

[ZikO]

Dear All,

I am glad to say that during iterations I missed command "recSet.MoveNext" I assumed it was there but I missed that. At the moment the problem I described disappeared.

If I can have this opportunity, I would like to ask about one more thing that confuses me a little bit. For SQL, I have used CurrentDb.Execute strSQL function. I have also read about DoCmd.RunSQL and DAO.Recordset. Is there any advantage or reasons for using one method over the others? i usually opened Recordset to read existing records and CurrentDb.execute to alter / delete records. I don't know if it's correct.

Thank you

 

[mdlueck]

I have also read about DoCmd.RunSQL and DAO.Recordset. Is there any advantage or reasons for using one method over the others?

I prefer to use ADO objects where ever possible, and DAO objects for the rest.

With ADO objects, I prefer ADO.Command objects where possible, ADO.RecordSet objects are second best. ADO.Command objects work along with ADO.Parameters objects which encapsulate variables making the SQL syntax much cleaner, hardens against SQL injection attacks, etc...

Some suggested posts to read:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746