ACC2000: How to Drag Data from Microsoft Excel to a Microsoft Access Database

ID: Q208640

The information in this article applies to:

Microsoft Access 2000
Microsoft Excel 2000

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

This article shows you how to use the drag-and-drop method to copy data from a Microsoft Excel worksheet to a table in an Access database.

MORE INFORMATION

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

When you drag information from Excel to an Access database, be aware of the following data-type conversion issues:

Data type Text in Excel appears as data type Text in Access.

Data type Number in Excel appears as data type Number in Access.

Data type Currency in Excel appears as data type Currency in Access.

Data type Time in Excel appears as data type Date/Time in Access.

Data type Date in Excel appears as data type Text in Access.

To drag data from an Excel worksheet to a table in an Access database, follow these steps:

Open the sample database Northwind.mdb and create a new table in Datasheet view.

Start Excel and create a new worksheet. Type the number 1 in cell A1 and type the letter A in cell A2.

In Windows 95/98 or Windows NT 4.0, right-click an empty space on the task bar, and then click Tile Windows Vertically (or Horizontally) so that you can see both Access and Excel on the desktop.

Select to highlight cells A1:A2 in the Excel worksheet.

While holding down the CTRL key, point the mouse pointer at the solid line around the cells that you selected until the mouse pointer becomes an arrow with a plus sign (+). Still holding down the CTRL key, drag the selected cells to the Access table, and then release the mouse button.

NOTE: By holding down the CTRL key during the dragging operation, you ensure that the data is copied from Excel to Access. If you don't hold down the CTRL key, the data is cut from Excel. If you mistakenly cut the data, you can click Undo on the Edit menu in Excel to retrieve the data.

When you release the mouse button to drop the data onto the table, the title of the Access table begins to flash. This happens because the program is waiting for your response. Click anywhere in the Access table. Note that you receive a message that states that you are about to paste two records into the table. Click Yes to confirm that you want to paste the two records.

Note that the two records are pasted into Field1 of the table. To paste data into a table field other than Field1, select the fields in the table where you want the data to be placed before you start to drag the cells from Excel. If you select only one table field, only the first column in the selection from Excel is pasted. If you try to repeat this and paste the second column into Field 2, Access appends the data as new records rather than adding the data to the existing records. Access is designed this way because it is a relational database program and does not behave in the same manner as a spreadsheet program.

Additional query words:


Keywords          : kbinterop kbdta IntpOff IntpOle 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: August 9, 1999