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 continuous and single, and various reports. Some output is produced by creating Excel spreadsheets.
In addition to this some users create their own spreadsheets using AccessrCO built in facility to export data to a spreadsheet, typically from continuous forms. These can be useful in that users can easily sort and filter 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 are about 50 or so simultaneous users.
It turns out that many users create and keep their own spreadsheets. Sometimes these start with the export approach, sometimes they are created manually from scratch. This involved manually copying data from the database into the spreadsheet. Clearly this is time consuming and inefficient. Nevertheless the user feels to be in control and has ownership of his or her spreadsheets. 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 up to date.
I see that in Excel it is possible to obtain data from an Access data source. 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 that are not normally visible to the user because they are there background and housekeeping reasons. For example, tables may have Autonumber primary indices 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.
If so what are your experiences?
What advice would you give?
Is it really feasible?
JimAssuming your production database is MDB or ACCDB and not SQL server.
On Monday, July 20, 2020 at 9:32:34 AM UTC-5, internet...@foobox.com wrote:
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 continuous and single, and various reports. Some output is produced by creating Excel spreadsheets.
In addition to this some users create their own spreadsheets using AccessrCO built in facility to export data to a spreadsheet, typically from continuous forms. These can be useful in that users can easily sort and filter 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 are about 50 or so simultaneous users.
It turns out that many users create and keep their own spreadsheets. Sometimes these start with the export approach, sometimes they are created manually from scratch. This involved manually copying data from the database into the spreadsheet. Clearly this is time consuming and inefficient. Nevertheless the user feels to be in control and has ownership of his or her spreadsheets. 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 up to date.
I see that in Excel it is possible to obtain data from an Access data source. 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 that are not normally visible to the user because they are there background and housekeeping reasons. For example, tables may have Autonumber primary indices 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.
If so what are your experiences?
What advice would you give?
Is it really feasible?
--- Synchronet 3.21d-Linux NewsLink 1.2Jim
Assuming your production database is MDB or ACCDB and not SQL server.
Because of Access limited security on MDB and ACCDB data, I would not allow users to create Excel connections to you production database. If the connection is read/write, they could edit records outside of your application control.
An alternative is to create an MDB/ACCDB with simplified table structure then update the tables with changes to your production database. If a user destroys the data, you can easily rebuild the database.
That's a good point Ron. If is were possible to establish a read-only connection the approach may be feasible.
I doubt however if the general user has the necessary skills to create queries or use data from tables.
Jim
On Monday, 20 July 2020 16:17:35 UTC+1, Ron Paii wrote:
On Monday, July 20, 2020 at 9:32:34 AM UTC-5, internet...@foobox.com wrote:
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 continuous and single, and various reports. Some output is produced by creating Excel spreadsheets.
In addition to this some users create their own spreadsheets using AccessrCO built in facility to export data to a spreadsheet, typically from continuous forms. These can be useful in that users can easily sort and filter 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 are about 50 or so simultaneous users.
It turns out that many users create and keep their own spreadsheets. Sometimes these start with the export approach, sometimes they are created manually from scratch. This involved manually copying data from the database into the spreadsheet. Clearly this is time consuming and inefficient. Nevertheless the user feels to be in control and has ownership of his or her spreadsheets. 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 up to date.
I see that in Excel it is possible to obtain data from an Access data source. 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 that are not normally visible to the user because they are there background and housekeeping reasons. For example, tables may have Autonumber primary indices 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.
If so what are your experiences?
What advice would you give?
Is it really feasible?
In your OP you statedJim
Assuming your production database is MDB or ACCDB and not SQL server.
Because of Access limited security on MDB and ACCDB data, I would not allow users to create Excel connections to you production database. If the connection is read/write, they could edit records outside of your application control.
An alternative is to create an MDB/ACCDB with simplified table structure then update the tables with changes to your production database. If a user destroys the data, you can easily rebuild the database.
I want to explore the possibility of users creating their own spreadsheets by linking them directly to the database so that these automatically keep up to date.Have you users link to the new simplified database instead of your production (your application will keep the data updated). Another alternative is to maintain Excel spreadsheets (linked to your application). Users would use these excel tables in their spreadsheets. For less technical users create template spreadsheets with all the linking already done.
On Tuesday, July 21, 2020 at 5:55:28 AM UTC-5, internet...@foobox.com wrote:
That's a good point Ron. If is were possible to establish a read-only connection the approach may be feasible.
I doubt however if the general user has the necessary skills to create queries or use data from tables.
Jim
On Monday, 20 July 2020 16:17:35 UTC+1, Ron Paii wrote:
On Monday, July 20, 2020 at 9:32:34 AM UTC-5, internet...@foobox.com wrote:
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 continuous and single, and various reports. Some output is produced by creating Excel spreadsheets.
In addition to this some users create their own spreadsheets using AccessrCO built in facility to export data to a spreadsheet, typically from continuous forms. These can be useful in that users can easily sort and filter 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 are about 50 or so simultaneous users.
It turns out that many users create and keep their own spreadsheets. Sometimes these start with the export approach, sometimes they are created manually from scratch. This involved manually copying data from the database into the spreadsheet. Clearly this is time consuming and inefficient. Nevertheless the user feels to be in control and has ownership of his or her spreadsheets. 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 up to date.
I see that in Excel it is possible to obtain data from an Access data source. 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 that are not normally visible to the user because they are there background and housekeeping reasons. For example, tables may have Autonumber primary indices 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.
If so what are your experiences?
What advice would you give?
Is it really feasible?
--- Synchronet 3.21d-Linux NewsLink 1.2Jim
Assuming your production database is MDB or ACCDB and not SQL server.
Because of Access limited security on MDB and ACCDB data, I would not allow users to create Excel connections to you production database. If the connection is read/write, they could edit records outside of your application control.
An alternative is to create an MDB/ACCDB with simplified table structure then update the tables with changes to your production database. If a user destroys the data, you can easily rebuild the database.
In your OP you stated
I want to explore the possibility of users creating their own spreadsheets by linking them directly to the database so that these automatically keep up to date.
Have you users link to the new simplified database instead of your production (your application will keep the data updated). Another alternative is to maintain Excel spreadsheets (linked to your application). Users would use these excel tables in their spreadsheets. For less technical users create template spreadsheets with all the linking already done.
Thanks. That's all very helpful
The problem is that individuals could have asked long ago but either they don't know they can or don't think they should. Everyone has elementary knowledge of Excel and use it for all sorts of purposes almost like a scrapbook. Very few have any real idea of its many capabilities.
The company is very successful in terms of growth and profit but is poorly managed. Individual managers do not know explicitly what their own admin staff do. No real training it provided and a new employee usually picks up what they need to do from the person next to them!!!
As an example of lack of training. I was looking at some Word documents that were sent out as letters to customers. Staff do not appear to know how to set tabs and certainly not decimal tabs. Lists of money are simply left justified !!!
Jim
On Tuesday, 21 July 2020 14:38:17 UTC+1, Ron Paii wrote:
On Tuesday, July 21, 2020 at 5:55:28 AM UTC-5, internet...@foobox.com wrote:
That's a good point Ron. If is were possible to establish a read-only connection the approach may be feasible.
I doubt however if the general user has the necessary skills to create queries or use data from tables.
Jim
On Monday, 20 July 2020 16:17:35 UTC+1, Ron Paii wrote:
On Monday, July 20, 2020 at 9:32:34 AM UTC-5, internet...@foobox.com wrote:
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 continuous and single, and various reports. Some output is produced by creating Excel spreadsheets.
In addition to this some users create their own spreadsheets using AccessrCO built in facility to export data to a spreadsheet, typically from continuous forms. These can be useful in that users can easily sort and filter 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 are about 50 or so simultaneous users.
It turns out that many users create and keep their own spreadsheets. Sometimes these start with the export approach, sometimes they are created manually from scratch. This involved manually copying data from the database into the spreadsheet. Clearly this is time consuming and inefficient. Nevertheless the user feels to be in control and has ownership of his or her spreadsheets. 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 up to date.
I see that in Excel it is possible to obtain data from an Access data source. 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 that are not normally visible to the user because they are there background and housekeeping reasons. For example, tables may have Autonumber primary indices 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.
If so what are your experiences?
What advice would you give?
Is it really feasible?
SOP ;[Jim
Assuming your production database is MDB or ACCDB and not SQL server.
Because of Access limited security on MDB and ACCDB data, I would not allow users to create Excel connections to you production database. If the connection is read/write, they could edit records outside of your application control.
An alternative is to create an MDB/ACCDB with simplified table structure then update the tables with changes to your production database. If a user destroys the data, you can easily rebuild the database.
In your OP you stated
I want to explore the possibility of users creating their own spreadsheets by linking them directly to the database so that these automatically keep up to date.
Have you users link to the new simplified database instead of your production (your application will keep the data updated). Another alternative is to maintain Excel spreadsheets (linked to your application). Users would use these excel tables in their spreadsheets. For less technical users create template spreadsheets with all the linking already done.
| Sysop: | Amessyroom |
|---|---|
| Location: | Fayetteville, NC |
| Users: | 59 |
| Nodes: | 6 (0 / 6) |
| Uptime: | 24:08:31 |
| Calls: | 810 |
| Calls today: | 1 |
| Files: | 1,287 |
| D/L today: |
12 files (21,036K bytes) |
| Messages: | 195,978 |