Connecting Access with Excel?

[Dave]

If someone could point me in the right direction (web site) that is easy to
understand that would be awesome or explain to me the process I want to go
through.
Also, If you do supply a site, could you also explain to me the basics of
whats going on, that would be great.
Note: I always reply if responses help me or not.

Using Access '03 and Excel '03...

I want to connect excel and access. Here is what I want to do.

Lets say I have an Access Table with Employee Info. (names, pay, etc.) and
an Excel document (blank).

I want the Excel document to have a column that is connected to a column in
the acesss Table. I.E. the Names of the Employees.

Then, I want the second column in the Excel document to be link to a column
in the same table in Access. I.E. Pay rate

Next, I would like to be able to alter and enter information in the Excel
doc. for columns 3 and on.

Here is the catch. I would like for the information in excel that is
refecting the access table to be changed or altered if I change information
in the Access Table.
I.E. Change of pay or more importantly, Add/Delete employees.

I do not care if I change info. in excel and it does not affect access.

Is this possible??

 

[a a r o n . k e m p f]

you need to get rid of Excel, dude
Excel doesn't support multiple users, it doesn't really even support -
ONE- user--

so you can't even have the spreadsheet open (to edit) while you run
something like this

It's best to store _EVERYTHING_ in a database; and if you need the
_RESULTS_ in Excel, then you should learn Analysis Services cubes
(SSAS).
PivotTables on Steroids are only available using SSAS (or using
offline cubes in Excel before 2007)

-Aaron

 

[BruceM]

I can't provide specifics, so will need to leave somebody else to reply, but
in the meantime you should ignore Aaron. He is correct that an Excel file
can be edited by one person at a time, but it is absurd to suggest it
doesn't support one user.

 

[Dave]

Aaron,

I am aware of this however, many individuals in my office are not
knowledgeable of access but are for excel. It is the simplicity of Excel
that I am looking for not the technology.

Thank you for your reply.
-Dave

 

[Golfinray]

Excel files will link directly to Access tables. When you update the Excel
spreadsheet, the data is automatically added to your tables.
You have two things, file/get external data and then you also have a linked
table manager under tools.

 

[Dave]

Golfinray,

I believe you are on to something however, I do NOT want my Access tables to
update. I want my excel worksheets. I am unsure if this is what you meant.
I understand that you most likely will have to open and update the excel
document but as long as it can accurately read the Access table I do not
care.


Thanks,
Dave

 

[John W. Vinson]

Golfinray,

I believe you are on to something however, I do NOT want my Access tables to
update. I want my excel worksheets. I am unsure if this is what you meant.
I understand that you most likely will have to open and update the excel
document but as long as it can accurately read the Access table I do not
care.

Microsoft unfortunately lost a lawsuit a year or so ago, and were obliged to
remove the ability to directlyl update Excel spreadsheets from an Access
database.

You can store the data in Excel, and use File... Get External Data... Link to
view (but not edit) the data in the spreadsheet; you can create queries
linking the data in the spreadsheet to other Access tables (still not
updateable). Or you can import and export the spreadsheet.

Or, you can use Excel to link to an Access table or query as if it were a
spreadsheet. I'm not experienced with this technique, you might want to ask in
an Excel newsgroup.

 

[a a r o n . k e m p f]

Access Data Projects are the only technology that gives you the
ability for multiple people to (enter / edit) (data / tabledesign) at
the same time.

Move to SQL Server if you want something that 'just works'

and for the record-- if you lke Excel so much, you should learn
something about PivotTables and Cubes.

Only through this method can you avoid stale data and concurrency
problems.

Microsoft Analysis Services = 'PivotTables on Steroids'

-Aaron

 

[a a r o n . k e m p f]

a) one person can't edit a spreadsheet in two different ways at the
same time
b) I use SQL Server, and I am _CONSISTENTLY_ editing multiple windows,
multiple servers, multiple databases-- and I never have locking
problems
c) If you look towards Jet to solve your problems-- you'll always have
concurrency problems

 

[BruceM]

a) What are you talking about? I can only perform one edit at a time due to
my being one person. You?

b) You must be very proud of yourself.

c) If a person has one answer in every situation there most likely will be
problems.

message
a) one person can't edit a spreadsheet in two different ways at the
same time
b) I use SQL Server, and I am _CONSISTENTLY_ editing multiple windows,
multiple servers, multiple databases-- and I never have locking
problems
c) If you look towards Jet to solve your problems-- you'll always have
concurrency problems

 

[Dave]

John,

I am just curious, if Microsoft lost the lawsuit a year or so ago, Shouldn't
Access and Excel '03 still provide the function????

 

[John W. Vinson]

John,

I am just curious, if Microsoft lost the lawsuit a year or so ago, Shouldn't
Access and Excel '03 still provide the function????

Nope. One of the service packs killed the ability, as required by law.

 

[a a r o n . k e m p f]

Re:
a) What are you talking about? I can only perform one edit at a time
due to
my being one person. You?


I FREQUENTLY HAVE TWO PROGRAMS OPEN AT THE SAME TIME, AGAINST THE SAME
DATABASE.
AND I CAN WRITE QUERIES AND EDIT TABLES IN TWO WINDOWS, OR IN TWO
TERMINAL SESSIONS-- AT THE SAME TIME.
This seems like a fairly ordinary thing to do.

Thus, MS Access doesn't even support _ONE_ user.
Thus, MS Access doesn't even support _ONE_ user.
Thus, MS Access doesn't even support _ONE_ user.
Thus, MS Access doesn't even support _ONE_ user.

-Aaron