Exporting to Excel and refreshing data

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

Hi:
I have a simple Access 2k app that allows users to select
individual people from a list and send them (and their
biographical data) to a spreadhseet via the
transferspreadsheet function in VBA - all works well.
What I would like to be able to do is, once the
spreadsheet has been created, if a phone number (or
whatever) changes in the database, then the spreadsheet
would be updated as well. The problem is that the query
used to create the original spreadsheet changes so
frequently that it will likely not contain the same
records. Therefore, the data updates would have to come
from the main table (and synchronize by way of the
primary key). Any thoughts? Thanks,
-Rick
 
Stop using Excel?

Access is a multi-user app.
Have the users keep their info up to date in a form in Access.
 
That would be a great solution if Access were as simple
to use as Excel as far as performing calculations etc.
Also, the main reason for allowing end users to export to
Excel is to give them the option of adding fields to
track data that is unique and doesn't need to be in the
main database (i.e. the personnel department wants a
spreadsheet of all of the company employees so they can
keep track of who has turned in their emergency cards).
Using Access would mean users would have to know how to
add fields etc. and now you've got a major learning
curve. If it can't be done, that's fine, I just thought
I would ask :)
Thanks,
-Rick
 
In principle you could do this by setting up an Excel template so that
every time one of these workbooks is opened it queries the database to
refresh its data. (Doing it by pushing the data from Access to workbooks
that users might have moved or copied or renamed and that might be in
use at the time sounds isn't my idea of fun.)

But if users are allowed - as it seems - to edit these workbooks as they
please, adding and deleting columns, merging cells and generally mucking
up the data (no data types and precious little validation in Excel,
remember) even having the workbook update itself would be a chancy
business. Even assuming your code could find the column with the primary
key, how could it be sure which fields in the database correspond to
which columns in the worksheet?

If data integrity matters, store the data in a relational database. You
don't need to allow users or departments to add fields any time they
think of something new to store about a person. Instead do it
relationally: each new thing to be stored (emergency card, date of
grandmother's last funeral, class of driving licence, whatever) becomes
a record in one table with a many-to-many relationship with the main
table.

It means moving the Access database and its user interface to another
level of sophistication - but IMO it will be much less work than making
a reliable Access/Excel solution. And it will also give you a central
location for this valuable information rather than, as now, storing the
same facts in all sorts of different places trusting to luck and the
talents of individual employees that you don't lose some of them.

That would be a great solution if Access were as simple
to use as Excel as far as performing calculations etc.
Also, the main reason for allowing end users to export to
Excel is to give them the option of adding fields to
track data that is unique and doesn't need to be in the
main database (i.e. the personnel department wants a
spreadsheet of all of the company employees so they can
keep track of who has turned in their emergency cards).
Using Access would mean users would have to know how to
add fields etc. and now you've got a major learning
curve. If it can't be done, that's fine, I just thought
I would ask :)
Thanks,
-Rick
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Hey!
That's what I said!!!

But I used short hand.
I like your lengthier explanation better.
(I just can't type that much. I am down to one error per word!)
--
Joe Fallon
Access MVP



John Nurick said:
In principle you could do this by setting up an Excel template so that
every time one of these workbooks is opened it queries the database to
refresh its data. (Doing it by pushing the data from Access to workbooks
that users might have moved or copied or renamed and that might be in
use at the time sounds isn't my idea of fun.)

But if users are allowed - as it seems - to edit these workbooks as they
please, adding and deleting columns, merging cells and generally mucking
up the data (no data types and precious little validation in Excel,
remember) even having the workbook update itself would be a chancy
business. Even assuming your code could find the column with the primary
key, how could it be sure which fields in the database correspond to
which columns in the worksheet?

If data integrity matters, store the data in a relational database. You
don't need to allow users or departments to add fields any time they
think of something new to store about a person. Instead do it
relationally: each new thing to be stored (emergency card, date of
grandmother's last funeral, class of driving licence, whatever) becomes
a record in one table with a many-to-many relationship with the main
table.

It means moving the Access database and its user interface to another
level of sophistication - but IMO it will be much less work than making
a reliable Access/Excel solution. And it will also give you a central
location for this valuable information rather than, as now, storing the
same facts in all sorts of different places trusting to luck and the
talents of individual employees that you don't lose some of them.

That would be a great solution if Access were as simple
to use as Excel as far as performing calculations etc.
Also, the main reason for allowing end users to export to
Excel is to give them the option of adding fields to
track data that is unique and doesn't need to be in the
main database (i.e. the personnel department wants a
spreadsheet of all of the company employees so they can
keep track of who has turned in their emergency cards).
Using Access would mean users would have to know how to
add fields etc. and now you've got a major learning
curve. If it can't be done, that's fine, I just thought
I would ask :)
Thanks,
-Rick
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Click to expand...
 
Back
Top