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?
ALABI ADEWALEAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OddyWriterMr.Commented:
There is a good example.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.