B4J Question B4J and Database ACCESS ?

[marco.canta]

Hi, I would like to know if and how you can manage database 'Access' MDB file with B4J.

Thanks
Marco

 

[Johan Hormaza]

Does not open the '.accdb' files?
Thank you

 

[panoss]

You 'll have to change the: DBFile = File.DirApp & "\" & "cars.mdb"
at Sub Database_Init.
(in the zip file, the mdb is in the Data directory)

 

[rwblinn]

Does not open the '.accdb' files?

As a starter tested the example only with the (old format) cars.mdb database.

Did another test, using the newer format .accdb using this sample database Finance.

DBFile = File.DirApp & "\" & "Finance.accdb"
...
gSQL.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & DBFile & ";showSchema=true")
...
DBUtils.ExecuteTableView(gSQL, "SELECT * FROM Currencies", Null, 0, TableView_Main)

Content table Currencies from the Finance database:

Note
I am not using MS Access, thus my knowledge is limited for this subject - mainly used the UCanAccess site for reference.

 

[Johan Hormaza]

Thanks @rwblinn Without doubts, you are one of the best ones in which I follow a lot in this blog.

 

[panoss]

I want to make some modifications to the project.
When I click on a row in the tableview, I want the current record to become the clicked one.
So that I can move to previous and next record and highlight the current row.
(and display the model of the current car in a text box)
How can I do this?

 

[Johan Hormaza]

I want to make some modifications to the project.
When I click on a row in the tableview, I want the current record to become the clicked one.
So that I can move to previous and next record and highlight the current row.
(and display the model of the current car in a text box)
How can I do this?

Here is an example, any doubt that you tell me

 

[panoss]

Thank you Johan, it 's close.
I 'm thinking of some mods:
1. first column of tvwMain will contain data of ID column
2. on the event tvwMain_SelectedCellChanged I 'll make a query to the db to fetch me the record with ID equal to the value of the first cell of the selected row.

Unless there 's a better way...

 

[CR95]

I downloaded B4JHowToAccess from #7 and I am testing MSAccess application with B4J 9.5 (64 bits)
As specified, I loaded the driver and the additional JARS in my Additional Libraries Folder "C:\Program Files\Anywhere Software\Additional Libraries" (as declared in the link)
But when compiling, I get an issue :"Impossible to find : C:\Program Files\Anywhere Software\B4J\libraries\ucanaccess-2.0.2.jar....
It seems that the compiler does not look for the addtional elements in the good place ???
Do you have an idea what can be done ?

 

[DonManfred]

in my Additional Libraries Folder "C:\Program Files\Anywhere Software\Additional Libraries"

this is 100% the wrong path.

The additional Libraryfolder should not be inside programfiles as it is an restricted folder.

 

[CR95]

Thanks DonManfred
I changed the additional Libraryfolder and set B4J link as C:\Users\CR\Documents\Additional Libraries
But the compilation error remains : :"Impossible to find : C:\Program Files\Anywhere Software\B4J\libraries\ucanaccess-2.0.2.jar....
FYI, B4J 64 bits is installed in "C:\Program Files\Anywhere Software\B4J\"

 

[jerry07]

If it is not finding this file in your C:\Users\CR\Documents\Additional Libraries it will report as
Impossible to find : C:\Program Files\Anywhere Software\B4J\libraries\ucanaccess-2.0.2.jar....
this is likely because it looks at multiple paths and is reporting last or default location.

I think you are running into this issue because in your code you are referring to #AdditionalJar: ucanaccess-2.0.2.jar
but your library is different version, for example C:\Program Files\Anywhere Software\B4J\libraries\ucanaccess-5.0.1.jar
Make sure code matches file exactly including version.

Ignore the path that is reporting in error msg if you have right file in Additional Library that is matching file referenced in code it will work.

 

[CR95]

@jerry07

Make sure code matches file exactly including version.

Yes. It works now.
Thanks for your help

 

[CR95]

Using B4JHowToAccess from #7, the driver connection is now successful
Unfortunately, after the first
DBUtils.ExecuteTableView(gSQL, "SELECT * FROM " & CurDBName , Null, 0, TableView_Main)
wites following error in debug window

org.hsqldb.HsqlException: user lacks privilege or object not found: MDB

As my MDB file is found by the driver connection, I guess that there is a Passsword issue.
Do you know if there is a way to send a password to the database (I suppose when connecting) ?

 

[jerry07]

Is your .mdb file protected by password?

If it is take look at this post: https://www.b4x.com/android/forum/threads/opening-ms-access-databases-mdb.107963/#content


EDIT:
BTW this dose not look right -> "DBUtils.ExecuteTableView(gSQL, "SELECT * FROM " & CurDBName , Null, 0, TableView_Main)"
"CurDBName" indicates your mdb file name however this should be your table name, this is likely why is not finding you object. Also I don't like the concatenation here, look up Smart String Literal.

When you put in code

Log(CurDBName)

What do you see in Logs window?

 

[CR95]

Thanks jerry07
You are right. I started from the "cars.mdb" example where the name of the database AND the name of the table are the same.
By putting my correct name of the table in the SELECT request, it works fine. This is not a password issue.
I will now be able to move this Access old DataBase to a recent B4J application.
Thanks also to rwblinn for his original 2014 work which is still operational in 2022