• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 66
  • Last Modified:

Please how do I export Multiple table at once to one Excel sheet from my ACCESS dBase using VBA or any other available means.....? I have used different codes and it did not work. Please help me out.

I already build a dBase, but I want to be pooling some report from the Access dbase to Excel. What I want to do is to export 9 tables all at once to one Excel sheet, which will already be designated in the document of the System. How do I do this?
0
ALABI ADEWALE
Asked:
ALABI ADEWALE
  • 3
  • 2
  • 2
1 Solution
 
OddyWriterMr.Commented:
1. In case of 9 tables to 1 spreadsheet in 1 workbook:
It depend on the structure of your 9 tables. If they are same structure, you can do it easier.

2. In case of 9 tables to 9 spreadsheet in 1 workbook:
If the structure of 9 tables in access in difference. VBA is easy to do

Please clarify your requirement.
0
 
PatHartmanCommented:
If the tables are all the same format, create a union query and export the union query.

Although - having 9 tables in the same format does sound like a design flaw but that's a different problem.
0
 
ALABI ADEWALEAuthor Commented:
Thanks all. The tables have the same format, with same fields. Only the fields hold information for different groups. the required information in similar, but from different set of people.  E.G. in a secondary school settings, each of these classes have a no. of pupil, and all their information in similar throughout. Now, their information are all in different tables, I want to get out all the students information into one excel worksheet, with just a click. This is the case!

I will appreciate your helpful contributions! Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PatHartmanCommented:
Based on that comment, you should review some database design articles.  Everything belongs in a single table rather than 9 separate tables.  Having 9 tables makes 9 times the work for you due to having to create extra forms/reports/queries, etc.
0
 
ALABI ADEWALEAuthor Commented:
Thanks, but since the table hold similar information for different groups I think it's ok, because individual table is been updated on a daily bases. I have also created forms for each, where the update takes place, the issue is just to export all of them at once to excel by clicking a tab. Please find the attached file
DBASE-STUDENTS.accdb
0
 
PatHartmanCommented:
Do you thing that the University of Connecticut's student application has a separate table for each class?  No, they don't.  That design would lead to hundreds of tables and hundreds of forms and require quarterly changes because classes are different every semester.

Relational databases are not spreadsheets.  All this data belongs in a single table.  Use criteria in your RecordSource query or a filter to select the data for one of the "tables".

I've attached a simplistic example that shows how you can filter a form to show a subset of the data from a single table.
DBASE-STUDENTS_Modified.accdb
0
 
OddyWriterMr.Commented:
There is a good example.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now