Android Question Help With Jackcess FindFirstRowByEntry

MrKim

Well-Known Member
Licensed User
Longtime User
I can get FindClosestRowByEntry to work fine but FindFirstRowByEntry will not find anything.

B4X:
Dim Access AsJackcessDatabase, Cur AsJackcessIndexCursor, Ind AsJackcessIndex, Tbl AsJackcessTable
Dim Found AsBoolean, Test AsString, IndVals(2) AsObject, Test2 AsString
Access.Open(File.DirInternal & "/PartsAndTime.accde")
Tbl.Initialize(Access.GetTable("OPsLookups"))
Ind.Initialize(Tbl.GetIndex("JobRel"))
Tbl.Reset
Cur.Initialize(Tbl.GetTable, Ind.GetIndex)
 
IndVals(0)="8862"
IndVals(1)=2
 
Found = Cur.FindFirstRowByEntry (IndVals) 'DOES NOT WORK
Cur.FindClosestRowByEntry(IndVals  'WORKS

Here is some of the data:



Here are my access indexes:



FindClosestRowByEntry leaves me right at Os_JobNum='8862' AND Os_ReleaseNum=2

FindFirstRowByEntry does not return a row.

Any help is appreciated.
 

Attachments

  • OpsLookups2.jpg
    OpsLookups2.jpg
    41.8 KB · Views: 298
  • OpsLookupsIndexes.jpg
    OpsLookupsIndexes.jpg
    29.2 KB · Views: 307

MrKim

Well-Known Member
Licensed User
Longtime User
For those playing around with Jackcess: Ultimately, for my needs, which are not horribly complex, I still found it simpler to just port my data to SQLite and then use it. Because of the overhead of an Access DB I also decided to bring the data in as raw text using my own Table/Record/Field separators. I used the VBA Print method to print the data to a file copy it to my Android, and use the incredible Regex.Split to parse the data back out and then insert it in to my SQLite tables. I can copy and recreate about 1500 records 150k data in under 2 seconds with a good Wireless connection.

In case it might be helpful to someone else I have posted a portion of the code below:
This is not all that is need to make this work.
I use the SMB lib to transfer the file.

In Access:

B4X:
TableSep = "!T&B%L!"
RecordSep = "!R&E%C!"
FieldSep = "!F&L%D!"
 
 
Set RS = DB.OpenRecordset("SELECT Emp_Num, Emp_FirstName, Emp_LastName, Emp_AllowEdits, Emp_Rate FROM Employees WHERE (Emp_Inactive=False);", dbOpenSnapshot)
FNum = FreeFile
Open TablePath & "PartsAndTimeTemp" For Output As #FNum
'Print #FNum, TableSep;
With RS
    If .RecordCount > 0 Then
        .MoveFirst
        Do While Not .EOF
            Print #FNum, ![Emp_Num] & FieldSep & ![Emp_FirstName] & FieldSep & ![Emp_LastName] & FieldSep & IIf(![Emp_AllowEdits], -1, 0) & FieldSep & ![Emp_Rate] & FieldSep & RecordSep;
            .MoveNext
        Loop
    Else
        Print #FNum, RecordSep & FieldSep & "NoData" & FieldSep;
    End If
End With
Print #FNum, TableSep;

In B4A:

B4X:
Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    'Dim  ObscureSeperator = "%~#&@#!%"
    'Dim Obscure2 = "&" &  "%~#&@#!%" & "&"
    Dim TableSep As String    :  TableSep = "!T&B%L!"  'SPECIAL CHARACTERS DO NOT USE!  \ ^ ${ } [ ] ( ) . + ? | - &
    Dim RecordSep As String  :  RecordSep = "!R&E%C!"  '  \ is the escape char and the char after it is the literal
    Dim FieldSep As String    :  FieldSep = "!F&L%D!"
End Sub
 
 
Sub GetData_Click
Dim Tbls() As String, Rec() As String, fld() As String, RecX As Int, TblX As Long, FldX As Long, Ftxt As String, Test As Long
    Ftxt=File.ReadString( File.DirInternal, FileToGet.text)
    If Main.SQL1.IsInitialized = False Then Main.SQL1.Initialize(File.DirInternal, "PartsAndTime.db", False)
     Main.SQL1.ExecNonQuery("DELETE FROM EmpInfoLookups;")
     Main.SQL1.BeginTransaction
     Tbls=Regex.Split(TableSep, Ftxt)
        TblX=0
            Rec=Regex.Split(RecordSep,Tbls(TblX))
            For RecX = 0 To Rec.Length - 1
                fld=Regex.Split(FieldSep,Rec(RecX))
                Main.SQL1.ExecNonQuery2("INSERT INTO EmpInfoLookups  VALUES(?, ?, ?, ?, ?)",  Array As Object(fld(0), fld(1), fld(2), fld(3), fld(4) ) )
            Next
        Main.SQL1.TransactionSuccessful
        Main.SQL1.EndTransaction
        Dim Cur As Cursor
        Cur = Main.SQL1.ExecQuery("SELECT * FROM EmpInfoLookups;")
        Cur.Position=Cur.RowCount-1
        For FldX=0 To 4
            Log(Cur.GetColumnName(FldX) & " = " & Cur.GetString(Cur.GetColumnName(FldX)))
        Next
End Sub

This is the Text file I used to create the SQLite DB:
Note I found it very easy to install and use SQLite in windows, then copy the DB to my android. Tip: Copy SQLite3.exe to whatever folder you are working in. You can skip copying long paths.

B4X:
DROP TABLE EmpInfoLookups;
 
CREATE TABLE EmpInfoLookups (Emp_Num TEXT PRIMARY KEY NOT NULL, Emp_FirstName TEXT, Emp_LastName TEXT, Emp_AllowEdits INT, Emp_Rate TEXT);
 
Last edited:
Upvote 0
Top