Paul Kimmel's Blog

Working on the book project—Professional DevExpress ASP.NET Controls at http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470500832.html—I needed to convert an existing Access database to SQL Server 2005. Unfortunately sometimes even Google doesn’t find you a straight forward solution. Its not Google’s fault. In this case its just that there are so many security issues, Trust Center nonsense, and other glitches that some tips just take to long.

If you are in a hurry the fastest way to upsize a database actually seems to be to go backward to go forward. Here is a quick, pretty painless way to move an Access 2007 database to SQL Serve 2005—if you are getting permission and Trust Center errors other ways.

1. Open you Access 2007 database (*.accdb)
2. In Access 2007 Choose Save As|Access 200 Database
3. Pick a new file name with the .mdb extension.
4. Click Save. (This step makes a copy of your Access database as a .MDB file)
5. Close Access
6. Open Microsoft SQL Server Management Studio
7. Expand Databases
8. Right Click Databases in the Object Explorer and click New Database
9. Provide a database name (see Figure 1) and Click OK.
10. Right click the new database in the Object Explorer
11. Click Tasks|Import which will start the SQL Server Import and Export Wizard
12. Click Next
13. In the Choose Data Source wizard pick the Microsoft Access data source
14. use the Browse button and brose to your Access database (see Figure 3)
15. Click Next
16. Chose a Destination (the database you created in steps 8 and 9) (see Figure 4)
17. Click Next
18. In the Specify Table Copy or Query use the default Copy option (see Figure 5)
19. Click Next
20. Select all of the tables you and views you want to copy
21. Click Next
22. Click Finish (to execute immediately)
23. Click Finish again (too many steps again, as far as I am considered)
24. Click Close after the wizard runs (see Figure 6)

This is a lot of steps, but it worked on my Vista box and converted an Access 2007 database to SQL Server 2005 without any goofy Trust Center issues, no security setting changes, and the least amount of headache. Borrowing from Alan Cooper’s—creator of Visual Basic—ideas in the book The Inmates are Running the Asylum sometimes software makes us perform too many of the equivalent of “are you sure steps”. There should be one button—upgrade to SQL Server—that in one step upgrades everything. One click and I get the result wanted. Tables, views, and stuff unneeded can be removed later. A lot of times the most obvious thing is what people want, sort of the equivalent of a one-click or easy button.

If you have to convert Access to SQL Server and you are getting warnings perhaps this approach will work best for you.

 
Figure 1: Create a New SQL Server database in SQL Server Management Studio.

Figure 2: The SQL Server Import and Export Wizard.

 
Figure 3: Choose Microsoft Access and brose to your Access 2000 .mdb file.

Figure 4: Pick your destination SQL Server database.

Figure 5: Indicate that you want to copy data from one or more tables or views.

Figure 6: The wizard running.