Linking Data within Tables

[Obie25]

Hi everyone,

I have a quick question. And perhaps a strange setup. We have an excel spreadsheet set up with a bunch of data.

The powers that be would like me to set up a database utilizing the spreadsheet as the foundation of the database. However, they would still like to keep the spreadsheet as one of the primary ways to enter/edit data along with the database.

Therefore, I was wondering if there was anyway..that I can set up a link(?)
between the access database and the excel spreadsheet in such a way that if one is editted the other one would be editted accordingly.

I know this is a replication of data etc etc. But this is what I was asked to do. Im not sure if it can be done anyway.

I know I can link in the excel spreadsheet into the access database...but can I create a table..based on the linked information..and add more rows and column to it?

Let me know your thoughts!

Thanks I appreciate any help!

 

[Fizzio]

Yes it can be done......easily.
Use the Link Tables wizard (File -> Get External Data -> Link Tables) and select the XL spreadsheet as your data source.

Be aware though that XL is essentially a flat file data storage system and therefore creating relationships such as 1-to-many is nigh impossible and as since the majority of data storage is with this relationship in mind

Custumer => Orders
Order => Order Items
etc.

You really have an uphill struggle on your hands.

Better to create a robust normalised data structure in Access and 'persuade' the powers that be that it will be a long term investment.

I have a client who had developed in Excel and wanted more help linking his XL sheets - the coding was unbelievable. I created the Access solution and he has not looked back (well all except one stubborn employee! )

 

[Pat Hartman]

I would recommend against using dual input. It is a recipe for corruption. Databases are designed to be shared for data entry, spreadsheets are not. If you want to export the data back to a spreadsheet for some reason, fine. Include an export button for them but do all the updating within Access.