Export 3 SQL Server 2008 tables to Different Tabs in Excel using VB.net

I want to export to excel from 3 different tables into 3 different tabs corresponding tables in excel.

Tables are:
Table 1 : IDNo    Year    Period   ReportDate
           
Table 2 : IDNo    Year    Period   ReportDate   Employee   AvgHours

Table 3 : IDNo    Year    Period   ReportDate   Sales    TotalHours

Please see Example Output what I would like to accomplish.

Thank you.
OutputFrom_3SQLServer2008Tables.xlsx
Queennie LAsked:
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.

lcohanDatabase AnalystCommented:
You could do that via T-SQL pretty easy like in selects below (and you can execute them via VB.net if you really want that) but note the excel output will be on the SQL server where you execute these queries and make sure the Excel doc exists and the three sheets have the exact same structure as your SQL tables:


insert into OPENROWSET(
      'Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=c:\export\sales.xls;;HDR=YES',
      'SELECT * FROM [Sheet1$]')
select * from Clients;

insert into OPENROWSET(
      'Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=c:\export\sales.xls;;HDR=YES',
      'SELECT * FROM [Sheet2$]')
select * from Products;

insert into OPENROWSET(
      'Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=c:\export\sales.xls;;HDR=YES',
      'SELECT * FROM [Sheet3$]')
select * from Sales;
Queennie LAuthor Commented:
I have an error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
lcohanDatabase AnalystCommented:
Argh...64 to 32 bit looks like...I think you'll need the JET redistributable AccessDatabaseEngine_x64.exe and you should be able to find/download it from Microsoft over the internet.

or this"Microsoft Access Database Engine 2010 Redistributable"
http://www.microsoft.com/en-us/download/details.aspx?id=13255


Maybe try this befre anything:

http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-provider-is-used-to-run-in-apartment-mode/

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
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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
Visual Basic.NET

From novice to tech pro — start learning today.