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.
-
Close all databases (including the birding database) you are working
on.
-
In MS Access, click File menu and select new database.
-
In the dialog box that appears, click the General tab and select Blank
database
-
Click OK to create a blank database
-
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.
-
Click Create to create a new database with this name.
-
Close this temporary database
-
Open your source database and go to the table TAB.
-
Click on the 'Sightings' table but do not open it.
-
Click File Menu and then 'Save As/Export'
-
In the dialog box that follows, check the 'To an External File or Database'
button and then click OK.
-
Select the name of the temporary database as your external file.
This is the same filename that you entered just now.
-
Click Export.
-
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.
-
Type a table name to be exported to e.g. 'SightingsKaisoon2001Oct'.
Then click OK.
-
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
-
You are still working in the source database i.e. your birding database.
If not, open the source database.
-
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.
-
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.
-
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.
-
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.
-
Click the Edit Menu and then Select All Records.
-
Click the Edit Menu and select COPY. This will copy the selected
records to the clipboard.
-
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.
-
Close your source database.
B: Alternative method using a form
-
If you find it hard to select the records by using the query, you can alternatively
open the SightingsFormForExport
-
Select records by clickig the Records Menu and then Filter by form or Advanced
Filter/Sort.
-
Select your filter criteria using any one or more of the fields.
-
Click Filter Menu and then Apply Filter/Sort
-
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.
-
The rest of the copy procedure is similar to that with the query method.
C: Paste the clipboard contents into the empty table.
-
Open the temporary database created in section A and click the table
TAB. You should see the new table you created in section A.
-
Open that table. This table has exactly the same design as the 'Sightings'
table.
-
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.
-
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.
-
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.
-
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.