Mdb files as datasource with ucanaccess

I use ucanaccess driver to get mdb files (access) as datasource. (and Lucee
4.5 or 5)
All work well. (Under Windows server 2008)

Except that I cannot make a replacement of that mdb file when declared as
datasource. (to come with an additionnal table for example)
I have to stop Lucee to manipulate that mdb file.
Then all sites are down in that time.
I could do so with CF9. It was very flexible.

Is there a way to allow that mdb file manipulation even if it is a
datasource ?
With the datasource parameters ? or is this a user priviledge question ?

Thanks for any help and suggestions.
Pierre

No help yet on this question.
Because of this problem, I can’t migrate the application from CF9 to Lucee.
During the process I have to replace one .mdb file to make a reset of the database.
As the mdb is a datasource, this replacement is not permitted as far as Lucee is running.
If I stop Lucee, I can replace the mdb. I need to do that while Lucee is up and running.

Thanks for any advise.
This application use Access mdb, because, it is for few users (around 10)
because data storage will be less than 2Gb (also I use 4 differents mdb)
and because it is very flexible.
(more flexible than MySQL or SQLserver)

Thanks for help and ideas, Pierre.

I forgot to ask if the problem comes from Lucee or from the ucanaccess driver ?
This problem does not exist under CF.
Thanks, Pierre.

It’s very unlikely someone can answer that definitively for you. I would guess very few people are using the ucanaccess driver. However, the filename is only in the jdbc driver definition, so there’s no way it could be Lucee - Lucee is just calling the JDBC driver using a standard API.

I will say it makes perfect sense to me that you can’t change a mdb file out from under a datasource while Lucee is running… Windows file locking would prevent such things. Plus, how can you ensure data consistency if you could have a web user accessing a mdb, and you can replace the entire database at the same time? How would you ever know what was saved and what was correct? Or is the mdb read only?

I would look at doing something more application consistent. Define your datasource in application.cfc instead of the administrator, and make the filename configurable in your application. Create an application reinit or reload URL variable that changes the MDB the application is referencing. Then new requests will use the new file and old ones can drain safely. (So use an application variable or modify the datasource in the administrator) Then you can switch back and forth between database1.mdb and database2.mdb, for example. Or create an atomic block with a cflock exclusive, shut down the datasource, replace the file and restart the datasource in code - such that you can be sure no requests are running.

Also, Lucee reloads pretty quickly - shutdown, replace file, restart shouldn’t take more than 4-5 seconds…

Again, without knowing more about your application it’s hard to have more specific recommendations.

You may want to review the JDBC options and make sure you’re using the same ones as CF9
http://ucanaccess.sourceforge.net/site.html#examples

Also notice there are various options in there - exclusive access for instance, that would cause this… And when you say replace - can’t you just open the mdb with a shared lock by opening it in Access and adding your table? Or can’t you just run a create statement from Lucee?

Thanks a lot for your cooperation and advise,

This is possible Under Windows and ColdFusion, but it is true that when I do this replacement, there no lock (.ldb) file with the mdb file. The application is closed, no user access.

I will try what you say "shut down the datasource, replace the file and restart the datasource in code ",
all should be done in code. No user can access to file management and administrator.

Also, I thought, I could drop the tables and recreate them in the same datasource.
The aim is to have empty tables with same structure, and by the way reset the records IDs to 0.
(restart primary key at value 0)
Hope this can be done over ucanaccess driver.

Thanks a lot for your comment.
Pierre.

I did a fair bit of Access programming back in the olden days (15 years ago). A common pattern then to allow for multi-user databases (on a network share) and to prevent locking would be to separate an application into two mdb’s - one for logic/ui/forms, and one for all the data - and then let the frontend database link to the tables in the backend database. I had code to manage and recreate those links I think (VB) - is this something you could try?

To continue on that thread - Access nowadays can work as a frontend to a full RDBMS, such as MSSQL or MySQL. If you want to keep forms etc in Access, you could consider going down that route, and have a modern, scalable and robust backend to support both web and Access clients.

Pierre Larde wrote on 2017-05-31:

https://dev.lucee.org/letter_avatar_proxy/v2/letter/p/67e7ee/45.png
Pierre_Larde
<https://mandrillapp.com/track/click/30000072/dev.lucee.org?p=eyJzIjoid3
NJM0c2OV85ak1PdkZNWXZWRkxpS2NIREFJIiwidiI6MSwicCI6IntcInVcIjozMDAwMDA3Mi
xcInZcIjoxLFwidXJsXCI6XCJodHRwczpcXFwvXFxcL2Rldi5sdWNlZS5vcmdcXFwvdVxcXC
9waWVycmVfbGFyZGVcIixcImlkXCI6XCI4MTMzZjA0NzI3NmI0NjE3YTMwMWY4MTVhOTkzZT
FiZFwiLFwidXJsX2lkc1wiOltcIjM1NjliMjY4ZjI4NjNiMGNiMzI4NjM2ZjQ0NWM5YTkwNT
FhMzZkNmZcIl19In0> May 31

No help yet on this question. Because of this problem, I can’t migrate
the application from CF9 to Lucee. During the process I have to replace
one .mdb file to make a reset of the database. As the mdb is a
datasource, this replacement is not permitted as far as Lucee is
running. If I stop Lucee, I can replace the mdb. I need to do that while
Lucee is up and running.

Thanks for any advise. This application use Access mdb, because, it is
for few users (around 10) because data storage will be less than 2Gb
(also I use 4 differents mdb) and because it is very flexible. (more
flexible than MySQL or SQLserver)

Thanks for help and ideas, Pierre.


Visit Topic
<https://mandrillapp.com/track/click/30000072/dev.lucee.org?p=eyJzIjoiWl
BUUTVMOW4wMFpXbGhaN2dPMmZZSmJfWXc4IiwidiI6MSwicCI6IntcInVcIjozMDAwMDA3Mi
xcInZcIjoxLFwidXJsXCI6XCJodHRwczpcXFwvXFxcL2Rldi5sdWNlZS5vcmdcXFwvdFxcXC
9tZGItZmlsZXMtYXMtZGF0YXNvdXJjZS13aXRoLXVjYW5hY2Nlc3NcXFwvMTMyM1xcXC8yXC
IsXCJpZFwiOlwiODEzM2YwNDcyNzZiNDYxN2EzMDFmODE1YTk5M2UxYmRcIixcInVybF9pZH
NcIjpbXCI0MDBkM2ViZWRmNTlkZjFmNjgyMjAyMDdlMDE2N2YxYjU3ZWVmNjNhXCJdfSJ9>
or reply to this email to respond.


Previous Replies

https://dev.lucee.org/letter_avatar_proxy/v2/letter/p/67e7ee/45.png
Pierre_Larde
<https://mandrillapp.com/track/click/30000072/dev.lucee.org?p=eyJzIjoid3
NJM0c2OV85ak1PdkZNWXZWRkxpS2NIREFJIiwidiI6MSwicCI6IntcInVcIjozMDAwMDA3Mi
xcInZcIjoxLFwidXJsXCI6XCJodHRwczpcXFwvXFxcL2Rldi5sdWNlZS5vcmdcXFwvdVxcXC
9waWVycmVfbGFyZGVcIixcImlkXCI6XCI4MTMzZjA0NzI3NmI0NjE3YTMwMWY4MTVhOTkzZT
FiZFwiLFwidXJsX2lkc1wiOltcIjM1NjliMjY4ZjI4NjNiMGNiMzI4NjM2ZjQ0NWM5YTkwNT
FhMzZkNmZcIl19In0> November 1

I use ucanaccess driver to get mdb files (access) as datasource. (and
Lucee 4.5 or 5) All work well. (Under Windows server 2008)

Except that I cannot make a replacement of that mdb file when declared
as datasource. (to come with an additionnal table for example) I have to
stop Lucee to manipulate that mdb file. Then all sites are down in that
time. I could do so with CF9. It was very flexible.

Is there a way to allow that mdb file manipulation even if it is a
datasource ? With the datasource parameters ? or is this a user
priviledge question ?

Thanks for any help and suggestions.
Pierre


Visit Topic
<https://mandrillapp.com/track/click/30000072/dev.lucee.org?p=eyJzIjoiWl
BUUTVMOW4wMFpXbGhaN2dPMmZZSmJfWXc4IiwidiI6MSwicCI6IntcInVcIjozMDAwMDA3Mi
xcInZcIjoxLFwidXJsXCI6XCJodHRwczpcXFwvXFxcL2Rldi5sdWNlZS5vcmdcXFwvdFxcXC
9tZGItZmlsZXMtYXMtZGF0YXNvdXJjZS13aXRoLXVjYW5hY2Nlc3NcXFwvMTMyM1xcXC8yXC
IsXCJpZFwiOlwiODEzM2YwNDcyNzZiNDYxN2EzMDFmODE1YTk5M2UxYmRcIixcInVybF9pZH
NcIjpbXCI0MDBkM2ViZWRmNTlkZjFmNjgyMjAyMDdlMDE2N2YxYjU3ZWVmNjNhXCJdfSJ9>
or reply to this email to respond.

You are receiving this because you enabled mailing list mode.

To unsubscribe from these emails, click here
<https://mandrillapp.com/track/click/30000072/dev.lucee.org?p=eyJzIjoiek
VnTDFnZWJhcm9mNUNQNU4tekJOa1Uza0xjIiwidiI6MSwicCI6IntcInVcIjozMDAwMDA3Mi
xcInZcIjoxLFwidXJsXCI6XCJodHRwczpcXFwvXFxcL2Rldi5sdWNlZS5vcmdcXFwvZW1haW
xcXFwvdW5zdWJzY3JpYmVcXFwvYmJlM2EwNDM2MmI5NDA0ODY1MjZjNWEzOGU5MTU2NGRkYm
IwMmM4MzA5NDg4NGJjYmVkYTdhOThlMDkxMmVjY1wiLFwiaWRcIjpcIjgxMzNmMDQ3Mjc2Yj
Q2MTdhMzAxZjgxNWE5OTNlMWJkXCIsXCJ1cmxfaWRzXCI6W1wiZmQxYzU0ODk4MzlmN2E1N2
IyMWQ0YzgyMTkyZGE5NTE2ZjgzOTczZlwiXX0ifQ> .

<https://mandrillapp.com/track/open.php?u=30000072&id=8133f047276b4617a3
01f815a993e1bd>

Thanks for your help.
All screens/forms UIs are written in CFML.
Acces database are only used as datasources. (no logic and UI)
Only mdb files ad databases.
Pierre.

Then you should consider using a different database engine, like H2 or HyperSQL (many other options). These are small, light, and allow for much more flexibility than Access IMO.

1 Like

Thanks, I need a very flexible database. For me, mdb files are great.
They are almost like a text file. Easy to transport, easy to modify strucuture, easy to manipulate,
easy to backup, etc… (2Go of data is enough for my need, and I use multi mdb files)
I will look over on H2 and HyperSQL.
I do not know about them. Can they be datasource in Lucee ?
Could be the files I need.
Thanks, again for your interest.
Pierre.

Of course, or else I wouldn’t recommend them in the Lucee dev forum :wink:

The nice thing is that, you can run these database engines in either “file mode” – which is similar to how you use Access, or in “server mode” – which will allow multiple clients to connect at the same time.

See
http://www.h2database.com/html/tutorial.html#using_server

http://hsqldb.org/doc/2.0/guide/running-chapt.html#rgc_server_modes

Could you give me some help,
I just installed H2 database.

In my application, if want to replace Access mdb files by H2.
I have 4 mdb files for the Web application (and 4 Lucee datasource),
I need to seperate them, because one is very big, others are small independant files.
How can I get H2 to get 4 equivalent files and be able to copy/paste when I need to ?

Or is H2 a single database, where all tables have to be in there ?

Thanks for guidance.
Pierre.

H2 is a database engine, which can be used as a database server.

You can have multiple schemas, multiple databases, etc., so you would probably want 4 different databases in this case.

From http://www.h2database.com/html/quickstart.html

  • The database URL jdbc:h2:~/test opens the database test in your user home directory
  • A new database is automatically created

You should really refer to the H2 documentation and mailing list though:

http://www.h2database.com/html/main.html

https://groups.google.com/forum/#!forum/h2-database