Other Ways of Moving Data

Though the Upsizing Wizard works for most circumstances, there are times when you will want to move your data in some other way. Sometimes the Upsizing Wizard does not move a table, and sometimes the amount of data is too large for the configured server locks to accommodate. Perhaps you upsized once already and now need to add data from another copy of your database to the tables already on the server.

Exporting Tables

To export tables and queries directly to the server

  1. On the File menu, click Save as Export.

  2. Click To an external File or Database.

  3. In the Save as type box, click ODBC Databases().

  4. Type in a table name, and click OK.

  5. Select a Data Source, and click OK.

  6. Complete your login information, and click OK.

When you create a table on the server this way, the correct data type and data is transferred, but you will not get the other features that the Upsizing Wizard provides. Microsoft Access AutoNumber fields are converted to integer fields but not identity fields. No indexes are created. No table relationships exist. No triggers for enforcing cascading updates and deletes are created. You will need to attend to these matters on the server. Of course, this may be desirable if you plan to optimize the structure, changing the database schema as you migrate to a server environment.

To link (attach) to a table you have exported

  1. On the File menu, click Get External Data, then click Link Tables.

  2. Select ODBC Databases().

  3. Select your SQL Server Data source.

  4. Complete the SQL Server Login dialog box.

  5. Select the table(s) in the Link Tables dialog box, and click Save password if desired, then click OK.

  6. If your table does not have an index, you will be prompted for the unique field.

  7. The table is added as dbo_tablename. The dbo stands for database owner. You can rename the table in Microsoft Access to the original table name to avoid confusion. (All tables in SQL Server have owners and the fully qualified name of a table on SQL Server is databasename.owner.tablename, for example, pubs.dbo.authors.)

Creating Append Queries

If the table already exists on the server, you can move data to it from Microsoft Access. In Microsoft Access, link/attach to the table. Then, create a new query by selecting data from the Microsoft Access table. With the query open in design view, select Append from the Query menu. When you are prompted for the target table, select the name of the linked SQL Server table. This creates an append query. Run the query to write the designated records to the SQL Server table. This can be a useful method for moving only portions of tables, for example, if you are combining data from several databases or from several tables. If the data size is too large to transfer all at once, use criteria (like OrderID <1000, then OrderID between 1000 and 2000, and so on) to move the data over in pieces.

The Bulk Copy Program

SQL Server offers a command-line program called the Bulk Copy Program (bcp) for moving data into a SQL Server table. The SQL Server Transfer Manager feature uses bcp. To use bcp, create your table in SQL Server and move your Microsoft Access data to a text file. If you are moving data over a slow link wide area network, bcp is a good method for moving only data. If you are distributing data on floppy diskettes for a manual transfer to the server, bcp is also a good choice.

To use bcp to transfer Microsoft Access data to a SQL Server database, you must first create a text file with the data. On the File menu in Microsoft Access, click Save As/Export and select Save table name: To an external File or Database. In the Save Table screen, select Save as type Text Files and click Export. Complete the rest of the Text Export Wizard, selecting your choice of Delimited or Fixed Width file. Now you have the text file containing your data. For information about bringing the data into SQL Server, see the Microsoft SQL Server Administrator's Companion.

There are two types of bcp, fast and slow. Fast bcp occurs automatically if no indexes exist on the table and if the Select Into/Bulk Copy option is set to true for the database. Fast bcp does not log the data inserts, so you must dump the entire database following the bcp action for a reliable backup.

On the server, open a command prompt window. Move to the BINN subdirectory of the SQL Server directory. Enter a command like this:

bcp dbname..tbl in text.txt /fmy.fmt /Sservername /Usa /Ppw

Moving AutoNumber Fields

The Upsizing Wizard knows all about AutoNumber/counter/identity fields, but if you are moving the data yourself you must deal with this issue. You can define the field on the server's target table as an integer (int) data type and identity field. Use an append query to select all the fields except the AutoNumber field. SQL Server provides new identity numbers for the records as they go into the table; these numbers are different from the original Microsoft Access record numbers. To preserve the Microsoft Access AutoNumber values, mark the identity field as temporarily inactive so it will accept your numbers instead of supplying new ones.

  1. Create but do not execute an append query to move the records from the Microsoft Access table to the linked SQL Server table.

  2. Create and execute a SQL Pass-Through Query with the SQL Statement:

    SET IDENTITY_INSERT newtablename ON

  3. Execute the append query. Execute a SQL Pass-Through query:

    SET IDENTITY_INSERT newtablename OFF

This plan usually works. There is one possible problem. The SET IDENTITY_INSERT statement is valid only for subsequent statements on the same connection, so it only works if Microsoft Access uses the same connection for the pass-through query as for the append query. For more information on the use of IDENTITY_INSERT, see Knowledge Base article Q152035 INF, "Appending Data from Access Table to SQL Table."

(If you use Visual Basic for Application code to open a recordset containing an identity field, you must use the dbSeeChanges option.)