Results 1 to 2 of 2
Thread: Access Data in Excel (2003)
2009-01-30, 22:58 #1
- Join Date
- Jun 2002
- Pittsburgh, PA
- Thanked 10 Times in 9 Posts
Access Data in Excel (2003)
I have an Access database that includes tblAll. Each record in tblAll has about 40 fields of information on one of the 1400 members of our organization. Each record has a unique field called Handle, which is similar to a customer ID.
I have a separate Excel worksheet that serves a totally different purpose from the Access database. It's used as a flat database with about 50 records, each pertaining to a member of the organization. Though it's for a different purpose, about five of its fields are identical to those in tblAll -- they contain the name and contact information for the members.
Everything works fine, except it's a pain to keep the data in the two applications synchronized. When somebody moves or changes email address, I have to update both the Access and the Excel. Sometimes I forget, and things get sent to the wrong address.
I'd like to use the Access data as the master data, and somehow link to it from Excel. I don't need real-time updating of the Excel data -- I could do it every few weeks when I print out the Excel stuff. It seems like this should be possible, but I'm not sure if it is. Nor am I sure how much trouble it would be. At this point I'm just looking for advice on feasibility.
Any ideas?Lou Sander
2009-01-30, 23:15 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: Access Data in Excel (2003)
You can use Data | Import External Data | New Database Query... to import the table into your workbook. Excel will start MSQuery to let you specify the database and the source table.
When you return to Excel, you'll see the Import Data dialog. Click Properties... to specify how the data will be updated.
Back in the Import Data dialog, specify that you want to import into a new worksheet.
You can always update the data by clicking in the imported table and selecting Data | Refresh Data.
In your original sheet, you can use lookup formulas (for example using VLOOKUP) to refer to the imported Access table.