| 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