It *Should* Be This Easy

…posted by davidjmedlock:

When it comes to databases, sometimes we simply have to work with what we’ve got and hope it will get better at some point in the future. For example, sometimes you may find yourself able to use only Microsoft Access. Now, Access was not designed to power web sites. It works okay for small web sites, but it quickly becomes unmanageable, in a few ways:

  • When the Access database is accessed via the website it is locked, making it virtually impossible to overwrite if you need to upload changes to the database.
  • If users are interacting with your site you’ll have to make sure you don’t lose any data when you make structural changes to the database. This means making the changes on your development machine, downloading the current version from your website, and combing through the database to make sure you’ve added and modified all the right columns and tables, then making sure the data holds up, then breaking the lock on the Access file (usually by changing the DSN), then uploading the new database… (Yes, it’s messy.)
  • An Access database is far more limited in the number of concurrent users it can support and once it reaches a certain size you’ll see some serious performance issues.

So, at some point moving to an actual database server is going to be a necessity for many rapidly growing sites. For Access users, the choice is often SQL Server, since it’s so incredibly easy to move data between Access and SQL Server, using Data Transformation Services.

Now, over the weekend, I found myself in the position of needing to move a database for a site over from Access to SQL Server. It was easier than I thought. Here’s all I had to do:
[list=1][*]Create the SQL Server databases: one for testing, one for production.[*]Create the ODBC datasources[*]Export the data from Access to the test database[*]Change my DSN variable in my application.cfm file[*]Change any Now() functions in my queries to GetDate() functions.[*]Test the code on a subdomain of the site[*]Upload the new code[/list]In total, it might have taken two or three hours, just because I had to find a copy of Enterprise Manager that I could use with a remote host. Fortunately for those of us who are perpetually broke, you can download the trial version of SQL Server and you get a non-expiring version of Enterprise Manager. (See referenced thread above.) Once I had that, everything else was a breeeze. :)

So, really, the only application level change I had to make was the DSN name and the current date function in my queries. The current date function could be solved in a couple of ways:

1. By setting a variable called “NowFunction” (or whatever) equal to the name of the function you need for the current database:



[left]INSERT INTO myTable ( date_added ) VALUES ( #NowFunction# )[/left]

2. By using the ColdFusion function Now() to insert the date:



INSERT INTO myTable ( date_added ) VALUES ( #Now()# )

And always be sure to test everything fully in a testing environment before you deploy it. And that is all I have to say. For now…

(Note: The code blocks above aren’t working correctly just yet. I’ll let the guys at SP know so they can check it out.)

Win an Annual Membership to Learnable,

SitePoint's Learning Platform

  • http://www.ryanguill.com Rynoguill

    i have used a program like navicat to import databases, tables and thier data into mysql before with surprising luck. a little tweaking is necessary, but it was much easier than i expected.

    but youre definately right about access, its not made for large and growing sites

  • http://www.comcar.co.uk rtr

    Hi David,
    I use an Access database, or rather two of them, and change over
    the application files to avoid the problem of locking. It works for me because users are only reading the data. Coldfusion is happily running the calculations for 1000 users a day. If the datatable is kept small and simple, is there any reason to consider SQL even if visitors/calculations expand to say 10,000 per day?

  • http://www.delyrical.com davidjmedlock

    rtr,

    That sounds like a good way of managing the locking issue and when users are only reading data, it greatly reduces the problems you’ll encounter.

    If you were to expand to 10,000 a day Access could handle pretty well. The company I currently work for ran their database in Access for a couple of years I believe before switching to SQL Server. So, no, I wouldn’t worry too much about switching databases at this point.

    I will say that the site I switched to SQL Server seemed to run faster after the switch, though. Also, if you add an sort of user interaction to your site, you may want to consider moving up to SQL Server in the process. My big issue was that it was a pain to try and maintain the data and make modifications to the database at the same time.

  • cfordie

    the locked databases with Access is a pain but there is an easy way to get around it. Just write a fake query to that db through CF – ie:

    SELECT This (fake fieldname)
    FROM That (fake table name)

    Then execute that page and it will give you an error of course but the .ldb file will go away. if it doesnt work right away just execute that page again. fun stuff!

  • http://www.delyrical.com davidjmedlock

    Very true. I’ve seen this solution before as well and it works fine from what I’ve heard. I don’t like the idea of keeping intentionally broken code on a live server though, so I’d remove it when you’re finished using it. (I know it most likely wouldn’t be an issue, but it’s better to be safe than sorry…)

    I believe you can prevent this on your development machine (or if you happen to have access to the CF Administrator on the server) by going into CF Administrator->Data Sources->Edit Data Source and uncheck “Maintain Connection”. In a live environment, this may cause your database access to slow down a bit, though since I believe it will have to recreate the connection each time.