MS Access to SQL Server
Now, Microsoft has poor representation on the web servre market; roughly 20% of servers use IIS, according to
Netcraft. Why is this?
Well, recently I had the enviable task of converting a the backend of an ASP system from MS Access to SQL Server. Basing a website on an Access database is weird in itself; the reason for this particular transition was that the database had started to break.
So, two products from the one manufacturer, with similar purpose, marketed as being compatible. Transition shouldn't be hard, right? Well... Access has a tool for moving data to SQL Server. The databases I was working on were Access 2000 and SQL Server 2000. Access 2000's version of this tool doesn't support SQL Server 2000. And Access databases aren't directly compatible across versions; in a newer version of Access the import tool choked on my database. So, reproduced table structure, and wrote queries to transfer data. Over a 128kbit up connection. (SQL Server has the facility to move its own databases more efficiently, but you have to be SA (admin) to do that. This was a shared host).
An hour later and all is done. Except half the indexes are missing... Because, SQL Server doesn't allow indexes on field types that Access allows indexes on; creating indexes seems to fail silently. So, pull out SQL Enterprise Manager (a piece of software written by people who don't believe in keyboard shortcuts), and fix things. Yay. Ooh, the system's stored queries rely on some sort of weird sorting thing that Access does automatically. Fix them.
Now, the ASP. Oldfashioned, pre-.NET ASP, with lots of on error resume nexts. ASP is ugly. When people learn ASP, they seem to almost universally agree to pretend SQL injection doesn't exist. Fix that .... Oh, some Access queries (with odd syntax, admittedly) aren't valid on SQL Server. Obviously. Fix. Yay! Working now. Except for putting in actual SQL Server details. Search and replace over hundreds of files? Command prompt? What's a command prompt? Download, do it on desktop, re-upload.
You want to read-protect a directory? What cheek. You'll need to use a graphical tool available only to the administrator. What, you're on a shared host? Sorry, I'm not going to help you.
Yay! Finished. Hope SQL Server 200-whatever is delayed indefinitely.
Really, remote-access to a Windows machine is incredibly unpleasant. Hopefully they'll fix this in longhorn.