B4J Question JSQL and UcanAccess

[EnriqueGonzalez]

Good day!

I am working with Access and B4J via UcanAccess, so far so good, everything works with the SQL class provided in JSQL library, the only thing i cannot make it work is TransactionSuccessful method, it is funny because the BeginTransaction actually is doing the job, but because even when the code arrives to TransactionSuccessful, the data is not commited.

According to UcanAccess you have to do something like this:

Connection conn=DriverManager.getConnection("jdbc:ucanaccess://<mdb or accdb file path>",user, password);

conn.setAutoCommit(false); 
st =this.ucaConn.createStatement();
st.executeUpdate("update example4 set descr='Lugo di Romagna'"); 
st.execute("insert into example4 (ID, descr) values(5,'DALLAS')"); 
conn.commit();

i think it would be easy to "Extract" the connection from the SQL class like with JavaObject and again with JO make the commit, but i do not know how to do that.

Thanks for reading all the way through here!! and for helping me!

 

[Erel]

Why are you using access as a db engine? Why not use a proper db engine such as SQLite?

What happens if you don't explicitly start the transaction?

 

[EnriqueGonzalez]

Thanks Erel.

I am afraid that for this project I am stuck with access.

If I do not set up a transaction everything works fine, all the changes are commited

 

[EnriqueGonzalez]

At the end i managed to call commit with a bit of inline java, thanks anyway for your help!

 

[jmon]

(this post is old, but I've got the answer )

Just call:

sq.ExecNonQuery("COMMIT;")

after every transactions. It will save your data.

source: https://msdn.microsoft.com/en-us/library/ms190295.aspx

 

[EnriqueGonzalez]

Thank you jmon!

my bad, that i did not write the answer here for other people to know!

My solution was a bit different, with javaobject i called a small function in inline java running the method of SQL.

I was so naive back then! jeje

 

[rgarnett1955]

Hi Erel,

Nothing wrong with MS Access. Out of all the products MS make it's the only one that i like. Unlike sqLite, MS Access is true multi user and with record locking. It provides transactional database functionality at the VBA level.

For small businesses with less than 100 users MS Access is low cost and with VBA using record-sets it is very easy to program quite complex functions.

MS Access is used as the database engine for process tag management at Loy Yang B Power Station and manages about 40,000 tags each with related tables for most of the fields. We also used it in the business system for budgeting, with about 30 users on at any-one time. All developed in-house by power station engineers, not expensive IT experts. The accountants replaced it with a proprietary system costing over three hundred thousand which trashed all of the intrinsic MS Access functionality like filters and on the fly queries and reports, so that instead of taking one screen to find a project it took 14.

The engineering personnel went back to using Excel spread sheets for their budgeting activities. Right where they started.

We also used MS Access for downloading plant historian data with millions of records of up to 100 double float fields.

Professional database programmers hate MS Access because it doesn't make them any money!

 

[amorosik]

Hi Erel,

Nothing wrong with MS Access. Out of all the products MS make it's the only one that i like. Unlike sqLite, MS Access is true multi user and with record locking. It provides transactional database functionality at the VBA level.

For small businesses with less than 100 users MS Access is low cost and with VBA using record-sets it is very easy to program quite complex functions.

MS Access is used as the database engine for process tag management at Loy Yang B Power Station and manages about 40,000 tags each with related tables for most of the fields. We also used it in the business system for budgeting, with about 30 users on at any-one time. All developed in-house by power station engineers, not expensive IT experts. The accountants replaced it with a proprietary system costing over three hundred thousand which trashed all of the intrinsic MS Access functionality like filters and on the fly queries and reports, so that instead of taking one screen to find a project it took 14.

The engineering personnel went back to using Excel spread sheets for their budgeting activities. Right where they started.

We also used MS Access for downloading plant historian data with millions of records of up to 100 double float fields.

Professional database programmers hate MS Access because it doesn't make them any money!

I have been using MsAccess for at least 20 years to carry out procedures of all kinds, and I continue to use it to this day
And based on my experience I can say that I would never, ever use an mdb or accdb file to keep the main data of any of my clients, even if it were a single-user system
For multi-user systems, this is not even mentioned
I believe that the fragility of a multi-user system based on Microsoft Access data files is known to all
Nobody questions the effectiveness (and performance) of Access for building desktop applications and used by a single user
But for a multi-user environment, which must guarantee reliability in the maintenance of information, keeping the data on mdb or accdb files means going in search of harming yourself
Just one consideration is enough, any db server can perform hot backups, without interrupting the users' activity
How do you do the same thing on an mdb or accdb based system?
It is not true that professional programmers hate Access, there are many groups, with experienced professional operators, who discuss issues and offer / receive technical support
The reality is that you have to use a tool for what it can do best, and in the case of Access the effectiveness in creating the front-end is undeniable
As it is undeniable that it is better to keep data on Sql Server, Oracle, Db2, Informix, Postregsql, Firebird, MySql, etc ..

Rather it would be to understand why Access is not used to connect to the data of the main database, that of the accountants
Allowing calculations, reports, graphics that for other users (engineering personnel) are more comfortable

 

[michaelleewebb]

i've been running msaccess programs for at least 20 years. never ever lost any data.

"And based on my experience I can say that I would never, ever use an mdb or accdb file to keep the main data of any of my clients, even if it were a single-user system".
what happened ?? do you have a story to tell ?

"I believe that the fragility of a multi-user system based on Microsoft Access data files is known to all"
not me.

"It is not true that professional programmers hate Access"
9 out of 10 times their advice is: get off msaccess, use <add database here>.

sorry for the rant, its just msaccess suffers the same fate as visual basic, its to easy to use. pro's do hate it, 9 times out of 10, about the same for visual basic.

 

[amorosik]

i've been running msaccess programs for at least 20 years. never ever lost any data.

"And based on my experience I can say that I would never, ever use an mdb or accdb file to keep the main data of any of my clients, even if it were a single-user system".
what happened ?? do you have a story to tell ?

"I believe that the fragility of a multi-user system based on Microsoft Access data files is known to all"
not me.

"It is not true that professional programmers hate Access"
9 out of 10 times their advice is: get off msaccess, use <add database here>.

sorry for the rant, its just msaccess suffers the same fate as visual basic, its to easy to use. pro's do hate it, 9 times out of 10, about the same for visual basic.

Get off msaccess, use Sql Server
Get off msaccess, use Oracle
Get off msaccess, use Ibm Db2

The three statements above, if said by a professional programmer, I believe make him less authoritative than when he had not yet expressed them
Microsoft Access is not just a 'database system'
It is a development environment that integrates the functionality of a 'database system' and those of an integrated development environment with user interface, report, query, and pure code modules
What I wrote in previous posts is that the 'database system' part of Access, in my opinion, is not suitable for supporting demanding workloads like today's professional environments.
While the part relating to the creation of forms, reports, queries, code modules, ensures unparalleled effectiveness compared to classic development environments
And is able to generate sufficiently reliable procedures to be used in most professional environments in any field they are

 

[michaelleewebb]

i think we agree to a point. msaccess jet database system has limits. i guess i've just never been exposed to the demanding workloads of todays professional environments. in my current environment i have 6 users on the database, transactions with the database occur every few minutes. on the development side it has well over 300 forms and 300 reports (as of 10 years ago). i guess what got me the most was when you said "And based on my experience I can say that I would never, ever use an mdb or accdb file to keep the main data of any of my clients". my experience has been theirs room for jet db on the bottom to the middle typical small office environments. their are lots of those.