Linking Excel Spreadsheets to an Access Database
Thread Starter
Join Date: Feb 2006
Location: Here and there
Posts: 216
Likes: 0
Received 0 Likes
on
0 Posts
Linking Excel Spreadsheets to an Access Database
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
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
Join Date: Feb 2000
Location: asia
Posts: 542
Likes: 0
Received 0 Likes
on
0 Posts
Linking
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
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
Thread Starter
Join Date: Feb 2006
Location: Here and there
Posts: 216
Likes: 0
Received 0 Likes
on
0 Posts
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?
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?
Join Date: Feb 2000
Location: asia
Posts: 542
Likes: 0
Received 0 Likes
on
0 Posts
