Importing Data and Databases | MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)

You can copy data from a number of different file formats to create an Access table. In addition to copying data from a number of popular database file formats, Access 2007 can also create a table from data in a spreadsheet or a text file. When you copy data from another database, Access uses information stored by the source database system to convert or name objects in the target Access table. You can import data not only from other Access databases but also from dBASE, Paradox, and-using ODBC-any SQL database that supports the ODBC standard (including Visual FoxPro).

Importing dBASE Files

To import a dBASE file, do the following:

Open the Access database that will receive the dBASE file. If that database is already open, close all open objects so that you see only the Navigation Pane.
On the External Data tab, in the Import group, click the More command, and then click dBASE File, as shown here.
Access opens the Get External Data-dBASE File dialog box, shown here. Click the Browse button to browse for the dBASE file you need to import.
Access opens the File Open dialog box, shown next. Select dBASE III, dBASE IV, or dBASE 5, as appropriate, in the list to the right of the File Name box. (In Windows XP, this list is labeled Files Of Type and appears below the File Name box.) Select the source file folder, and then select or type the file name in the File Name box. If you’re having difficulty finding the file you want, type a search string in the Search field.
Click the Open button to return to the Get External Data-dBASE File dialog box with the file path to the dBASE file you need in the File Name box. Make sure the first option, Import The Source Data Into A New Table In The Current Database, is selected, and then click OK to import the dBASE file you selected. Access displays a message that informs you of the result of the import procedure, as shown next.

If the import procedure is successful, the new table will have the name of the dBASE file (without the file name extension). If Access finds a duplicate table name, it will generate a new name by adding a unique integer to the end of the name. For example, if you import a file named COMPANY.DBF and you already have tables named Company and Companyl, Access creates a table named COMPANY2.
Click the Close button to dismiss the message that confirms the import procedure.

You’ll find a dBASE 5 file named COMPANIE.DBF on the companion CD. Follow the procedure just described to import this file into the Conrad Systems Contacts sample database or into a new blank database. When you open the table that Access creates from this dBASE format data, you’ll see data for the sample companies, as shown in Figure 6–2.

image from book
Figure 6–2: Access can import every data type supported in a dBASE file.

When you look at a table imported from dBASE in Design view, you’ll find that Access has converted the data types, as shown in Table 6–1.

Table 6–1: dBASE-to-Access Data Type Conversions
Open table as spreadsheet
dBASE Data Type	Converts to Access Data Type
Character	Text
Numeric	Number, Field Size property set to Double
Float	Number, Field Size property set to Double
Logical	Yes/No
Date	Date/Time
Memo	Memo

As we noted earlier, we created the COMPANIE dBASE file from the Companies table you can find in the ImportLink sample database. You can open these two tables side by side to see the differences. First, dBASE doesn’t support field names longer than 10 characters. So, CompanyName in the original file is shortened to COMPANYNAM, and LastOrderDate appears as LASTORDERD. Also, dBASE doesn’t support Hyperlink, Currency, or Decimal data types, so it stores Hyperlink data types as Memo, and Currency and Decimal data types as Number, Double.

Importing Paradox Files

The procedure for importing Paradox files is similar to the procedure for importing dBASE files. To import a Paradox file, do the following:

Open the Access database that will receive the Paradox file. If that database is already open, close all open objects so that you see only the Navigation Pane.
On the External Data tab, in the Import group, click the More command, and then click Paradox File. Access opens the Get External Data-Paradox File dialog box. Click the Browse button to browse for the Paradox file you need to import. Access opens the File Open dialog box, as shown earlier on page 262.
Select the source file folder, and then select or type the file name in the File Name box. If you’re having difficulty finding the file you want, type a search string in the Search field.
Click the Open button to return to the Get External Data-Paradox File dialog box with the file path to the Paradox file you need in the File Name box. Make sure the first option, Import The Source Data Into A New Table In The Current Database, is selected and then click OK to import the Paradox file you selected.
If the Paradox file is encrypted, Access opens a dialog box that asks for the password. Type the correct password and click OK to proceed, or click Cancel to start over.

When you proceed, Access responds with a message that indicates the result of the import procedure. If the import procedure is successful, the new table will have the name of the Paradox file (without the file name extension). If Access finds a duplicate table name, it will generate a new name by adding a unique integer to the end of the name as explained earlier about dBASE files.
Click Close to dismiss the message that confirms the import procedure.

You can try this procedure using the Companie.db file that’s included on the companion CD.

When you look at a table imported from Paradox in Design view, you’ll find that Access has converted the data types, as shown in Table 6–2.

Table 6–2: Paradox-to-Access Data Type Conversions
Open table as spreadsheet
Paradox Data Type	Converts to Access Data Type
Alphanumeric	Text
Number	Number, Field Size property set to Double
Money	Number, Field Size property set to Double
Short Number	Number, Field Size property set to Integer
Long Integer	Number, Field Size property set to Long Integer
Binary Coded Decimal	Number, Field Size property set to Double
Date	Date/Time
Time	Date/Time
Timestamp	Date/Time
Memo	Memo
Formatted Memo	Not supported
Graphic	Not supported
OLE	OLE Object (but Access won’t be able to activate the object)
Logical	Yes/No
AutoIncrement	AutoNumber
Binary	Not supported
Bytes	Not supported

Importing SQL Tables

To import a table from another database system that supports ODBC SQL (such as SQL Server, Visual FoxPro, or Oracle), you must first have the ODBC driver for that database installed on your computer. Your computer must also be linked to the network that connects to the SQL server from which you want to import data, and you must have an account on that server. Check with your system administrator for information about correctly connecting to the SQL server.

If you have SQL Server 2005 installed or have downloaded and installed SQL Server 2005 Express Edition, which you can download from www.microsoft.com/sql/editions/ express/default.mspx, you already have SQL Server at your disposal. See the Appendix for instructions on how to install SQL Server 2005 Express Edition. One of the best ways to be sure SQL Server is running on your computer is to use the SQL Server Configuration Manager. You can start the Configuration Manager from the Windows Start menu in the Configuration Tools folder under Microsoft SQL Server 2005. You can also start the Configuration Manager by running C:\Windows\System32\SQLServerManager.msc. In the Configuration Manager, select SQL Server 2005 Services and be sure the SQL Server (MSSQLSERVER) service is marked as Running. If it is not running, right-click the service name and click Start on the shortcut menu.

To import data from a SQL table, do the following:

Open the Access database that will receive the SQL data. If that database is already open, close all open objects so that you see only the Navigation Pane.
On the External Data tab, in the Import group, click the More command, and then click ODBC Database. Access opens the Get External Data-ODBC Database dialog box. Make sure the Import The Source Data Into A New Table In The Current Database option is selected and then click OK.
Access opens the Select Data Source dialog box, shown here, from which you can select the data source that maps to the SQL server containing the table you want to import.

You can select a data source name (.dsn) file that you created previously, or click the Machine Data Source tab, as shown next, to see data sources that are already defined for your computer.

Troubleshooting

Access won’t use ODBC for all file types.

Notice that the Machine Data Source tab lists installed sources for dBASE, Microsoft Excel, Access, and Visual FoxPro files. Access will not let you use ODBC for dBASE, Excel, and Access because it uses its own more efficient direct connection via its database engine. Access 2007 uses ODBC to import and link to Visual FoxPro, but you must have Visual FoxPro installed on your computer to be able to work with Visual FoxPro tables from Access.

If you don’t see the data source you need, see “Creating a Data Source to Link to an ODBC Database” on page 255 for instructions. After you select a data source, click OK.

When Access connects to the server, you’ll see the Import Objects dialog box, which lists the available tables on that server, as shown next.

image from book

If you want to import a Visual FoxPro table file (.dbf), select the Visual FoxPro Tables driver on the Machine Data Source tab of the Select Data Source dialog box and click OK. Access displays a Configure Connection dialog box. Select Free Table Directory and click the Browse button to locate the folder that contains the file that you want to import. Click OK, and Access displays the Import Objects dialog box similar to the one shown here for SQL Server that lists all the Visual FoxPro tables that exist in the folder you specified.

Note

To be able to import or link Visual FoxPro tables, you must download and install the latest Visual FoxPro ODBC driver. You can find the latest driver at http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc/defaultaspx.

From the list of tables or list of files, select the ones you want to import. If you select a table name in error, you can click it again to deselect it or you can click the Deselect All button to start over. Click OK to import the SQL tables you selected.
If the import procedure is successful, the new table will have the name of the SQL or Visual FoxPro table. If Access finds a duplicate table name, it will generate a new name by adding a unique integer to the end of the name as explained earlier about dBASE files.

Note

You’ve no doubt noticed by now that the different databases use different style conventions (dbo.newstore, Newstore, NEWSTORE) for table names.

In general, Access converts SQL and Visual FoxPro data types to Access data types, as shown in Tables 6–3 and 6–4.

Table 6–3: SQL-to-Access Data Type Conversions
Open table as spreadsheet
SQL Data Type	Converts to Access Data Type
CHAR[ACTER]	Text, or Memo if more than 255 characters in length
VARCHAR	Text, or Memo if more than 255 characters in length
TEXT	Memo
TINYINT	Number, Field Size property set to Byte
SMALLINT	Number, Field Size property set to Integer
INT	Number, Field Size property set to Long Integer
REAL	Number, Field Size property set to Double
FLOAT	Number, Field Size property set to Double
DOUBLE	Number, Field Size property set to Double
DATE	Date/Time
TIME	Date/Time
TIMESTAMP	Binary^[1]
IMAGE	OLE Object
^[1] The ACE database engine supports a Binary data type (raw hexadecimal), but the Access user interface does not. If you link to a table that has a data type that maps to Binary you will be able to see the data type in the table definition, but you won’t be able to successfully edit this data in a datasheet or form. You can manipulate Binary data in Visual Basic.

Table 6–4: Visual FoxPro-to-Access Data Type Conversions
Open table as spreadsheet
Visual FoxPro Data Type	Converts to Access Data Type
Character	Text
Numeric	Number, Field Size property set to Integer
Float	Number, Field Size property set to Double
Date	Date/Time
Logical	Yes/No
Memo	Memo
General	OLE Object

Importing Access Objects

If the database from which you want to import data is another Access database, you can import any of the six major types of Access objects: tables, queries, forms, reports, macros, or modules. To achieve the same result, you can also open the source database, select the object you want, click the Copy command in the Clipboard group on the Home tab of the Ribbon, open the target database, and then click the Paste command in the Clipboard group on the Home tab. Using the Import command, however, allows you to copy several objects without having to switch back and forth between the two databases.

To import an object from another Access database, take the following steps:

Open the Access database that will receive the object. If that database is already open, close any open objects so that only the Navigation Pane is showing.
On the External Data tab, in the Import group, click the Access command. Access opens the Get External Data-Access Database dialog box, shown here.

Click the Browse button to open the File Open dialog box, previously shown on page 262. Select the folder and the name of the .accdb, .mdb, .adp, .mda, .accda, .mde, .accde, or .ade file containing the object that you want to import, and then click Open.

Note

Microsoft Office Access 2007 provides a database utility to create a compiled version of an .mdb or .accdb desktop application or .adp project file that contains no source code. The compiled versions have .mde, .accde and .ade extensions, respectively. You cannot import forms, reports, or modules from an .mde, .accde or .ade file. For details about creating a compiled version of your application, see Chapter 25, “Distributing Your Application.”

Click OK. Access opens the Import Objects dialog box, shown here, which provides tabs for each of the object types in the database you selected. First click the tab for the object type, and then select the specific object you want to import.

If you select an object in error, you can click the name again to deselect it. If you want to import all objects of a particular type, click the Select All button. You can import multiple objects of different types by clicking each object tab in turn and selecting the objects you want to import.

You can also click the Options button (which has been clicked in the preceding illustration) to select additional options. If you import any tables from the source database, you can select the option to import the table relationships (if any) defined for those tables in the source database. If the object is a table, you can select the option to import the table structure (the table definition) only or to import the structure and the stored data. If your source database is an .mdb or .adp file created in a prior version of Access, you can select the Menus And Toolbars check box to import all the custom menus and toolbars from your source database. Be aware, however, that these items appear on a special Add-Ins tab on the Ribbon, and some of the commands you designed in your custom menus and toolbars might not work in Access 2007. You can also select the Import/Export Specs check box. (See the sidebar, “Defining an Import Specification,” on page 291 for details.) If you select the Nav Pane Groups check box, Access imports any custom Navigation Pane groups you have defined in the database. (See Chapter 2, “Exploring the New Look of Access 2007,” for details about creating custom groups.) You can also choose to import a query object (the definition of the query) by selecting As Queries under Import Queries; or you can ask Access to run the query and import the data results into a table by selecting As Tables. (See Chapter 7, “Creating and Working with Simple Queries,” for details about building and using queries.) Click OK to copy the objects you selected to the current database.
If the import procedure is successful, the new object will have the name of the object you selected. If Access finds a duplicate name, it will generate a new name by adding a unique integer to the end of the name as explained previously. Because objects such as queries, forms, reports, macros, and modules might refer to each other or to tables you’re importing, you should carefully check name references if Access has to rename an imported object.

Note

If the source Access database is a secured file created in a previous Access version, you must have at least read permission for the database, read data permission for the tables, and read definition permission for all other objects in order to import objects. After you import the objects into your database, you will own the copies of those objects in the target database.