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 2010 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 and—using ODBC—any SQL database that supports the ODBC standard.
Inside Out: What Happened to Paradox and Lotus File Support?
In Access 2010, Microsoft has deprecated support for importing or linking to Paradox and Lotus files.
On the companion CD, you’ll find a dBASE 5 file named COMPANIE.dbf that you can use to follow along with the next procedure to import this file into the Conrad Systems Contacts sample database or into a new blank database. 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 & Link 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 Browse 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 here.
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 Company1, Access creates a table named Company2.
Click Close to dismiss the message that confirms the import procedure.
Open the table that Access creates from this dBASE format data and you’ll see data for the sample companies, as shown in Figure 8-2.
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 8-1.
Table 8-1. dBASE-to-Access Data Type Conversions
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 the Hyperlink, Currency, or Decimal data type, so it stores Hyperlink data types as Memo, and Currency and Decimal data types as Number, Double.
To import a table from another database system that supports ODBC SQL (such as SQL Server 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 computer running 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 computer running SQL Server.
If you have SQL Server 2008 installed or have downloaded and installed SQL Server 2008 Express Edition, which you can download from www.microsoft.com/express/sql/default.aspx, you already have SQL Server at your disposal. 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 2008. You can also start the Configuration Manager by running C:\Windows\System32\SQLServerManager.msc. In the Configuration Manager, select SQL Server 2008 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 & Link group, click the ODBC Database button. 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 computer running SQL Server that contains 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 here, 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, Access, and Microsoft Excel. 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.
If you don’t see the data source you need, see Creating a Data Source to Link to an ODBC Database, 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 here.
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 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 data types to Access data types, as shown in Table 8-2.
Table 8-2. SQL-to-Access Data Type Conversions
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[a] |
IMAGE | OLE Object |
[a] The Access Database Engine (ACE) 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. | |
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 & Link group, click the Access command. Access opens the Get External Data - Access Database dialog box, shown here.
Click Browse to open the File Open dialog box, previously shown on Importing dBASE Files. 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
Access 2010 provides a database utility to create a compiled version of a .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 a .mde, .accde or .ade file. For details about creating a compiled version of your application, see Chapter 27, “Distributing Your Application,” on the companion CD.
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 Select All. 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 was 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 version of Access before 2007, 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 2010. You can also select the Import/Export Specs check box. (See the sidebar Defining an Import Specification, 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, 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 9, 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 version of Access, you must have at least read permission for the database, read data permission for the tables, and read definition permission for all other objects to import objects. After you import the objects into your database, you will own the copies of those objects in the target database.