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 ?
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)
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.
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?
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.
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.
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.
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> .
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.
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
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.
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 ?
