Adding info from Excel to Access

[Red Lorakeet]

I have an existing access database - loads of fields - 2 of which are
process number and keywords.

I have a spreadsheet which is produced on a daily basis which contains a
field called process number and keyword.

All I want to do is automatically - if possible get this info into the
database. The process number obviously needs to link to the same process
number in both applications.

It seems too simple but I'm struggling!

Thanks

 

[Jeff Boyce]

You don't mention whether the Excel data is new, or reflects "updates".
Your use of the phrase "link to the same process number" implies that you
have matching process numbers in your Access and your Excel data, and are
only interested in updating.

First, why are you keeping the same data (process number & keyword) in two
locations (Access & Excel)? Does some of the data (?Excel) go away?

Second, why in Access? What is it about Access that makes it preferable to
Excel for keeping this data?

Finally, take a look at setting up a link to the Excel data as a
"quasi-"table. This way you'd be able to create queries against the data as
if it actually were inside of Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Red Lorakeet]

Thanks for your response, much appreciated.

The access database contains all kind of information on a project that is
running at the moment.

A separate project running at the same time (using different software)
involves imputting loads of questions and answers into it. I am then able
to get an excel spreadsheet generated out of it which I want to use in order
to imput the keywords which relate to each process number.

The original access database contains (amongst others) the two fields that
are in the spreadsheet, process number and keywords. The process number is
already filled in but the keywords field is blank at the moment - to be
filled in by the info in the spreadsheet.

As you say, I think the answer is to import the spreadhsheet into access as
a table and then I think I can do an update query?

 

[Jeff Boyce]

I'd recommend not bothering to "import" the data. That, in Access
vocabulary, means to make a copy of the data. Instead, use File | Get
External ... | Link ... to link to the Excel spreadsheet file.

Then create an update query that updates the Access table field (?keyword?)
to the value in the corresponding Excel "record" (joining your Access table
and Excel {linked} "table" on the Process#/ID). First step is to create a
select query that returns what you expect to see, then convert it to an
update query that updates as above.

Regards

Jeff Boyce
Microsoft Office/Access MVP