Importing Excel spreadsheet data into existing Access DB
- by Keeb13r
I've designed an Access 2003 DB with 3 tables: APPLICATIONS, SERVERS, and INSTALLATIONS. Records in the APPLICATIONS and SERVERS tables are uniquely identified by a synthetic primary key (in Access, an "auto number"). The INSTALLATIONS table is essentially a mapping table between APPLICATIONS and SERVERS: it's a list of records of which applications are installed on which servers. A record in the INSTALLATIONS table is also identified by a synthetic primary key, and it consists of an APPLICATION_ID and SERVER_ID for the records in their respective tables.
I have an Excel 2003 spreadsheet I would like to import into this database, but it's proving difficult. The spreadsheet is made up of several tabs/worksheets, each one representing a server with its own listing of installed applications. I'm not sure how to proceed with an import - the "Get External Data -- Import" feature in Access has an import "In an Existing Table" option, but it's greyed out. I'm also unsure how I build the relationships between applications and servers for importing records into the INSTALLATIONS table.
I had previously fooled around with adding some security to the Access DB file. I think I removed everything but perhaps I didn't and that's causing the problem?
Some sample data from the Excel spreadsheet:
SERVER101
* Adobe Reader 9
* BMC Remedy User 7.0
* HostExplorer 2008
* Microsoft Office 2003
* Microsoft Office 2007
* Notepad++
SERVER102
* Adobe Reader 9
* DameWare Mini Remote Control
* Microsoft Office 2003
* Microsoft .NET Framework 3.5 SP1
* Oracle 9.2
SERVER103
* AWDView
* EXTRA! Personal Client 32-bit
* Microsoft Office 2003
* Microsoft .NET Framework 3.5 SP1
* Snagit 9.1
* WinZip 12.1
The Access DB design is very simple:
APPLICATION
* APPLICATION_ID (autonumber)
* APPLICATION_NAME (varchar)
SERVER
* SERVER_ID (autonumber)
* SERVER_NAME (varchar)
INSTALLATION
* INSTALLATION_ID (autonumber)
* APPLICATION_ID (number)
* SERVER_ID (number)
