Linking an Access Database to an Excel Spreadsheet
I created an Access database and wanted to import the data from an existing Excel spreadsheet. As users input data, it will also import it into Access. The problem I ran across when I linked the Access database to the Excel spreadsheet is that it would not allow other users to input the data onto the Excel Spreadsheet. Is there a way that I can link the Access database with Excel and still have other users to be able to input data onto the Excel spreadsheet?
Re: Linking an Access Database to an Excel Spreadsheet
You cant use the XL sheet if its attached and in use.
So , Save the 1 excel file to a generic file like File2Import.xls (a copy of the original, staged to import)
LINK this file as an attached table in access.
build your queries to import the data to the various tables (these queries should have WHERE [field] is not null, so you dont get the blanks)
put all this in a macro: mImportXLfiles
now the import process is just 2 steps:
1. Get a new excel file, save it to the same import name everytime, File2Import.xls
2. run the import macro.
All tables are filled.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By awan0126 in forum Excel Programming / VBA / Macros
Last Post: 06-29-2011, 10:00 AM
By excelaspire0219 in forum Excel General
Last Post: 02-10-2009, 10:50 AM
By chrismann85 in forum Excel Programming / VBA / Macros
Last Post: 01-08-2008, 01:35 PM
By PABHL in forum Excel General
Last Post: 06-08-2006, 03:40 PM
By ahmebah in forum Excel - New Users/Basics
Last Post: 02-11-2005, 11:06 AM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1