Generating an Excel doc from a DB Query

[nirl]

Hi Guys,
I am a VB newbie.
I was given a small task, it is to generate an SQL query to an Access MDB file, and output the result to an Excel file.
My question is how do you generate the query, and if there is a way to export this table easily to excel.
I am not too familiar with this new environment. Btw, I have Visual Studio 2005.

Thanks in advance
N

[RobDog888]

If you add a reference to MS Access you can connect to your db using ADO.NET. Then create an instance of Access.Application and use its TransferSpreadsheet passing the queryname and Excel format version.

[nirl]

From your words it does sound simple.
What do you mean by reference and ADO.net ?
Is this something you implement as a query in the DB that you can call afterwards from VB, and store back into excel ?
An example would help me a lot because I am not familiar with VB at all.
Thanks for the quick response !

[RobDog888]

Visual Studio 2005 is .NET. It is allot to explain in 2005 but I have a link to a tutorial ms site for some of this.

http://msdn.microsoft.com/vstudio/ex...g/default.aspx

[nirl]

Wow that's agreat site. I liked the idea that they explain things o well there, I didn't go through them all, though, I focused on the DB area but they talk about the SQL server, and I am dealing with Access MDB file. moreover, they don't say how to store a table in an excel file...
What should I do ?

[RobDog888]

If you add a reference to Access in your project you can use the Access Object Model to do a Application.DoCmd.TrasferSpreadsheet call and in that one line of code you can output your Access table records to a new Excel spreadsheet.

That is probably the easiest way but for more control and formatting and stuff you will want to use ADO.NET to connect to your db and then use the Excel Object Model to manipulate Excel.

[nirl]

RobDog888, could you please help me out with a piece of code to accomplish that ? It would help me a lot, thanks.

[RobDog888]

This is 2003 code but should be similar.

Assumes the desired query is "qryExcel1" and outputs to an Excel workbook in the root of your C drive.

'Export access query to Sheet1
Option Explicit On 
Option Strict On
'Add a reference to MS Access xx.0 Object Library
Imports Microsoft.Office.Interop
 
Public Class Form1
    Inherits System.Windows.Forms.Form
 
    Private moApp As Access.Application
    Private mbKillMe As Boolean
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            moApp = DirectCast(GetObject(, "Access.Application"), Access.Application)
            mbKillMe = False
        Catch ex As Exception
            If TypeName(moApp) = "Nothing" Then
                moApp = DirectCast(CreateObject("Access.Application"), Access.Application)
                mbKillMe = True
            Else
                MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
                MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End If
        End Try
        If mbKillMe = False Then
            If moApp.CurrentDb.Name <> "C:\RobDog888.mdb" Then
                moApp.OpenCurrentDatabase("C:\RobDog888.mdb")
            End If
        Else
            moApp.Visible = True
            moApp.OpenCurrentDatabase("C:\RobDog888.mdb")
        End If
        moApp.DoCmd.SetWarnings(False)
        Try
            moApp.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport, _
            Access.AcSpreadSheetType.acSpreadsheetTypeExcel9, "qryExcel1", "C:\Book1.xls", False, "Sheet1$")
            Me.Activate()
            MessageBox.Show("Export Done!", "VB/Office Guru™ Access Demo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "VB/Office Guru™ Access Demo", _
            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
        moApp.DoCmd.SetWarnings(True)
    End Sub
 
    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        If mbKillMe = True Then
            Try
                moApp.CurrentDb.Close()
                moApp.Quit(Access.AcQuitOption.acQuitPrompt)
            Catch
                'No longer opened
            End Try
            moApp = Nothing
        End If
    End Sub
End Class

[nirl]

Hey thanks alot for the code !
I would not be able to use my computer until tomorrow, so just wanted to say that I appreciate your contribution very much, and I will start doing it as soon as I can.
Thanx again

[RobDog888]

Np, just post back as there may be a few issues between my 2003 code and 2005.

[nirl]

Hi RobDog888,
My environment does not recognize the namespace Microsoft.office as well as Access.Application, the intellisense does not see those at all. Am I missing any components ?

[RobDog888]

Did you add the references and have Office installed?

[nirl]

Copied the code as is and I have office installed, it is the second computer I have tried it on, and still same result

[RobDog888]

You also need to add a reference to Access as I had commented in my code

'Add a reference to MS Access xx.0 Object Library

[nirl]

Sorry if I am being such a newbie, but how do you add a reference, what do you mean ?

[RobDog888]

I dont have 2005 installed but in 2003 it was "Project > Add References..." menu. Then select the COM tab and select MS Access xx.0 Object Library.

[nirl]

Gees mate, you're amazing. It did solve it.

regarding this part:
If mbKillMe = False Then
If moApp.CurrentDb.Name <> "C:\Projects\DBReport\SmartPlug.mdb" Then
moApp.OpenCurrentDatabase("C:\Projects\DBReport\SmartPlug.mdb")
End If
Else ...

The app states that moApp.CurrentDb.Name is "Nothing", meaning that it couldn't open the DB, (of course, I changed the DB to be my own DB). For some reason, it can't open the DB. more than that, why does the watch not showing the contents of that object ? It states System.__ComObject, and there is no casting on that. Is that a casting issue ? I am C++ guy and not too familiar with COM

[nirl]

Well, RobDog888 ! Your code works great !
What happened to me was that there was a silent process of MSACCESS.EXE running, which prevented the application to run correctly. So, it was a matter of openning my eyes to it.

Can I generate SQL Query instead of just giving a table name ? And if so, is there another syntax ?
I tried "SELECT [Index] FROM Table1" but get an error.
I also saw examples showing a CreateQuerydef, but also those don't accept the SQL phrase.
Is this formatting only ?

[RobDog888]

If you need to do queries against your db then its better to use ADO.NET as its better performing then the Access Object Model would be executing a query.

[nirl]

Can I use ADO.NET to export an excel file like in the great example you gave me ?
My mission is this:
1. Get an SQL query from "C:\XYZ.mdb"
2. Generate an Excel1.xls file from the result.
3. MD5 it
I can take care of everything except for the DB-Excel part.
Do you know how to use ADO.NET to get the result and export it to Excel ?
Thanks for the great help, I've learnt so much from you so far.
N

[RobDog888]

Ok, easiest way to get there is to execute an SQL statement to create a query in your Access db. The pass the query as the soiurce for the TransferSpreadsheet method. Then you just left with the MD5 part.

moApp.DoCmd.RunSQL("CREATE VIEW qryMyQuery AS SELECT ...")

[nirl]

There are few things I want to understand:
1. Can I get an object out of the SQL query signifying the object I need to pass to TransferSpreadSheet (A table) ?
2. What is the type of object that TransferSpreadSheet is working on ?

Could you please show me how the code might be looking like ?
I'd expect:

Set table1 = moApp.DoCmd.RunSql("SELECT [index],[Name] FROM Users WHERE [index]=\"1234\""

TransferSpreadSheet .. , .. , .. , table1, "C:\Book1.xls","Sheet1"

How can I write this properly, Great thanks,
N

[whiteWay]

I might be wrong, but you cannot use an SQL statement to export data.

If this is so, then you can export a table using the following:

'create a data table and then run the command

moApp.DoCmd.TransferSpreadsheet acExport, 8, "dataTable", "h:\excelWB", True, ""

[nirl]

Are you saying that ADO can help me better ?

[RobDog888]

No, TransferSpreadSheet can have a table or a premade query as its source. So if you execute a sql statement to create a query and then pass that query as the source you will be done.