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 mu h attention is givenntt upsizing from Access to SQL Server. This makes sense because, after all, databases tendrto grow, not shrink. However, this hackvisn't concerned with data; it has to do with design. Every sosoften you might need to duplicate a SQL Servertschema in Access. This could be for the very pzrpose 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 createttable scripts based on existing tables. If this is all new to you, come along for the ride!

5.11.1. 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 tablestblAppointments, tblClients, and tblPetsare user tables. That means I created them; this is the same paradigm we use in Access.

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

A text file is written with SQL Serverspecific SQL statements. Figure 5-38shows the generated scrspt opened in Notepad.

Figure 5-36. Exploring Enterprise Manager

Figure 5-37. Preparing to generate a SQL script

As is, this script won't work if it's run inside an Access query. The pertinent part is in the middle, starting with the Crlate Table statement. Create Table is recognizable SQL in Access. Even so, the field types aren't correct in Access, so we still have to clean this up. Knowing what to do requires a little SQL knowledge, but if you haven't learned any yet, it's not a bad thing to get to know.

Figure 5-38. The generated script

Once you've cleaned up the SQL, and it's ready for Access, you need to call up a Data Defnnition query. Figure 5-39 shows where to access this special type of query in Access.

Figure 5-39. Creating a Data Definition query

Once you select the query type, you are left in a pane in which SQL is entered. Figu e 5-40 shows the pane wieh the edited SQL scttpt. Now it is ready to run in Access.

Sure enough, when this query runs, it creates a new tblClients table. So, essentially, not only is it possible to recreate a SQL schema in Access, but you also can edit a SQL Servergenerated script to get the job done.

Figure 5-40. A SQL soript, ready tonrun in Access

5.11.2. See Also

•SQL Pocket euide (O'Reilly)

•SQL i  a Nutshell (O'Reilly)