How to export SQL statement results to an Excel File

Question

I have an Access DataBase and a form in Excel VBA. All the data I input into the DB is input through the VBA form.

This DB contains all the benefits cards we already received this year in the company. But the same employee can ask for the card twice or more, so we'll have more than one record on the DB for him.

What I need is when the number of records is greater than one, the SQL statement result should appear in a Excel report.

I use the SELECT (*) COUNT statement to know when there is more than one record that is compatible with the search criterion. But I can't make the result appear in an Excel file.

Here is my code:

Public Function Relatorio()
    Dim sql As String
    Dim cn  As ADODB.Connection
    Dim rs  As ADODB.Recordset
    Dim rel As String

    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & enderecoDB & ";Jet OLEDB:Database"
    cn.Open
    Set rs = New ADODB.Recordset
    sql = "INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=" & enderecoDB & ";', 'SELECT * FROM [Planilha1$]') SELECT * FROM controle WHERE BP = " & controlectform.nmbpbox.Value & ";"
    rs.Open sql, cn
End Function

When I run this code it gives me a message saying something like:

Can't locate the OPENROWSET Table exit

I'm not able to install new programs, so I need to do this using only Excel VBA and the Access DB.

How can I make this work?

try to put this query in access as a view and than query the new view from vba — Asaf
– Asaf, Commented Apr 5, 2017 at 19:13
I use a field in the form to make the "select". Even if I put the query in access can I continue using the form field to make the "select"? — Humberto Faria
– Humberto Faria, Commented Apr 5, 2017 at 19:22

Jimmy Smith · Accepted Answer · 2017-04-06 14:10:47Z

I don't believe Access supports the OPENROWSET, dynamic table you're working with there. I have a lot of old projects that do this though, so here's my method

Public Function Relatorio()

    Dim sql As String
    Dim cn  As ADODB.Connection
    Dim rs  As ADODB.Recordset
    Dim rel As String


    Set cn = New ADODB.Connection

    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & enderecoDB & ";Jet OLEDB:Database"

    cn.Open

    Set rs = New ADODB.Recordset

    dim path_To_XLSX
    dim name_of_sheet
    path_To_XLSX = "c:\temp\output.xlsx"
    name_of_sheet = "Planilha1"
    sql = sql = "SELECT * INTO [Excel 12.0;Database=" & path_To_XLSX & "]." & name_of_sheet & " FROM controle WHERE BP = '" & controlectform.nmbpbox.Value & "';" 

    rs.Open sql, cn

    'If this application is in an unsecure environment, use the following code instead!  This is to prevent a SQL injection, security concern here.
    'As it is an Access Database, this is likely overkill for this project
    'Create Command Object.
    Set Cmd1 = New ADODB.Command
    Cmd1.ActiveConnection = cn
    cmd1.CommandText = "SELECT * FROM controle INTO [Excel 12.0;Database=" & path_To_XLSX & "]." & name_of_sheet & " WHERE BP = ?"
    ' Create Parameter Object.
    Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) 'use adVarchar for strings(versus adInteger), https://www.w3schools.com/asp/met_comm_createparameter.asp
    Param1.Value = controlectform.nmbpbox.Value
    Cmd1.Parameters.Append Param1
    Set Param1 = Nothing
    Set Rs = Cmd1.Execute()

End Function

Your answer helped me a lot. Excel can't accepted the sql statement. Gives me an error on FROM clause. I searched in the web and I found it another sql statement that works fine with the code. sql = "SELECT * INTO [Excel 12.0;Database=" & path_To_XLSX & "]." & name_of_sheet & " FROM controle WHERE BP = '" & controlectform.nmbpbox.Value & "';" Thank you very much.
I had INTO twice. Will edit my answer with your fix. Glad this is helping!

Community · Accepted Answer · 2017-05-23 12:25:39Z

0

I had this challenge so many years ago that I cant remember but this link ring the bell. check if it help.

https://stackoverflow.com/a/28889774/382588

try { connw.Open(); OleDbCommand command; command = new OleDbCommand( "Update Deliveries " + "SET Deliveries.EmployeeID = ?, Deliveries.FIN = ?, Deliveries.TodaysOrders = ? , connw); command.Parameters.Add(new OleDbParameter("@EMPID", Convert.ToDecimal(empsplitIt[1]))); command.Parameters.Add(new OleDbParameter("@FIN", truckSplit[1].ToString())); command.Parameters.Add(new OleDbParameter("@TodaysOrder", "R")); catchReturnedRows = command.ExecuteNonQuery();//Commit connw.Close(); } catch (OleDbException exception) { MessageBox.Show(exception.Message, "OleDb Exception"); }

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Apr 6, 2017 at 1:43

Asaf

3,12511 gold badges40 silver badges56 bronze badges

2 Comments

Jimmy Smith Over a year ago

This is definitely a good way to do a parameterized query in C#, but in this case he's working within Access which uses VBA (VB6 era, not VB.NET). I'm actually just about to write one of these queries for a VB.NET app (minus the curly braces of course lol)

Asaf Over a year ago

I believe the command string is the trichy part... if i'm right (not sure)... taking a c# oledb-with command function and embed this query might wotk... this is what i woyld try

ASH · Accepted Answer · 2017-04-14 12:52:46Z

you can use this, to print the actual SQL.

Private Sub Command2_Click()

Dim db As Database
Dim qr As QueryDef

Set db = CurrentDb

For Each qr In db.QueryDefs
  TextOut (qr.Name)
  TextOut (qr.SQL)
  TextOut (String(100, "-"))
Next

End Sub

Public Sub TextOut(OutputString As String)

    Dim fh As Long

    fh = FreeFile
    Open "C:\Users\rs17746\Desktop\Text_Files\sample.txt" For Append As fh
    Print #fh, OutputString
    Close fh

End Sub

Here is one more version for you. This will export the results of each query, each to a separate text file.

Private Sub Command0_Click()


Dim qdf As QueryDef
Dim strFileName As String
For Each qdf In CurrentDb.QueryDefs
If Left(qdf.Name, 1) <> "~" Then

'you need to figure out TransferText command. Maybe
'you won't be lazy and expect people to read it to
'you and tutor you on how it works.
strFileName = qdf.Name

'Docmd.TransferText ....
DoCmd.TransferText transferType:=acExportDelim, TableName:=strFileName, FileName:="C:\test\" & strFileName & ".txt", hasfieldnames:=True

End If
Next qdf
MsgBox "Done"


End Sub

Collectives™ on Stack Overflow

How to export SQL statement results to an Excel File

3 Answers 3

2 Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Linked

Related