Previous Page
Next Page

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!

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 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 Serverspecific SQL statements. Figure 5-38shows the generated script 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 Create 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

Plenty of books are available on the subject of SQL. See the end of this hack for a short list.

Once you've cleaned up the SQL, and it's ready for Access, you need to call up a Data Definition 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. Figure 5-40 shows the pane with the edited SQL script. 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 script, ready to run in Access

5.11.2. See Also

  • SQL Pocket Guide (O'Reilly)

  • SQL in a Nutshell (O'Reilly)

    Previous Page
    Next Page