Exporting Selected Records from MS Access

Created on 4 Oct 2001
For enquiry, email Dr Chan Kai Soon

Overview

This file is created to help you export selected bird sightings records from your birding database (the source database) to a temporary database. These records can then be pasted into another database (the target database) in another computer which need not be networked with your own computer. As long as there is a way to transfer/copy the temporary database to the other computer (via email or diskettes), your birding data can be appended to the target database if both the source and target databases have exactly the same table design. The procedure may seem complicated but it is probably the only way for you to add and pool your birding data to another computer which is not networked to your computer.

The export procedure involve three stages as described in sections A to C below.

This file (Export-Records.html) describes the export procedure. To learn how to import the records into the target database,  read the Import-Records.html file. The principle is however the same i.e. copy records from source table to clipboard and then paste from clipboard to target table.

Before you proceed, it is suggested that you make a backup copy of your databases in another folder in case of any error or damage. You can then copy them back to your original folder and retry.



A: Create a temporary database with an empty table.
  1. Close all databases (including the birding database) you are working on.
  2. In MS Access, click File menu and select new database.
  3. In the dialog box that appears, click the General tab and select Blank database
  4. Click OK to create a blank database
  5. Type in a meaningful name for the blank database, e.g. KaiSoon Export 2001Oct. It is recommended that this database be created in the same folder as your source (birding) database.
  6. Click Create to create a new database with this name.
  7. Close this temporary database
  8. Open your source database and go to the table TAB.
  9. Click on the 'Sightings' table but do not open it.
  10. Click File Menu and then 'Save As/Export'
  11. In the dialog box that follows, check the 'To an External File or Database' button and then click OK.
  12. Select the name of the temporary database as your external file. This is the same filename that you entered just now.
  13. Click Export.
  14. In the dialog box that follows, check the 'Definition Only' button. Do not check the 'Definition and Data' button. You do not need to export data at this stage because you have not yet selected the records.
  15. Type a table name to be exported to e.g. 'SightingsKaisoon2001Oct'. Then click OK.
  16. A new table with the name you provided will be created in the temporary database although you cannot see it now. This table has exactly the same definition/design as the 'Sightings' table but does not contain any record.
B: Select records from a query and and copy them to the clipboard
  1. You are still working in the source database i.e. your birding database. If not, open the source database.
  2. Open the query named SightingsQueryForExport. If this query is not available, you can create it from the Sightings table using the 'Simple Query wizard". You should use ALL fields in exactly the same sequence i.e. by clicking the '>>' to add all fields.
  3. You will be prompted to input selection criteria to select the records. This is usually the birder name and date after which records were computerized. You can customize the criteria to your needs by re-designing this query.
  4. After you enter all criteria required, you will see a table of all records that meet your criteria. This is presented in the data sheet format.
  5. Browse through these records to check if they are the correct records you want. Note the number of records on the status bar. This will be the number of records to be exported.
  6. Click the Edit Menu and then Select All Records.
  7. Click the Edit Menu and select COPY. This will copy the selected records to the clipboard.
  8. Close the query and answer Yes to save contents to the clipboard. Note that if there are too many records to be added to the Clipboard and you have little memory, it will take a long time for the query to close.
  9. Close your source database.
B: Alternative method using a form
  1. If you find it hard to select the records by using the query, you can alternatively open the SightingsFormForExport
  2. Select records by clickig the Records Menu and then Filter by form or Advanced Filter/Sort.
  3. Select your filter criteria using any one or more of the fields.
  4. Click Filter Menu and then Apply Filter/Sort
  5. You will now see that the number of records available is decreased depending on your criteria and the word (Filtered) appears next to the total number of records available. You can review or browse the records by changing to datasheet view instead of form view to determine whether these records are the ones you want to be copied.
  6. The rest of the copy procedure is similar to that with the query method.
C: Paste the clipboard contents into the empty table.
  1. Open the temporary database created in section A and click the table TAB. You should see the new table you created in section A.
  2. Open that table. This table has exactly the same design as the 'Sightings' table.
  3. Click the Edit Menu and then select Paste Append. Note that if there are too many records to be pasted, the append process may take a long time. If  Paste Append is not available from the Edit Menu, close the database and repeat section B.
  4. Click Yes to confirm records to be appended. All records from the clipboard will be pasted and the number of records in the table will increase by the same number copied in section B.
  5. You can browse through this table to re-check if all records pasted are there. If there are records you do not want to export, you can mannually delete them one by one. If some records are missing, you may want to select more records using other appropriate criteria (i.e. repeat section B), copy them to the clipboard and paste append them again.
  6. Close the table and then the temporary database. The temporary database file is now ready for sending to another computer.

Note: In section B, if you 'Cut' instead of 'copy' you will be literally moving records from one table to another, making this a means of archiving your records to an external database. As the SightingsID field is an 'autonumber' field, 'cut' and paste will change this 'autonumber' value. This means data which depends on the SightingID value will no longer be valid. This situation applies to the Video table. If you want to use the Video table to catalog your video or photos, do not 'cut' records from the sightings table.

In the above procedure, you are exporting records from the sightings table. If you had added new records to your Tripname table (likely to have been done) or other tables (birders and location tables) you need to export these tables to the same temporary database as well. The new values in these tables will be required for the import process to work.


You may want to rename the temporary database and archive it to a different folder.

Back to FAQ.