Android Question Convert B4A Code To Microsoft Access VB

Mahares · Aug 17, 2017

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

B4X:

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 · Aug 17, 2017

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

Mahares · Aug 17, 2017

DonManfred said:
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 · Aug 17, 2017

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

B4X:

"SELECT *..."

in Access. You have to use:

B4X:

"SELECT ALL..."

- Colin.

Computersmith64 · Aug 17, 2017

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:

B4X:

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 · Aug 17, 2017

Computersmith64 said:
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

B4X:

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 · Aug 17, 2017

fixit30 said:
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 · Aug 17, 2017

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 · Aug 17, 2017

Computersmith64 said:
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 · Aug 17, 2017

fixit30 said:
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 · Aug 17, 2017

Mahares said:
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 · Aug 17, 2017

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

fixit30 · Aug 17, 2017

Computersmith64 said:
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 · Aug 17, 2017

fixit30 said:
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 · Aug 17, 2017

fixit30 said:
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 · Aug 17, 2017

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 · Aug 18, 2017

Hi Mahares,

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

B4X:

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 · Aug 18, 2017

hbs said:
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

Android Question Convert B4A Code To Microsoft Access VB

Mahares

Expert

DonManfred

Expert

Mahares

Expert

Computersmith64

Well-Known Member

Computersmith64

Well-Known Member

fixit30

Active Member

Computersmith64

Well-Known Member

Mahares

Expert

fixit30

Active Member

Computersmith64

Well-Known Member

Computersmith64

Well-Known Member

fixit30

Active Member

fixit30

Active Member

Computersmith64

Well-Known Member

Computersmith64

Well-Known Member

Mahares

Expert

hbs

New Member

Mahares

Expert

Similar Threads

Android Question Convert B4A Code To Microsoft Access VB

Expert

Expert

Expert

Well-Known Member

Well-Known Member

Active Member

Well-Known Member

Expert

Active Member

Well-Known Member

Well-Known Member

Active Member

Active Member

Well-Known Member

Well-Known Member

Expert

New Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency