As Jamie shows, even when you’re working with SQL Server, Access can have an important role to play. Here’s what to do to make sure that you get the most out of using the two tools together.

Because I’m a consultant and a trainer specializing in SQL Server and Visual Basic, you might be wondering why I’m writing an article on Access. In the Introduction to SQL Server course that I teach, there’s always at least one student (usually more) interested in moving their Access database to SQL Server. I use Access myself to solve many business problems that I’m presented with. There’s a time and place for Access and there’s a time to move up to SQL Server. Frequently, as I intend to show, the two times occur simultaneously.

As a consultant working with several clients building new database-driven applications, I use Access as a prototyping tool. I started using Access on a particularly difficult project. Working with that client, everybody on the team knew that we’d need the power of a larger relational database management system (RDBMS). Trying to practice what I preach, we attempted to do the logical design on paper. As the database grew and the paper flowed, both the project team and the client began to get lost in an enormous jungle of data elements. We all discovered that we needed a big picture to pull together the detail that was overwhelming us.

Accessing the design

The client asked me how we could use SQL Server to draw this picture, and I told him we’d need a third-party tool, like Erwin. The client felt that he’d already spent enough money on BackOffice for the server, Windows 95 for the workstations, and memory or new machines to support the new environment. A new tool wasn’t an option.

That was only part of the problem. By this point in the project, I’d already created some of the tables in SQL Server — and, at this point, the client wanted to change some data types. There was no data in the tables yet, so it wasn’t a big deal to drop the tables and rebuild them as desired. Then, the table names weren’t quite right. Drop and rebuild — again. Then, “Oh, there’s that one case when column x needs to store the letter ‘F,’ so it can’t be an integer.” This table had some data in it, so the process was significantly more complicated: Rename the table, build the new table, insert the data from the old table into the new table, and drop the old table. Now, this was starting to get old. Not to mention that the client wasn’t ready to sign off on the database design but we were loading data into the tables to meet deadline.

The client had done some other projects in Access, so we decided to use Access to draw the big picture that we needed. Using Access gave us many benefits. It allowed us to see the tables taking shape, plus we could change data types and remove columns easily (in SQL Server, you can’t do this without jumping through several hoops).

So, Access became our database design tool. We could print tables, view the ER diagrams (Entity Relationships, not the show), and generally get a better feel for where we were going. Life became simpler and happier for all involved.

Building the clients

While we hadn’t yet completely agreed on the data design, we needed to start prototyping the client software. Enter Visual Basic — that was going to be the language for the application. The client had several VB programmers who were comfortable with DAO, but our end goal was SQL Server, where DAO would be inappropriate. I showed the developers some benchmarks comparing DAO and RDO performance and instantly had a bunch of converts to RDO.

So, we started using RDO, but we used a DSN that pointed at our Access database. This allowed us to continue development without SQL Server, but we wouldn’t need to rewrite all of our VB code when the time came to convert.

We built some of the data entry applications and a few reports. We even wrote some business servers using VB’s object capabilities. We were still making changes to the database design, though, which annoyed the VB programmers, to say the least. Every so often, development would stop while we all rolled up our sleeves and fixed the VB references to match the database changes. The easiest changes on the database, name changes, were the most aggravating for the programmers. Still, the good news was that, thanks to Access, we were moving on both fronts. We eventually nailed down the database design and it was time to start moving it to SQL Server. At first, we used the quick and dirty way of getting Access to SQL Server: the export function. When you use the Export option, Access builds the tables in SQL Server using its best judgment. All of Access’s text columns become varchar in SQL Server, the Yes/No fields become bit, OLE Object maps to image, and Access Memo becomes SQL Server text. (See Table 1 for more datatype mappings.) The export went quickly and things were looking up.

Table 1. Access to SQL datatype conversions.

Along with the table structures, we’d sent over the data in the tables. The first thing I noticed was that our Access Counter fields had been created with an appropriate data type, but they hadn’t been converted to SQL Server identity fields. As a result, new records weren’t getting data in our old Counter fields. In addition, the embedded carriage return in some fields made ISQL/w do some surprising things.

However, we decided we could live with these issues, converted our DSN, and started running with SQL Server. All of a sudden, our code was crawling compared to the performance we’d been having with Access. That’s when I discovered that indexes aren’t created when exporting to SQL Server.

It’s not hard to build indexes in SQL Server, and, in fact, even when using the Upsizing tool, you’ll probably want to build your indexes manually. My experience has been that SQL Server applications need far fewer indexes than their Access counterparts. Relationships are also not recreated during an export, and these have to be implemented by using referential constraints or triggers.

With the issue list mounting, we decided we had to check out the Upsizing tool. The biggest problem was that we were using Access 2.0 and SQL Server 6.5, and there’s no Upsizer for this configuration. We had to choose between converting to SQL Server 4.x or Access 97. Once again, it was easier to go with Access, and we upgraded to Access 97.

There are other options, by the way, including using SQL Server’s Bulk Copy Program (BCP). But if Access is involved, and there’s not a lot of data, exporting or the Upsizing tool are your best choices.

Lessons learned: Export

If you’re going to use Access as a SQL Server prototype, there are a couple of things to watch out for. You can see these for yourself by setting up a database to experiment with. In Access 95 or Access 97, the File | New option leads to a Wizard with several canned databases. Pick one (the “Contact Management” database is a good choice) and check the option to include sample data. This loads the table with data to work with, making for an even better case study.

Now, I personally don’t like using spaces in my table names, though the Wizard does create several tables with spaces. As it turns out, Access and SQL Server both support spaces, but by using a different notation around the table names. Access uses square brackets and SQL Server uses double quotes (and then only after the Quoted Identifiers database option has been turned on). You must also set up the SQL Server DSN to handle quoted identifiers. Of course, if you rename the Wizards “Contact Types” table to ContactTypes, you’ll also need to change the table name occurrences in all the forms and reports. The best plan is probably to avoid spaces.

So, the time comes to move this database to SQL Server, and you choose to export. After exporting, you use the Enterprise Manager to check out the tables built for you in SQL Server. Table 2 shows one of my sample tables and the data types in Access, SQL Server after Export, and SQL Server after upsizing.

Table 2. Converting an Access Table to SQL Server.

As I mentioned, the counter field won’t be auto-incrementing, relationships won’t be enforced, and there will be no indexes. If your application was developed in Access or you don’t want to alter your existing programs in any way, you can remove the original tables from your database and then add attached tables to reference the new versions on SQL Server. Unfortunately, the SQL Server names will be different from the original table names, as the new names will include the name of the tables’ owners. However, you can still change the names of the attached tables in Access so that your forms and reports can continue to work (this will also work for any programs that accessed the MDB directly). You might also need to make some changes to your code if you’ve taken advantage of table type recordsets, which aren’t supported in ODBC.

For prog-rams that went through ODBC to get at the Access data, changing the ODBC DSN to point to SQL Server should take care of most issues, depending on the embedded SQL in your program. If you’ve got a lot of code that depends on the DAO object model, then you’ve got material for more articles. When using Access for prototyping, the key is to stick to pure SQL for all your database accesses.

However, the export will be simple and quick (which is why my client favored using this approach). After it’s done, you’ll need to add primary key constraints and any other referential constraints that were in the Access database. This can be a non-trivial activity.

If you naively try to make a field a primary key in SQL Server, you’ll discover that SQL Server won’t let you. The reason is that all of the columns that were created in SQL Server will have been defined to allow null values. Unlike Access, which is very friendly about this sort of thing, SQL Server will only let you add a new column or change a column’s name. You can’t make a null column into a not null (or required) column. Nor can you make an existing column an IDENTITY field to implement a Counter field.

In order to fix these problems, you must recreate the table and repopulate it as described above. While handling these problems, you might also want to add default constraints, check constraints, or referential constraints to fill in for Access’s validation and default field properties.

One last wrinkle: Once you’ve replaced the SQL Server field with an IDENTITY field, you’ll want to reload it with the values that were in the Access database. Unfortunately, SQL Server won’t let you do that either, as an IDENTITY field is supposed to calculate its own values. You’ll need to Set IDENTITY Off on the new field while you load your data.

To sum up: If you follow my suggestions, exporting will work, provided you have a few tables or simply want to get the basic table up to SQL Server to be tweaked. If the Access system is more than just a prototype (if you’re converting an existing system), then you’d want to use the Upsizing tool.

Lessons learned: Upsizing

The Upsizing tool has options to handle the counter fields, primary keys, and referential integrity. With referential integrity, you need to decide if you want updates to cascade. If you do, then you’ll have to select the triggers option, which will cause the Upsizer to issue a warning to you if you also select DRI (declarative referential integrity or foreign key constraints). You can’t have both.

I prefer to use constraints and provide cascade functionality by using a stored procedure to do a bottom deletion. This does require more work when you create your application. The only advice I can give you here is to experiment with both methods, but then pick a style and stick with it for consistency. The wizard can also add time stamps that are helpful for optimistic concurrency control. The Upsizer will also handle data validation and column defaults.

With the Upsizer, you can also choose to have the Upsizer rename your old tables and link the tables from SQL Server, retaining the original Access table names. Upsizer leaves the old tables in the database but changes their names by adding “_local” to the end. However, Upsizer will then ignore those tables the next time you run it. When you’re experimenting, you’ll need to rename the tables to try it again.

Which should you use? As I’ve suggested, the best answer is to practice and experiment using the canned tables that are delivered with Access. Originally, I exported most of my prototypes to SQL Server. But as the Upsizer has improved, I’m using it more and more.

Even now, though, I still don’t let the Upsizer build the databases. I always do this first, using TSQL or the Enterprise Manager. But I do use Access until the database design is concrete. This has saved me countless hours of manipulating SQL Server. And, in the long run, it saved my clients some cash.

Other Similar Articles in Our Site

Client/Server Development with Access

Migrating Access (and Outlook Data) to SQL Server

Improving on VBA with Transact-SQL