Re: need procedure for exporting from Access to Excel

On Fri, 22 Jul 2005 03:53:56 GMT, "Kevin" <kevind@xxxxxxxxx> wrote:

>John Nurick wrote:

<snip>

>> 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

<snip>
>> Either way you're likely to hit problems if one user has the workbook
>> open when another is trying to update it via Access.
<snip>
>
>
>Your option (1) does what I want, as long as it will allow adding more
>than one record. Users aren't totalling anything.

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 *** 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 ***.

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.

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 work*** 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 work***", _
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 ***. 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?

That's VBA.
--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Loading