Android Question Convert B4A Code To Microsoft Access VB

[Mahares]

I have a table in Access with 2 columns WELL_NO and PUMPER. Both are TEXT. I want to update the PUMPER column from a text to a number starting with 900. Multiple WELL_NO can have the same PUMPER. For instance:
WELL_NO PUMPER
D7645 SA3
RA87 FR1
TP875 SA3
becomes:
WELL_NO PUMPER
D7645 900
RA87 901
TP875 900

The below code works very well in B4A (Thank goodness for B4A). I am curious to see the equivalent code in Access VB. Thank you

Sub ConvertPumperToNumber
    Dim txt2, MyPumper, MyInitials As String       
    Dim Cursor1 As Cursor
    txt="SELECT DISTINCT PUMPER FROM " & DBTableName & " WHERE PUMPER IS NOT NULL OR PUMPER ='' "
    Cursor1=SQL1.ExecQUERY(txt)
   
    Dim cursor2 As Cursor
    txt2="SELECT * FROM " & DBTableName & " WHERE PUMPER IS NOT NULL OR PUMPER ='' "
    cursor2=SQL1.EXECQUERY(txt2)
   
    Dim J As Int=900
    For i= 0 To Cursor1.rowcount -1
        Cursor1.position=i
        MyPumper=Cursor1.getstring("PUMPER")
        For  k=0 To cursor2.rowcount-1
            cursor2.position=k
            MyInitials=cursor2.getstring("PUMPER")
            If MyInitials=MyPumper    Then
                txt="UPDATE " &  DBTableName &" SET PUMPER = ? WHERE PUMPER=?"
                SQL1.ExecNonQuery2(txt, Array As Object(J+i, MyInitials ) )
            End If
        Next
    Next
    Cursor1.close
    cursor2.close

End Sub

 

[DonManfred]

If there is a jdbc driver for msaccess then you can use jRDC2 to connect to your database.

 

[Mahares]

If there is a jdbc driver for msaccess then you can use jRDC2 to connect to your database.

No. This is an independent Access database with its own table. I just want the equivalent code on how to do it in Microsoft Access. I am not using B4A to interact with Access. I am looking for Access programmers' help.
Thanks

 

[Computersmith64]

As far as the queries go, I would have thought it should pretty much work - except that you can't use:

"SELECT *..."

in Access. You have to use:

"SELECT ALL..."

- Colin.

 

[Computersmith64]

Actually, I think there's an error in your queries. If you are wanting to select records that are not null & are not blank (''), then you want:

txt="SELECT DISTINCT PUMPER FROM " & DBTableName & " WHERE PUMPER IS NOT NULL AND PUMPER !='' "

 txt2="SELECT * FROM " & DBTableName & " WHERE PUMPER IS NOT NULL AND PUMPER !='' "

- Colin.

 

[fixit30]

txt="SELECT DISTINCT PUMPER FROM " & DBTableName & " WHERE PUMPER IS NOT NULL AND PUMPER !='' "

txt2="SELECT * FROM " & DBTableName & " WHERE PUMPER IS NOT NULL AND PUMPER !='' "

Sorry, your syntax is wrong...

It should be

txt="SELECT DISTINCT PUMPER FROM " & DBTableName & " WHERE PUMPER IS NOT NULL OR PUMPER <>''"

txt2="SELECT * FROM " & DBTableName & " WHERE PUMPER IS NOT NULL OR PUMPER <>''"

USE <> Instead of != and use OR instead of AND

 

[Computersmith64]

Sorry, your syntax is wrong...

It should be[...]

Nope - both "!=" & "<>" are usable operators for not equal in SQLite - https://sqlite.org/lang_expr.html

- Colin.

 

[Mahares]

I am not looking for someone to debug my B4A code. It works quite well in my B4A project. I am looking for a knowledgeable Microsoft Access VBA programmer to give me the equivalent code in VBA. That is all.

 

[fixit30]

Nope - both "!=" & "<>" are usable operators for not equal in SQLite - https://sqlite.org/lang_expr.html

Sorry, but this thread is talking about MS Access, not SQLite.

 

[Computersmith64]

Sorry, but this thread is talking about MS Access!

Sorry - but if you read the OP, the original code is written in B4A using SQLite.

- Colin.

 

[Computersmith64]

I am not looking for someone to debug my B4A code. It works quite well in my B4A project. I am looking for a knowledgeable Microsoft Access VBA programmer to give me the equivalent code in VBA. That is all.

No worries - if you want your buggy SQLite query to be carried over to Access, I'll leave you to it.

- Colin.

 

[fixit30]

Yes, but asking for a VBA alternative, which has nothing to do with SQLite.

 

[fixit30]

No worries - if you want your buggy SQLite query to be carried over to Access, I'll leave you to it.

As I see it, there are no bugs in his B4A code, as he said, it works 'very well' YOU introduced bugs in your answer.

 

[Computersmith64]

Yes, but asking for a VBA alternative, which has nothing to do with SQLite.

Yes - but I was pointing out that there is a bug in the SQLite query, which if not fixed will be carried over to Access. The way the query is written now, it will select records that are not null or that are empty.

Considering that Mahares uses the DISTINCT clause, it seems likely that this is an error. If it's not an error, then the "PUMPER = ''" is redundant, as (one) empty row will be selected anyway.

- Colin.

 

[Computersmith64]

As I see it, there are no bugs in his B4A code, as he said, it works 'very well' YOU introduced bugs in your answer.

OK - good luck with writing queries that work then! The only reason his query works is probably because there are no empty rows in either table. In fact, his entire ConvertPumperToNumber sub could be replaced by a single query - both in B4A & Access. But I'm sure you knew that already & will no doubt be able to show Mahares how!

- Colin.

 

[Mahares]

Thank you guys for debating the subject, but that is not the point of the thread. Let me emphasize again that I need my code in post #1 translated from B4A to Access VBA. Could you please give some guru in Access a chance to help me out.

 

[hbs]

Hi Mahares,

This access VBA code should do what you want. Replace "Table1" wtih your actual tablename.

Sub ConvertPumperToNumber()

    Dim rsPumperList As Recordset, intPumperNo As Integer
    Dim strTableName As String
   
    strTableName = "Table1"
      
    intPumperNo = 900
   
    Set rsPumperList = CurrentDb.OpenRecordset("SELECT PUMPER FROM " & strTableName & " GROUP BY PUMPER HAVING (((PUMPER) Is Not Null)) OR (((PUMPER)<>'')) ORDER BY PUMPER")
   
    While Not rsPumperList.EOF
        CurrentDb.Execute ("Update " & strTableName & " SET PUMPER = '" & Trim(Str(intPumperNo)) & "' WHERE PUMPER = '" & rsPumperList.Fields("PUMPER") & "'")
        intPumperNo = intPumperNo + 1
        rsPumperList.MoveNext
    Wend
    rsPumperList.Close
      
End Sub

Regards

Peter

 

[Mahares]

This access VBA code should do what you want. Replace "Table1" wtih your actual tablename

Hello Peter:
Your code is impeccable. It is exactly what I was hunting for.
Thank you