Linking Excel with Access tables .....

[m.cringle]

Hi folks,

I have created a spreadsheet in order to make it simpler and quicker to
input a large number of entries in one sitting. The first sheet in the
workbook is a copy of my main table and a number of other sheets
display the secondary tables (which are used for combo boxes in the
main table). My reasoning behind this is so that users can copy and
paste the relevant text to prevent data inconsistencies.

My problem is - I want to link the individual Excel sheets with their
corresponding tables in the database so that when the spreadsheet is
opened they automatically refresh.

However - I have tried the usual way but because of my userlevel
security, Excel wont let me access the database (even with Admins
permission).

Is there any way to set something up in Access to make it export when I
click a link, in order to avoid the security problems? Or alternatively
a way to avoid these problems in Excel?

Just let me know if there is any more details you need

Much appreciated!

 

[Guest]

Unfortunately you probably can't do it if I understand what you are
attempting. Recently Microsoft got into a patent infringement case and has
turned off the ability to update Access through a linked Excel worksheet and
visa versa.

I use to teach this technique and last semester while teaching an Access
class I found out the hard way that it no longer worked. I sure looked like a
fool in front of my students - more than normal even!

You might consider Data Access Pages and having your users get to the
database through a web interface instead.

 

[m.cringle]

Hi Jerry

Are you talking specifically about an Access database with user-level
security or just in general? A collegue knows how to do this and has
created a similar situation with another database and works fine.
However, this other database is completely unsecured.

(We are using office 2003 if that makes any difference)

 

[Guest]

All of Access including 2003 and XP. Maybe even Access 2000. It was done
during a service pack/release. If he doesn't have automatic updates on and
hasn't updated manually, he's safe. However if and when someone installs the
latest updates for Office, he'll probably have some grief on his hands.

It has nothing to do with user level security. It's a limit to the
functionality of Access-Excel links.