|
|
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 ManagerEnterprise 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 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
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
 |
|
|
Generate SQL Script…, as shown in 