7 Replies Latest reply on Mar 21, 2011 3:27 AM by plarts

    Unix and SQL and ACCESS database

    plarts Level 1

      I have

      a CF file working under Windows with 2 queries (on Access database) :


      1 - is a classical  Insert

      2 - is a select max ID of last insert


      This always worked under Windows platform.


      My Host changed the platform to UNIX :


      Now : I have a bug on the 2nd query (select max ID)

      message is : You are trying to access a closed table


      I put a cfabort after the 1st Query : no error

      but the record is not inserted.

      Then it seems that at 2nd query the table is not free up.


      The 1st query code is :

      <CFQUERY name="insert_art" datasource="#pres_datasource#">
           insert into article
           values(#form.option#,'#form.titre#','#n_contenu#',#form.ordre#,#createodbcdate(now())#,'# file_to_tele#',#form.album_id#,#session.user_id#)



      2nd query is :

      <cfquery name="get_last_id" datasource="#pres_datasource#">
           select max(art_id) as max_id from article


      Very simple.


      And I repeat, this only under UNIX.


      Any idea and help , thanks,

      My Host does not want to collaborate. And deos not want to go back on Windows.



        • 1. Re: Unix and SQL and ACCESS database
          Dave Watts Adobe Community Professional

          Access is not supported on Unix. You will need to port to another database platform like MySQL - which is a good idea anyway - or switch hosts.


          Dave Watts, CTO, Fig Leaf Software



          • 2. Re: Unix and SQL and ACCESS database
            plarts Level 1

            Thanks, good to know.

            It is only a simple MDB file, what a shame.

            So usefull, so flexible, several file to constitute a bigger DB, so easy to use, so easy to manipulate

            so easy to dowload/upload.


            It is strange, that most of the queries are fonctionnal on UNIX (with Access)

            - update, select, most of common SQL commands,

            and Insert does not work ?


            Thanks for any extra informations.


            • 3. Re: Unix and SQL and ACCESS database
              Adam Cameron. Level 5

              I've never looked at what the DataDirect JDBC drivers that ship with CF do, but for an Access DB to work, as well as the MDB file (which is just the data), you'd need the Jet DB Engine installed, as well as ODBC. Both of which are Windows products.


              You are mostly wrong in your assertions that it's just a simple file, that it's useful, flexible, easy to use and manipulate.  If you have exposure to other DB engines you will realise that Access is very much none of those (in comparison, where there is a comparison).


              That, and it's a single-user desktop product, and not intended to be used as a back-end for a client-server DB environment.

              Access if for creating DBs like "My CD Collection", and not much else.  Or perhaps for slapping a simple GUI onto a proper DB.


              It's not fit for purpose in a web environment (and nor did anyone at MS ever suggest it was, so no disrespect to them there).




              • 4. Re: Unix and SQL and ACCESS database
                Owain North Level 4

                I can honestly say you are the first person I have ever heard say anything good about Access databases


                As a platform for running an application from, it's just plain awful!

                • 5. Re: Unix and SQL and ACCESS database
                  plarts Level 1

                  I am very sorry, but I use Access (only the DB part, mdb file) for years and years,

                  I am very much satisfied. I use it inside ColdFusion, very easily.
                  I have look and used MySQL, I have abandonned. (not ergonomic at all, not flexible, lost of time)
                  I think you cannot imagine the gained time with the mdb files.

                  I speek for small businesses.


                  Or I use SQL Server for big business.


                  This is my opinion, and my way of working which bring me no problem at all never.

                  I just testing Access 2007, I pass now through the "ODBC socket" , but it is still very rapid to manage.


                  I think that the way of most of people, it not always the best way.


                  If I have a bigger DB, I decompose my DB in several MDB files, just add coded links if really necessary.

                  So connections to DB are more spread, and files are less sollicitated.



                  • 6. Re: Unix and SQL and ACCESS database
                    Owain North Level 4

                    No that's fair enough, if it works for you then it works.


                    The problem comes with scaling - although you can just keep adding files Access becomes extremely unreliable when people use it for web applications, as it's never what it was intended for; Microsoft themselves recommend not using it for websites.


                    The advantages of database engines (rather than a flat-file format) are many - they can manage their own caching and transactions, memory, concurrency and locking. Whilst Access probably has some of these features by now, I've only ever seen problems when an Access database becomes busy.


                    The main concern for me would always be the future-proofing and expandability. You start with Access, you have nowhere to go. If, however, you start with SQL Server Express (which is free) then you can simply upgrade to Web edition, then Standard edition, then mirror it, or cluster it, add replication or log shipping and take your website from a small company site to a massive international operation with essentially zero changes to your code.


                    Try doing that in Access


                    As has been mentioned, Access is really intended for small applications with one or two users; it's essentially one up from using Excel. I'm honestly not sure you do speak for small businesses when you say that moving to MySQL would be "not ergonomic or flexible". Any time saved now will well and truly be back to bite you in the *** further down the line

                    • 7. Re: Unix and SQL and ACCESS database
                      plarts Level 1

                      Thanks, good suggestions :


                      True : "when Access database becomes busy" , has been the only problem Ihave seen on a big Forum.

                      in all sites have built.


                      The idea to pass to SQL server Express can be a good alternative,

                      I will look at that.


                      Thanks for all.