Using Excel to view/edit Access data

Giganews Newsgroups
Subject: Using Excel to view/edit Access data
Posted by:  internet.shoppi…@foobox.com
Date: Mon, 20 Jul 2020

I am interested to discover whether it is possible, feasible and simple to =
create an Excel spreadsheet linked to Access data table and queries.

I have developed an Access database with up to 100 tables and 100 queries. =
It offers a great deal of functionality, displaying data in forms both con=
tinuous and single, and various reports.  Some output is produced by creati=
ng Excel spreadsheets.

In addition to this some users create their own spreadsheets using Access=
=E2=80=99 built in facility to export data to a spreadsheet, typically from=
continuous forms.  These can be useful in that users can easily sort and f=
ilter data in ways not already set up in the database.

I point out to the users that both reports and spreadsheets created in this=
way are static in that new or edited data in the database are not updated =
in the spreadsheet.  In this way the spreadsheets always out of date.

The database has been developed and evolved over some 20 years and there ar=
e about 50 or so simultaneous users.

It turns out that many users create and keep their own spreadsheets. Someti=
mes these start with the export approach, sometimes they are created manual=
ly from scratch.  This involved manually copying data from the database int=
o the spreadsheet.  Clearly this is time consuming and inefficient.  Nevert=
heless the user feels to be in control and has ownership of  his or her spr=
eadsheets.  They also feel that they are busy at work even though it could =
be made more efficient!!

I want to explore the possibility of users creating their own spreadsheets =
by linking them directly to the database so that these automatically keep u=
p to date.

I see that in Excel it is possible to obtain data from an Access data sourc=
e.  All the tables and queries are then exposed.

How easy is it to then create new queries in Excel should the required ones=
not already exist?

I foresee a number of problems not the least the skill or lack of it on the=
part of the users.

Although I try to use meaningful names for tables, queries and fields they =
may not be sufficiently clear to others.  The tables also contain fields th=
at are not normally visible to the user because they are there background a=
nd housekeeping reasons.  For example, tables may have Autonumber primary i=
ndices that are not usually visible.

Apologies for this being a bit rambling but I am trying to find out whether=
others have followed this course. =20
If so what are your experiences? =20
What advice would you give? =20
Is it really feasible?

Jim

Replies