It *Should* Be This Easy

Share this article

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

davidjmedlockdavidjmedlock
View Author
Share this article
Read Next
From Idea to Prototype in Minutes: Claude Sonnet 3.5
From Idea to Prototype in Minutes: Claude Sonnet 3.5
Zain
Essential Plugins for WordPress Developers: Top Picks for 2024
Essential Plugins for WordPress Developers: Top Picks for 2024
SitePoint Sponsors
WebAssembly vs JavaScript: A Comparison
WebAssembly vs JavaScript: A Comparison
Kaan Güner
The Functional Depth of Docker and Docker Compose
The Functional Depth of Docker and Docker Compose
Vultr
How Top HR Agencies Build Trust Through Logo Designs
How Top HR Agencies Build Trust Through Logo Designs
Evan Brown
Leveraging Progressive Web Apps (PWAs) for Enhanced Mobile User Engagement
Leveraging Progressive Web Apps (PWAs) for Enhanced Mobile User Engagement
SitePoint Sponsors
10 Artificial Intelligence APIs for Developers
10 Artificial Intelligence APIs for Developers
SitePoint Sponsors
The Ultimate Guide to Navigating SQL Server With SQLCMD
The Ultimate Guide to Navigating SQL Server With SQLCMD
Nisarg Upadhyay
Retrieval-augmented Generation: Revolution or Overpromise?
Retrieval-augmented Generation: Revolution or Overpromise?
Kateryna ReshetiloOlexandr Moklyak
How to Deploy Apache Airflow on Vultr Using Anaconda
How to Deploy Apache Airflow on Vultr Using Anaconda
Vultr
Cloud Native: How Ampere Is Improving Nightly Arm64 Builds
Cloud Native: How Ampere Is Improving Nightly Arm64 Builds
Dave NearyAaron Williams
How to Create Content in WordPress with AI
How to Create Content in WordPress with AI
Çağdaş Dağ
A Beginner’s Guide to Setting Up a Project in Laravel
A Beginner’s Guide to Setting Up a Project in Laravel
Claudio Ribeiro
Enhancing DevSecOps Workflows with Generative AI: A Comprehensive Guide
Enhancing DevSecOps Workflows with Generative AI: A Comprehensive Guide
Gitlab
Creating Fluid Typography with the CSS clamp() Function
Creating Fluid Typography with the CSS clamp() Function
Daine Mawer
Comparing Full Stack and Headless CMS Platforms
Comparing Full Stack and Headless CMS Platforms
Vultr
7 Easy Ways to Make a Magento 2 Website Faster
7 Easy Ways to Make a Magento 2 Website Faster
Konstantin Gerasimov
Powerful React Form Builders to Consider in 2024
Powerful React Form Builders to Consider in 2024
Femi Akinyemi
Quick Tip: How to Animate Text Gradients and Patterns in CSS
Quick Tip: How to Animate Text Gradients and Patterns in CSS
Ralph Mason
Sending Email Using Node.js
Sending Email Using Node.js
Craig Buckler
Creating a Navbar in React
Creating a Navbar in React
Vidura Senevirathne
A Complete Guide to CSS Logical Properties, with Cheat Sheet
A Complete Guide to CSS Logical Properties, with Cheat Sheet
Ralph Mason
Using JSON Web Tokens with Node.js
Using JSON Web Tokens with Node.js
Lakindu Hewawasam
How to Build a Simple Web Server with Node.js
How to Build a Simple Web Server with Node.js
Chameera Dulanga
Building a Digital Fortress: How to Strengthen DNS Against DDoS Attacks?
Building a Digital Fortress: How to Strengthen DNS Against DDoS Attacks?
Beloslava Petrova
Crafting Interactive Scatter Plots with Plotly
Crafting Interactive Scatter Plots with Plotly
Binara Prabhanga
GenAI: How to Reduce Cost with Prompt Compression Techniques
GenAI: How to Reduce Cost with Prompt Compression Techniques
Suvoraj Biswas
How to Use jQuery’s ajax() Function for Asynchronous HTTP Requests
How to Use jQuery’s ajax() Function for Asynchronous HTTP Requests
Aurelio De RosaMaria Antonietta Perna
Quick Tip: How to Align Column Rows with CSS Subgrid
Quick Tip: How to Align Column Rows with CSS Subgrid
Ralph Mason
15 Top Web Design Tools & Resources To Try in 2024
15 Top Web Design Tools & Resources To Try in 2024
SitePoint Sponsors
7 Simple Rules for Better Data Visualization
7 Simple Rules for Better Data Visualization
Mariia Merkulova
Cloudways Autonomous: Fully-Managed Scalable WordPress Hosting
Cloudways Autonomous: Fully-Managed Scalable WordPress Hosting
SitePoint Team
Best Programming Language for AI
Best Programming Language for AI
Lucero del Alba
Quick Tip: How to Add Gradient Effects and Patterns to Text
Quick Tip: How to Add Gradient Effects and Patterns to Text
Ralph Mason
Logging Made Easy: A Beginner’s Guide to Winston in Node.js
Logging Made Easy: A Beginner’s Guide to Winston in Node.js
Vultr
How to Optimize Website Content for Featured Snippets
How to Optimize Website Content for Featured Snippets
Dipen Visavadiya
Psychology and UX: Decoding the Science Behind User Clicks
Psychology and UX: Decoding the Science Behind User Clicks
Tanya Kumari
Build a Full-stack App with Node.js and htmx
Build a Full-stack App with Node.js and htmx
James Hibbard
Digital Transformation with AI: The Benefits and Challenges
Digital Transformation with AI: The Benefits and Challenges
Priyanka Prajapat
Quick Tip: Creating a Date Picker in React
Quick Tip: Creating a Date Picker in React
Dianne Pena
How to Create Interactive Animations Using React Spring
How to Create Interactive Animations Using React Spring
Yemi Ojedapo
10 Reasons to Love Google Docs
10 Reasons to Love Google Docs
Joshua KrausZain Zaidi
How to Use Magento 2 for International Ecommerce Success
How to Use Magento 2 for International Ecommerce Success
Mitul Patel
5 Exciting New JavaScript Features in 2024
5 Exciting New JavaScript Features in 2024
Olivia GibsonDarren Jones
Tools and Strategies for Efficient Web Project Management
Tools and Strategies for Efficient Web Project Management
Juliet Ofoegbu
Choosing the Best WordPress CRM Plugin for Your Business
Choosing the Best WordPress CRM Plugin for Your Business
Neve Wilkinson
ChatGPT Plugins for Marketing Success
ChatGPT Plugins for Marketing Success
Neil Jordan
Managing Static Files in Django: A Comprehensive Guide
Managing Static Files in Django: A Comprehensive Guide
Kabaki Antony
The Ultimate Guide to Choosing the Best React Website Builder
The Ultimate Guide to Choosing the Best React Website Builder
Dianne Pena
Exploring the Creative Power of CSS Filters and Blending
Exploring the Creative Power of CSS Filters and Blending
Joan Ayebola
How to Use WebSockets in Node.js to Create Real-time Apps
How to Use WebSockets in Node.js to Create Real-time Apps
Craig Buckler
Best Node.js Framework Choices for Modern App Development
Best Node.js Framework Choices for Modern App Development
Dianne Pena
SaaS Boilerplates: What They Are, And 10 of the Best
SaaS Boilerplates: What They Are, And 10 of the Best
Zain Zaidi
Understanding Cookies and Sessions in React
Understanding Cookies and Sessions in React
Blessing Ene Anyebe
Enhanced Internationalization (i18n) in Next.js 14
Enhanced Internationalization (i18n) in Next.js 14
Emmanuel Onyeyaforo
Essential React Native Performance Tips and Tricks
Essential React Native Performance Tips and Tricks
Shaik Mukthahar
How to Use Server-sent Events in Node.js
How to Use Server-sent Events in Node.js
Craig Buckler
Five Simple Ways to Boost a WooCommerce Site’s Performance
Five Simple Ways to Boost a WooCommerce Site’s Performance
Palash Ghosh
Elevate Your Online Store with Top WooCommerce Plugins
Elevate Your Online Store with Top WooCommerce Plugins
Dianne Pena
Unleash Your Website’s Potential: Top 5 SEO Tools of 2024
Unleash Your Website’s Potential: Top 5 SEO Tools of 2024
Dianne Pena
How to Build a Chat Interface using Gradio & Vultr Cloud GPU
How to Build a Chat Interface using Gradio & Vultr Cloud GPU
Vultr
Enhance Your React Apps with ShadCn Utilities and Components
Enhance Your React Apps with ShadCn Utilities and Components
David Jaja
10 Best Create React App Alternatives for Different Use Cases
10 Best Create React App Alternatives for Different Use Cases
Zain Zaidi
Control Lazy Load, Infinite Scroll and Animations in React
Control Lazy Load, Infinite Scroll and Animations in React
Blessing Ene Anyebe
Building a Research Assistant Tool with AI and JavaScript
Building a Research Assistant Tool with AI and JavaScript
Mahmud Adeleye
Understanding React useEffect
Understanding React useEffect
Dianne Pena
Web Design Trends to Watch in 2024
Web Design Trends to Watch in 2024
Juliet Ofoegbu
Building a 3D Card Flip Animation with CSS Houdini
Building a 3D Card Flip Animation with CSS Houdini
Fred Zugs
How to Use ChatGPT in an Unavailable Country
How to Use ChatGPT in an Unavailable Country
Dianne Pena
An Introduction to Node.js Multithreading
An Introduction to Node.js Multithreading
Craig Buckler
How to Boost WordPress Security and Protect Your SEO Ranking
How to Boost WordPress Security and Protect Your SEO Ranking
Jaya Iyer
Understanding How ChatGPT Maintains Context
Understanding How ChatGPT Maintains Context
Dianne Pena
Building Interactive Data Visualizations with D3.js and React
Building Interactive Data Visualizations with D3.js and React
Oluwabusayo Jacobs
JavaScript vs Python: Which One Should You Learn First?
JavaScript vs Python: Which One Should You Learn First?
Olivia GibsonDarren Jones
13 Best Books, Courses and Communities for Learning React
13 Best Books, Courses and Communities for Learning React
Zain Zaidi
5 jQuery.each() Function Examples
5 jQuery.each() Function Examples
Florian RapplJames Hibbard
Implementing User Authentication in React Apps with Appwrite
Implementing User Authentication in React Apps with Appwrite
Yemi Ojedapo
AI-Powered Search Engine With Milvus Vector Database on Vultr
AI-Powered Search Engine With Milvus Vector Database on Vultr
Vultr
Understanding Signals in Django
Understanding Signals in Django
Kabaki Antony
Why React Icons May Be the Only Icon Library You Need
Why React Icons May Be the Only Icon Library You Need
Zain Zaidi
View Transitions in Astro
View Transitions in Astro
Tamas Piros
Getting Started with Content Collections in Astro
Getting Started with Content Collections in Astro
Tamas Piros
What Does the Java Virtual Machine Do All Day?
What Does the Java Virtual Machine Do All Day?
Peter Kessler
Become a Freelance Web Developer on Fiverr: Ultimate Guide
Become a Freelance Web Developer on Fiverr: Ultimate Guide
Mayank Singh
Layouts in Astro
Layouts in Astro
Tamas Piros
.NET 8: Blazor Render Modes Explained
.NET 8: Blazor Render Modes Explained
Peter De Tender
Mastering Node CSV
Mastering Node CSV
Dianne Pena
A Beginner’s Guide to SvelteKit
A Beginner’s Guide to SvelteKit
Erik KückelheimSimon Holthausen
Brighten Up Your Astro Site with KwesForms and Rive
Brighten Up Your Astro Site with KwesForms and Rive
Paul Scanlon
Which Programming Language Should I Learn First in 2024?
Which Programming Language Should I Learn First in 2024?
Joel Falconer
Managing PHP Versions with Laravel Herd
Managing PHP Versions with Laravel Herd
Dianne Pena
Accelerating the Cloud: The Final Steps
Accelerating the Cloud: The Final Steps
Dave Neary
An Alphebetized List of MIME Types
An Alphebetized List of MIME Types
Dianne Pena
The Best PHP Frameworks for 2024
The Best PHP Frameworks for 2024
Claudio Ribeiro
11 Best WordPress Themes for Developers & Designers in 2024
11 Best WordPress Themes for Developers & Designers in 2024
SitePoint Sponsors
Top 10 Best WordPress AI Plugins of 2024
Top 10 Best WordPress AI Plugins of 2024
Dianne Pena
20+ Tools for Node.js Development in 2024
20+ Tools for Node.js Development in 2024
Dianne Pena
The Best Figma Plugins to Enhance Your Design Workflow in 2024
The Best Figma Plugins to Enhance Your Design Workflow in 2024
Dianne Pena
Harnessing the Power of Zenserp for Advanced Search Engine Parsing
Harnessing the Power of Zenserp for Advanced Search Engine Parsing
Christopher Collins
Build Your Own AI Tools in Python Using the OpenAI API
Build Your Own AI Tools in Python Using the OpenAI API
Zain Zaidi
Get the freshest news and resources for developers, designers and digital creators in your inbox each week
Loading form