need procedure for exporting from Access to Excel

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I am looking for a brief tutorial on setting up a procedure whereby
when a record is added to an Access database, a record is created in an
Excel spreadsheet.

I'm not yet familiar with VB, but if that's what I need to work with, I
can work it out.

Thanks in advance. This would really simplify my work life.
 
Hi Kevin,

There are various ways of doing this - but first you should think
carefully about whether it is really necessary. You'd be creating two
independent copies of your data, and if anyone edited either the the
Access table or the Excel sheet you'd have two different versions, and -
perhaps - no way of knowing which was authoritative.

Normally it's best to store your data in Access and export to Excel as
and when you need it exported, not to store it in both.
 
Kevin,

I support Jophn's comments on this subject; however, if it is an absolute
necessity, then the easiest way to do this would be to link to the Excel file
as a table, then write the code to append the record to the Excel table as
you would any other table. Be aware, though, that communicating with an
Excel spreadsheet will significantly slow down execution. As John said, the
better approach would be to periodically export your data using the
TransferSpreadsheet. It would take less coding and give better performance.
 
John said:
Hi Kevin,

There are various ways of doing this - but first you should think
carefully about whether it is really necessary. You'd be creating two
independent copies of your data, and if anyone edited either the the
Access table or the Excel sheet you'd have two different versions,
and - perhaps - no way of knowing which was authoritative.

Normally it's best to store your data in Access and export to Excel as
and when you need it exported, not to store it in both.
Click to expand...
======================================================

Thanks John. The sociology of this is that I have several users who are
comfortable with Excel, which they're happy to use read-only, but very
unwilling to learn Access.

So... what I'd like to do is have a modal dialog that will come up
every time a record is created in Access that asks the user whether
she/he wants to duplicate the record in Excel.

What would be really spiffy would be a process that allows edits to be
made in either the Access or the Excel side, but record creation only
at the Access end. That would ensure that there was always a primary
key to identify the record to be overwritten. I wouldn't worry about
data collisions--Last One In Wins.
 
Last One In Wins.
LOL, That is great. It has now become my model for handling concurrency.
 
Thanks John. The sociology of this is that I have several users who are
comfortable with Excel, which they're happy to use read-only, but very
unwilling to learn Access.
Click to expand...

I still feel it would be preferable to keep the data in Access and copy
it to Excel when an Excel user needs it. This is especially true if the
users don't need to edit the workbook (I assume that's what you mean by
read-only): that means you can just export the data to a new workbook
every time.

This doesn't require the Excel users to understand Access. Excel is
quite capable of importing an Access table or query, and the process can
be automated with VBA; or you could have a little Access application
with a button that launches a macro with TransferSpreadsheet to export
the data; and so on.
So... what I'd like to do is have a modal dialog that will come up
every time a record is created in Access that asks the user whether
she/he wants to duplicate the record in Excel.
Click to expand...

You can only do this in Access by ensuring that users only create or
edit records via forms and not by working directly in table or query
datasheets. Subject to that condition it's not too difficult, provided
you can ensure that users never rename or move the Excel workbook or the
data you're trying to update. You'd just use something like this in the
AfterUpdate event of the form:

Display a MessageBox asking if the record just appended should be
duplicated in Excel. If so, use VBA code to either

1) build and execute a SQL query that selects the record in the Access
table whose primary key is the same as the current primary key value on
the form and appends it to the table in Excel; or

2) a) launch Excel and open the workbook
b) find the bottom row of the data
c) if found, write the values from the fields on the form
into the corresponding cells on the worksheet
d) save and close the workbook
e) close the instance of Excel.

(1) is simpler but more vulnerable to things the users may have done on
the worksheet (e.g. adding a totals row at the bottom).

(2) lets you write code that explores the worksheet and responds to what
it finds (e.g. if it finds a totals row, insert a blank row above it,
write the data there, and adjust the formulas in the totals row).

Either way you're likely to hit problems if one user has the workbook
open when another is trying to update it via Access.
What would be really spiffy would be a process that allows edits to be
made in either the Access or the Excel side, but record creation only
at the Access end. That would ensure that there was always a primary
key to identify the record to be overwritten. I wouldn't worry about
data collisions--Last One In Wins.
Click to expand...

I take it this means that edits in the Excel sheet get written back to
the Access sheet. This would only make things three to ten times as
difficult<g>.
 
John said:
I still feel it would be preferable to keep the data in Access and
copy it to Excel when an Excel user needs it. This is especially true
if the users don't need to edit the workbook (I assume that's what
you mean by read-only): that means you can just export the data to a
new workbook every time.

This doesn't require the Excel users to understand Access. Excel is
quite capable of importing an Access table or query, and the process
can be automated with VBA; or you could have a little Access
application with a button that launches a macro with
TransferSpreadsheet to export the data; and so on.


You can only do this in Access by ensuring that users only create or
edit records via forms and not by working directly in table or query
datasheets. Subject to that condition it's not too difficult, provided
you can ensure that users never rename or move the Excel workbook or
the data you're trying to update. You'd just use something like this
in the AfterUpdate event of the form:

Display a MessageBox asking if the record just appended should be
duplicated in Excel. If so, use VBA code to either

1) build and execute a SQL query that selects the record in the Access
table whose primary key is the same as the current primary key value
on the form and appends it to the table in Excel; or

2) a) launch Excel and open the workbook
b) find the bottom row of the data
c) if found, write the values from the fields on the form
into the corresponding cells on the worksheet
d) save and close the workbook
e) close the instance of Excel.

(1) is simpler but more vulnerable to things the users may have done
on the worksheet (e.g. adding a totals row at the bottom).

(2) lets you write code that explores the worksheet and responds to
what it finds (e.g. if it finds a totals row, insert a blank row
above it, write the data there, and adjust the formulas in the totals
row).

Either way you're likely to hit problems if one user has the workbook
open when another is trying to update it via Access.


I take it this means that edits in the Excel sheet get written back to
the Access sheet. This would only make things three to ten times as
difficult<g>.
Click to expand...

=====================================================================
Ah, a little humor lightens things up!


Your option (1) does what I want, as long as it will allow adding more
than one record. Users aren't totalling anything.

I haven't started my VBA class yet: If you have can point me to code to
accomplish your (1), I'd be grateful.

Also--slightly off-topic--I seem to remember that some form of BASIC
shipped with Access. Is that so, do you know?

Thanx.....
 
John Nurick wrote:
Click to expand...
Your option (1) does what I want, as long as it will allow adding more
than one record. Users aren't totalling anything.
Click to expand...

As described, (1) allows only one record to be added at a time, a record
corresponding to the record just added to the Access table. As far as i
can remember, the same general approach will also work to update an
existing record in the Excel sheet after the form has been used to
update its counterpart in the Access table, but it does not allow
records to be deleted from the Excel sheet.

And let me say again that I feel the whole idea is misconceived and
unreliable.
I haven't started my VBA class yet: If you have can point me to code to
accomplish your (1), I'd be grateful.
Click to expand...

I'm not going to try and give you a ready-made solution, (a) because I'd
need to know a lot more than I do about the data, the workbook and what
the users do and need, and (b) because while the basic approach is
fairly simple it will take a lot of error trapping, testing and
debugging to make it work reliably in real-world use.

I think that the absolute minimum, which should work in ideal
circumstances, is this:

1) create a linked table connected to the workbook and worksheet in
question.
2) create an append query that appends records from the Access table to
the linked table. Include a criterion consisting of a parameter that
limits it to the record whose primary key value is displayed on your
form. (if necessary look up Parameter Queries in help). The SQL view of
the query will look something like this:

INSERT INTO LinkedTable (FieldPK, Field2, Field3)
SELECT (MyTable.FieldPK, MyTable.Field2, MyTable.Field3)
FROM MyTable
WHERE (MyTable.FieldPK = Forms!MyForm!txtFieldPK);

3) In the AfterUpdate event procedure of the form, put something like
this:

If MsgBox("Add this record to the worksheet", _
vbQuestion + vbYesNo) = vbYes Then
CurrentDb.Execute MyAppendQuery, dbFailOnError
End If

This minimal approach has many problems. For instance, there's no
protection against adding duplicate records to the Excel sheet. To
achieve that, you'd need to modify the AfterUpdate code to

-run DCount() against hte linked table to see if there are already any
records with the same primary key value
-if none, execute the append query as above
-if one, execute a similar update query instead
-if more than one, do something else.

By the time you've got it working reliably, you'll know quite a lot
about VBA, SQL and automating Excel.
Also--slightly off-topic--I seem to remember that some form of BASIC
shipped with Access. Is that so, do you know?
Click to expand...

That's VBA.
 
Back
Top