Linking Excel Spreadsheets to an Access Database

[Oxeagle]

First off, i'd like to say thanks to all of you who have helped me with many technical questions, and I now come to you again with another situation! Here's the problem:

I have an Access database, which is the central data system, and an Excel spreadsheet (actually lots of spreadsheets, but i'll try to keep it as simple as possible for now!) which contains data on a company's products. What I would like to do is to link the Spreadsheet to the Database in such a way that the spreadsheet's data can be viewed in real time in the database, and data can be entered into the database via a form, which then updates the spreadsheet. How would I go about setting up a system like this? The only problem is that, for the moment, it has to be done without importing the spreadsheet into the database as it is required to remain in place because it is updated, and it updates, other external spreadsheets.

Any help would be greatly appreciated!

Cheers,


Ox

[stickyb]

Should be easy.

Go to File/Get External Data/Link Tables and then change the file type to xls.

Select the spreadsheet, and away you go.

I am using Access 2000, don't know if the menu has changed in later versions

[Oxeagle]

stickyb,

Problem is that it doesn't automatically update, you have to reopen Access to allow it to recognise the changes. Also, it won't allow me to insert a new entry between existing rows in the linked spreadsheet. Or I havn't figured out how to yet, which is more than likely! Basically, I want to be able to access and modify the Spreadsheet as if it was part of the database, whilst keeping it seperate.

Any more suggestions?

* Just had a thought - Would it be possible to write a macro or VB procedure to re-import data from the Spreadsheet every time a change was made in the database? Or would this take too long or drain system resources?

[stickyb]

stickyb,

Problem is that it doesn't automatically update, you have to reopen Access to allow it to recognise the changes.

I think the refresh or requery property will solve that problem