Export Access DB to Excel

[dj982020]

Is there a way to export an entire database to Excel and keep the relationships intact?

DJ

 

[BNPMike]

In a relational database the relationships are defined by data ie by matching values in foreign key fields and the referenced key field(s). If you export a series of tables into Excel the relationships will therefore follow. What good it'll do you rather depends on what you do next.

 

[SkipVought]

DJ,

What's the objective of exporting a database into Excel? Why?

Why not Query the database from Excel?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[dj982020]

We are trying to make the database viewable via the Web. The person in charge of doing this wants the database in comma or tab delimited format. I want each Access table to be a different Excel sheet. I don't know how excel keeps track of relationships between tables.

 

[SkipVought]

You can query Access Tables via Data/Get External Data -- Access Files -- Access Table -- [Next],[Next],[Next],[Finish]

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[dj982020]

Okay, that gets a table into Excel, but how do I know anything about relationships. My original question was about keeping the relationships intact. Maybe I just don't know how to get to the information in Excel.

Thanks for your help,
DJ

 

[BNPMike]

What do think "relationships" means? Tell us and we'll give you a solution.

What happens in Access's relationships dialogue is arcane and confusing. The only positive thing it does is allow referential integrity but you need something like Jet to understand that. Excel is a spreadsheet and spreadsheets don't have any analagous feature.

 

[SkipVought]

What BNPMike says is true. This will likely NOT be a simple "out of the box" solution. You will need to "reverse engineer," in a sense, the relationships that Mike referred to. These interrelationships can be built into the queries that you store in MS Query if you perform compound joins.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[BNPMike]

Sorry SkipVought, I'd forgotten MS Query. However you would question why anyone would want to move a relational database into Excel if they were then going to issue SQL on tables.

 

[SkipVought]

I dunno??? They want an Excel look & feel/front end in their web app????

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[BNPMike]

Hey, Skip, I thought the days when you gave users what they wanted had long gone.

 

[SkipVought]

why not an .asp using VB Script to query your database?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244