Access Hacks

Hack #49. Create Access Tables with SQL Server Scripts

SQL Server writes scripts that create tables. With a little editing, you can put them to work in Access.

So much attention is given to upsizing from Access to SQL Server. This makes sense because, after all, databases tend to grow, not shrink. However, this hack isn't concerned with data; it has to do with design. Every so often you might need to duplicate a SQL Server schema in Access. This could be for the very purpose of preparing your Access database for SQL Server.

If you are familiar with SQL Server, you already know SQL Server Enterprise Manager can write SQL create table scripts based on existing tables. If this is all new to you, come along for the ride!

Walking Through Enterprise Manager

Enterprise Manager, shown in Figure 5-36, is the utility you use to manage SQL Server.

The Pets database is accessed in the left pane. The database contains various objects. The tables of the Pets database are listed in the right pane. Most of the tables are system tables. The last three tables—tblAppointments, tblClients, and tblPets—are user tables. That means I created them; this is the same paradigm we use in Access.

To generate a SQL script, right-click the tblClients table, and select All Tasks → Generate SQL Script…, as shown in Figure 5-37. After you select a destination for the script, a file is created.

A text file is written with SQL Server–specific SQL statements. Figure 5-38shows the generated script opened in Notepad. ...