In March (CFDJ, Vol.3, issue 5) we covered reasons to upsize your Access database to SQL Server and the tools you'll use to do it. Here we explain in detail how to copy data to SQL Server and troubleshoot your upsizing project.

The IMPORT AND EXPORT data wizard (aka the DTS Wizard - Data Transformation Services) certainly helps get you going with building tables in SQL Server. It takes an Access database (as well as many other database types) to import its table structures and data to a Microsoft SQL Server. Since it uses a wizard format, it's simple to use as long as you know what data you want to transform. To start the wizard choose START > MSDE > IMPORT AND EXPORT DATA or START > Microsoft SQL Server 7.0 > IMPORT AND EXPORT DATA.

On the first data-gathering screen (see Figure 1) you'll be prompted for information about the data source. Choose Microsoft Access from the drop-down menu for the data source (or whatever is appropriate for the database you actually intend to import to SQL Server. You'll find quite a long list of possibilities). Then select the appropriate .MDB file and type your username and password if necessary. When you're finished, click the NEXT > button to continue on to set up the destination database.

On this page you'll most likely leave the destination database on the default Microsoft OLE Provider for SQL Server. This is the selection you'd normally use to import the MS Access database you chose on the previous page of the wizard into a SQL Server database. Next you'll need to provide the name of the server and the security information. Typically this information is provided to you by your system administrator or by the Web-hosting company that houses your SQL Server. Once this information is correct, you'll be able to select the actual database you wish to import the tables into.

If you haven't already created a database to house your new data, choose from the database drop-down menu. Avoid making the typical mistake of importing the tables into the MASTER database or one of the example databases Microsoft provides. You'll want to start off fresh if you can. A dialog box will open (see Figure 2), prompting you for the name and size of your database. Type the name and accept the defaults. When you press OK, the new database will be created. After this is complete, press Next again. You'll be shown a lovely pictorial of the action that is about to be taken - namely, importing the MS Access database into the SQL Server database. Choose the "Copy Tables from the source database" radio button and push Next.

Transformation
Now you'll be given a list of the tables in your database. Notice that you won't be given the names of any queries or reports. Choose the individual tables you wish to import or just press the SELECT ALL button. As you select the tables, a field activates that will allow you to change the name of the destination table. Do so if necessary. There's also a Transform button that will allow you to make changes to the table's structure as it is being brought into the new SQL Server table. If you transform anything, you'll want to be aware of the new data types available in SQL Server but not in MS Access. There are some confusing things. For instance, a Text field in Access is similar to an NVARCHAR or VARCHAR data type in SQL Server. On the other hand, a Text field in SQL Server is like a memo field in MS Access. Please check your SQL Server references for more specifics on these data types and their exact characteristics.

On the last page of the wizard you'll be able to select when the import should be executed. You'll probably want to choose "Run Immediately" and just choose NEXT>. Finally, press FINISH and watch the action. If anything goes wrong on the data import, the wizard will let you know what the problems were. You'll need to fix them in the Transformations and then retry the tables that failed. Referential integrity can complicate the process of retrying individual tables. If life becomes too complicated, consider dropping the referential constraints for the tables in question and re-creating the constraints after the upgrade. When you're done we suggest you review the table structure in the design table view. You'll probably want to at least add the primary key constraint by clicking on the key symbol to your key field and look at which fields allow nulls. You might also check the Identity box if your key field was AutoNumber in Access and pick some fields to index. It's a good idea to look at the data by right-clicking on the table and doing the action "open all records" to make sure nothing went wrong.

Finding and Troubleshooting Upgrade Problems
After you've finished importing your data, you'll need to test your queries thoroughly. There are a few differences between MS Access's version of SQL and the implementation that SQL Server uses.

Field and Table Names

• Be careful what you name your fields and tables in MS Access: use only letters A-Z, digits 0-9, and underscores. No spaces or other funny characters are allowed unless you want to "delimit your identifiers with square brackets []" (you don't).
• Avoid reserved words for field names or you'll get errors when you use them in SQL queries - for example, DESC, AND, OR.

• Logical fields in Access can be True, False, or Null - the default equivalent bit field in SQL Server can be only 0 or 1. For this reason you may prefer to transform logical fields to integers instead. Also, if you have any queries that compare a bit field to TRUE or -1, you'll have problems.
• AutoNumber fields are equivalent in SQL Server to an Identity field (a checkbox that you turn on when you set up a new field). Be aware that an Identity field in SQL Server 6.5 had some problems and can be replaced by a long and your own next ID code if you prefer.
• Memo fields are converted to TEXT or NTEXT fields.
• Access will auto trim text for fields that are too long, while SQL Server will give an error if you try to insert too long a piece of text. Check the length of the fields and use the left function to trim to correct length; for example, if the firstname field is varchar 30, in your insert statement put VALUES('#left(firstname,30)#') instead of VALUES('#firstname#').

SQL and Function Differences
The syntax for some SQL statements, functions, and operators is different between Access and SQL Server (see Table 1).

Changing SQL and Access Queries
If you have ColdFusion Studio, use the extended search to find where these problems may occur and deal with them. For instance, the above-mentioned problem with DELETE * could be dealt with by simply doing an extended search for DELETE * and replacing it with DELETE. However, be careful with function names that are in both Access SQL and ColdFusion, such as ucase() and #ucase()#, because you only want to change the Access SQL function to upper()! We suggest you carefully watch your ColdFusion application server log after switching over to find any issues you may have missed.

Access Queries become Views in SQL Server. To convert a query to a view, cut and paste from the Access SQL view into SQL Server's View creation designer. Just be aware that, unlike Access queries, they can't use an ORDER BY clause - you'll need to do the ordering when you run the view SQL in ColdFusion. The view designer can also be useful when you want to test some SQL from ColdFusion to find out why it has a syntax error. Alternatively, you may choose to create a stored procedure for a query, especially if you need to parameterize the WHERE clause. Stored procedures also let you code multiple queries and procedural code such as IF statements inside SQL Server.

Summary
Upgrading to SQL Server may sound intimidating, but Microsoft has provided tools to ease your way. Many SQL Server interfaces either look similar to Access or can be administered using Access as a front end. Upgrading to SQL Server is a big step, but the payoffs are well worth it for ColdFusion programmers. Plus, the MSDE version of SQL Server is free with Office! As you get used to SQL Server, you'll find it superior to Microsoft Access in many ways, and you'll have an excellent addition to your résumé!

Resources

1. SQL Server data types: www.pin-pub.com/foxtalk/ft9902i/ft99b20.html
2. SQL tutorial: http://w3.one.net/~jhoffman/sqltut.htm
3. Microsoft's SQL Server site: www.microsoft.com/sql/
4. Whitepaper on upsizing: www.microsoft.com/accessdev/prodinfo/upsize97.htm
5. Creating and deploying with the MSDE: www.microsoft.com/sql/ techinfo/msde.htm
6. Reasons to upsize: http://www.sys-con.com/coldfusion/article.cfm?id=17
7. Sams Teach Yourself Sql in 10 Minutes, by Ben Forta: www.amazon.com/exec/obidos/ASIN/0672316641/

• Figure 1
• Figure 2
• Figure 3