B4J Question Help me... ucanaccess

[vmag]

Hello.
I'm trying to master your b4A and B4J products.
So far, I like everything very much, but there is one problem-
The B4J project works with the Access database only in demo mode...
What could be the reason?
You need Access!!!
For example, we have:
1. B4J.8.10 + Access *.mdb (2000 - 2003)
2. http://ucanaccess.sourceforge.net/site.html (Last 5.0.0)
3.

#AdditionalJar: ucanaccess-5.0.0.jar
#AdditionalJar: hsqldb-2.5.0.jar
#AdditionalJar: jackcess-3.0.1.jar
#AdditionalJar: commons-logging-1.2.jar
#AdditionalJar: commons-lang3-3.8.1.jar

4. Connecting:

DBFile = "C:\.........\cars.mdb" ' exclusively version 2000-2003
SQL.Initialize ("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess:// " & DBFile & "; memory=false")

5. Trying to delete the table:

SQL.BeginTransaction
Try
SQL.ExecNonQuery("DROP TABLE IF EXISTS [cars]")
Catch
Log(LastException.Message)
End Try
SQL.TransactionSuccessful
SQL.Rollback
SQL.ExecNonQuery("COMMIT")
SQL.Close

The program works without errors, but nothing happens in the database.
Moreover, if you perform actions with changes to table entries, everything also works,
but only on the screen, when the program is closed, there are no changes in the real database.
What could be the reason ?
If possible, send an example of a project that can actually delete a table in the mdb file (Access 2000-2003).
Thank you for any help!
This project download -> https://yadi.sk/d/-AyrAnO38lts8w

 

[Erel]

Please use [code]code here...[/code] tags when posting code.

Why are you calling: SQL.Rollback ???

 

[vmag]

Why are you calling: SQL.Rollback ???

can be removed, still does not affect the result

 

[Erel]

Remove all code and only keep this:

SQL.ExecNonQuery("DROP TABLE IF EXISTS [cars]")
SQL.Close

The B4J project works with the Access database only in demo mode...

What does it mean? Does it work properly in debug mode?

 

[vmag]

I did the code as You said-nothing has changed. Maybe ucanaccess works in demo mode ? What do you need for real work?

 

[Erel]

Tested with this code:

Dim SQL As SQL
SQL.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & DBFile & ";memory=false")
Dim rs As ResultSet = SQL.ExecQuery("SELECT * FROM information_schema.tables")
Do While rs.NextRow
    Log(rs.GetString("TABLE_NAME"))
Loop
rs.Close
SQL.ExecNonQuery("DROP TABLE CARS")
SQL.ExecNonQuery("DROP TABLE CARS1")
SQL.ExecNonQuery("DROP TABLE CARS2")
SQL.Close

The tables were dropped.

 

[Erel]

I've used different versions though it probably doesn't matter. You can see the versions that I used here: https://www.b4x.com/android/forum/threads/opening-ms-access-databases-mdb.107963/

 

[vmag]

Your code works really well!
Thank you very much!
I tried to figure out what the reason is and finally found out that the problem in SQL syntax - this is how DROP TABLE IF EXISTS-does not work.
Like this:

SQL.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & DBFile & ";memory=false")
SQL.ExecNonQuery("DROP TABLE CARS")

really works.
Thank you very much!

 

[vmag]

I've used different versions though it probably doesn't matter. You can see the versions that I used here: https://www.b4x.com/android/forum/threads/opening-ms-access-databases-mdb.107963/

Please provide the simplest examples for adding, changing, and deleting individual entries in the table, such as:

INSERT INTO CARS ( BRAND ) SELECT "volvo" AS model
UPDATE cars SET cars.BRAND = "Volvo-S70" WHERE (((cars.BRAND)="volvo"))
DELETE cars.BRAND FROM cars WHERE (((cars.BRAND)="volvo"))

Thank you so much for your help!

 

[Erel]

You should start a new thread for a new question.
Worth watching the SQL video tutorial: https://www.b4x.com/learn.html
Or the SQL programming guide.