Can MS Acess be linked to an Excel worksheet?

[Madhart]

I'm taking a shot in the dark here but can MS Acess be linked to an Excel
worksheet? At work we use Acess(gatelog) to track people in and out. We
then transfer those times for people manually to an Excel worksheet. Is
there any (hopefully easy) way to link the two so the Excel worksheet is auto
updated with all the same information? If so how would I go about that and
what all would need to be involved?

Thanks.

 

[david]

You can't update an Excel worksheet. Somebody got a patent for that and
tried to screw money out of MS.

You might be able to append data to a spreadsheet, or even create a
new spreadsheet.

Start by using the menu command File | Save As/Export to create a
spreadsheet. When you have that working, you can create a macro
or form that does the same thing using docmd.TransferSpreadsheet.

(david)

 

[Rick Brandt]

I'm taking a shot in the dark here but can MS Acess be linked to an
Excel worksheet? At work we use Acess(gatelog) to track people in
and out. We then transfer those times for people manually to an
Excel worksheet. Is there any (hopefully easy) way to link the two
so the Excel worksheet is auto updated with all the same information?
If so how would I go about that and what all would need to be
involved?

Thanks.

One used to be able to do so with a simple link, but then Microsoft lost a
lawsuit and had to issue an update to remove that capability. Now links to
Excel are read only. You can still use automation to do so. You could also
have Excel query the data from Access (pull rather than push).

What exactly are you doing in Excel that you couldn't just do with Access?
Seems like that would eliminate the problem as well.

 

[posted_by_anonymous]

or.. JUST A SIMPLE ADO RECORDSET
that way you don't need Excel installed, I believe

Related to original post? Even remotely? Even very, very remotely?

When are you going in for your craniorectomy, troll?

Aunty No-ny-muss

 

[david]

I don't think an ADO Recordset would work, but I haven't tried it.
The way the patch was introduced was odd an bizarre, so I don't
think there is any sensible way to predict the behaviour of an ADO
recordset other than trying it.

As I recall, the patch was a patch to MSACCESS.exe, (so linked
tables can't be updated), but Jet was patched to use MSACCESS.exe
instead of the Excel IISAM, (so DAO recordsets can't be updated)
or something like that.

In any case, given the lack of explicit documentation from MS, the
only way to find out if an ADO recordset on an Excel spreadsheet
is updatable is to try it and see.

(david)

 

[George Hepworth]

The best way to deal with trolls is to ignore them with one exception.
Sometimes it is necessary to correct an inaccurate or misleading statement.

However, most posters are smart enough to figure out very quickly who the
trolls are all by themselves.

George

 

[a a r o n . k e m p f]

why don't you think that you could use an ADO recordset to update data
in Excel?

I do this all the time!

 

[a a r o n . k e m p f]

re:
but can MS Acess be linked to an Excel
worksheet


YES, the OP was asking about updating Excel from Access.
I choose to do this through ADO because ADO is rock solid and it works
against hundreds of database engines.

DAO only works against JET; and it has too many layers for effective
troubleshooting

 

[david]

That is the reason Aaron. People assume whatever you say is worth only
as much as everything else you say.

I've done my best, pointing out that what you implied may be true, but
I doubt if anyone is listening.

(david)


message
why don't you think that you could use an ADO recordset to update data
in Excel?

I do this all the time!