Importing data into Access

  • Thread starter Thread starter Martin May
  • Start date Start date
M

Martin May

Apologies if this is a FAQ or me being plain dopey, but I've been using
Access for some time now and have never managed to successfully import data
from programs such as Excel without carefully getting every record in
exactly the right order. This can obviously be a very onerous task with lots
of records. Is there a facility for Access (97) to accept data and check
that the right input data ends up in the right record?
 
Is there a facility for Access (97) to accept data and check
that the right input data ends up in the right record?
Click to expand...

You will have to define what you mean by data being put in the right reocrd.

What do you mean by that?

Are you talking about order of data, or are you trying to import data into
existing records?
 
Is there a facility for Access (97) to accept data and check
that the right input data ends up in the right record?
Click to expand...

Major apparent misconception here:

An Access Table HAS NO ORDER. It's an unordered "bag" of data. When
you add new records to a table, whether by importing from some
external file or by any other method, Access will put the new records
wherever there is space - often they will be stored sequentially, but
there's no guarantee whatsoever.

If you want to see the records in a particular sequential order, you
must - no option! - use a Query sorting the records by one or more
fields within the record.
 
Forgot to mention a couple of other details:

If you find import errors in Access it could be because of improper data in
Excel. I had a collection of reports that others were supposed to enter
only numerical data, but instead they had an occasional improper text entry
instead. Removing/fixing the non-compliant data fixed the import error
situation.

I am using Access2000/Excel2000. There may be a few differences in the
layout, but i am guessing it will be very similiar in operation to
Access97/Excel97.

By following the methods detailed in my previous post I have found my data
is imported flawlessly into Access from Excel.
 
My problem is adding extra data to records that already exist in tables.
Click to expand...

aha! Import is the WRONG TOOL in that case. Importing data creates new
records.

Instead, use File... Get External Data... Link to link to the
spreadsheet, and create an Update query joining your existing table to
the linked table; update the field to

=[LinkedTableName].[fieldname]
 
Hi Martin,

Martin said:
Many thanks to everyone for giving up their time to help.
I realised whilst in bed(!) that I had been ambiguous with my request. My
problem is adding extra data to records that already exist in tables. It's a
database that I have written for my school. I often receive data for pupils
on floppy disk that I need to add to their existing records. I frequently
end up copying and pasting this data one pupil at a time, but would very
much like to know if a facility exists to import the data to the correct
pupil automatically.
Click to expand...

Neither Access nor Excel "know" which data go with which pupil, nor
which pieces of information go in which tables, unless you tell them how
the information is related. Migrating data from other formats into a db
always involves some effort on the data manager's part, even if it's
just following the prompts from the Import Spreadsheet Wizard. Are you
adding new pupils & their records, updating existing students' records,
or both? In either case, you'll need to bring the data into your
database using File | Get External Data | Import (linking to a file on a
floppy is too problematic). If you're trying to APPEND new data to your
tables, just follow the Wizard's instructions for adding new data to
existing tables. If you're wanting to UPDATE existing records in your
tables, that's a whole 'nother ball game. Import the spreadsheet as a
new, temp table and create an Update query joining the temp table to the
table containing the information that needs to be updated. See the OLH
for help on how to create an Update query.

hth,

LeAnne
 
Back
Top