Copying data from excel to access

[Guest]

I have an excel database that is constantly being updated with new records.
For ease of access I have now set it all up in an access database. My problem
is that I have to use excel for the updates but I am concerned about linking
my access tables to the excel worksheets as i'm not always on the network
where the excel file is located. My question is whether there is a quick way
to hard copy new records from excel into access. When i try a simple
copy/paste it tries to copy all cells into the first field box.

As you can probably tell i'm a newbie so simple responses appreciated.

Thanks

 

[John Vinson]

I have an excel database that is constantly being updated with new records.
For ease of access I have now set it all up in an access database. My problem
is that I have to use excel for the updates but I am concerned about linking
my access tables to the excel worksheets as i'm not always on the network
where the excel file is located. My question is whether there is a quick way
to hard copy new records from excel into access. When i try a simple
copy/paste it tries to copy all cells into the first field box.

As you can probably tell i'm a newbie so simple responses appreciated.

Thanks

I'd suggest having a (permanent) table in Access, with all the proper
field types, a Primary Key if that's appropriate (it almost always
is), indexes, and so on.

When you need to get the data from Excel, run a Delete query to empty
the table; use File... Get External Data... Link to link to the data
entry spreadsheet; and run an Append query to move the data from the
spreadsheet into the table.

Any reason you can't set up a user-friendly Form to do the data entry
in Access, if the only purpose of the Excel is for data entry???

John W. Vinson[MVP]

 

[Guest]

Thanks John,

Will give that a try and see how it goes. Very new to access so will have to
look up use of append and delete queries but thanks for your response.

In regards to reason for not entering data in Access. The data is coming
from a couple of different survey packages and I have a greater knowledge of
how to organise them into excel than I do Access. Will soon be moving to just
the SNAP survey package so will attempt to directly link from there
eventually but for the moment have to go through the tedious process listed
above.

Cheers,

Matt

 

[John Vinson]

In regards to reason for not entering data in Access. The data is coming
from a couple of different survey packages and I have a greater knowledge of
how to organise them into excel than I do Access.

Just note that Excel and Access are VERY different programs; an
excellent spreadsheet design will almost by definition be a badly
non-normalized Access design, and vice versa. If you're doing survey
data this is particularly common: in Excel you'ld typically have one
column per question, which is very poor design in a relational
database!

Check out Duane Hookum's _At Your Survey_ database at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane for
a different approach which may save you some effort.

John W. Vinson[MVP]