Is it possible to export a query to Excel when the query is just SQL in VBA?

[Seth Schrock]

I have a listbox that allows multiple selections. Since I'm using it as the criteria for a query, I'm doing the query in VBA. I would like to be able to export the results of the query to Excel. I have tried the following:

DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLSX, "C:\IT Inventory\IP Address List.xlsx", False

However, it doesn't recognize the variable strSQL as a query (as many of you probably would have known immediately). Is there a way to do this? Do I need to execute the query first (CurrentDb.Execu te strSQL)?

[zmbd]

yes

http://bytes.com/topic/access/answers/940229-updating-access-using-excel-spreadsheets-used-distribute-access-info

and

http://bytes.com/topic/access/answers/940200-after-exporting-access-query-xls-how-do-i-use-vb-auto-rename-file

-z

[Seth Schrock]

Here is what I tried:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, strSQL, "C:\IT Inventory\IP Address List.xslx", True

and here is the error message that I got: Run-time error 7871: The table name you entered doesn't follow IT Inventory object-naming rules.

What I'm gathering from both my original method and the new method is that it is looking for a table/query with the name "SELECT tblDevice.Compu terName FROM....", which of course doesn't exist.

[zmbd]

Oh,
Sorry, didn't read the OP very well.
The "strqry" must be the name of an actual query or table in the database not one that is created at runtime.

You can get around this by adding the dynamic query to the qrydef collection, calling that name in the method, and then deleting it later.

-z

[Seth Schrock]

I'm totally lost by your second paragraph. How do I add strSQL (I assume that is the dynamic query you mentioned) to the qrydef collection?

[NeoPa]

You have to create a new query using the SQL first Seth. The answer to your question is actually "No", but Z was trying to help you along those lines anyway. More of a workaround.

[zmbd]

add dynamic query to collection

Depends on how one looks at the glass :)

No, you can not work with the SQL string directly in the transfersheet method

Yes, there is a way to use the SQL string by adding the SQL as a new query to the collection.

So here's how I work around the limitation (in very brief as I need to wind down another project before the weekend):

Declare your normal DAO database stuff but we're also going to work with the query object
Dim zqrydef As QueryDef

Now I use the same Query name over and over again for reports transfers etc so I kill the current version

On Error Resume Next
zdbs.QueryDefs.Delete "thequeryname"

I know... the error trap... lazy on my part; however, if the query doesn't exsit then the kill fails

Now I build my SQL for the query say as strSQL and then create the new query as
(note that I actually have a function that does this so I pass it the name of the query "strQueryNa me" say it's "qry_demona me")
Set zqrydef = dbs.CreateQuery Def(strQueryNam e, strSQL)

Now you can use the transfersheet method using the runtime SQL named "qry_demona me" because now it's in the collection.

-z

[twinnyfo]

Hey Z,

I admit that your solution is venturing into new territory for me, so I must ask questions to understand.....

By adding the QueryDef "qry_demona me" to the QueryDefs collection, with this cause an error if we perform this same action again? Or will we just overwrite the current QueryDef for "qry_demona me"?

Also, will the query "qry_demona me" be available to the database outside this module? i.e., is this QueryDef truly created on the fly, just for the purpose of exporting the query to Excel, or is there now a query named "qry_demona me" available in my list of queries?

I think I am starting to learn some new things here......

:-)

[Seth Schrock]

Okay, I fear that I'm not totally understanding what is going on here, but I did make a stab at it. Here is what I have. On the form I have a button, cmbExportList. Here is the main portion of its On_Click event:

Private Sub cmbExportList_Click()
Dim strSQL as String

strSQL = "SELECT tblDevice.ComputerName, tblIPAddress_New.IPAddress " & _
         "FROM tblDevice LEFT JOIN tblIPAddress_New ON tblDevice.DeviceNumber = tblIPAddress_New.DeviceNumber " & _
         "WHERE tblDevice.Type IN " & strList & " AND tblIPAddress_New.IPAddress <> 'DHCP' " & _
         "ORDER BY tblDevice.Branch, tblIPAddress_New.IPAddress"
         
CreateQryDef (strSQL)

I then have the function CreateQryDef() like this:

Function CreateQryDef(pstrSQL As String)
Dim db As Database
Dim rst As dao.Recordset
Dim zqrydef As QueryDef

On Error Resume Next
zdbs.QueryDefs.Delete "zqrydef"

Set zqrydef = db.CreateQueryDef("zqrydef", pstrSQL)

End Function

Option Explicit is set. Is this what you had in mind? I'm scared to even test it at this point without having someone else look at it. Once I get the QueryDef created, I can get the export function fine.

[NeoPa]

Twinny, adding a QueryDef to the QueryDefs collection saves it permanently and makes it available to the user as a Query.

If you type QueryDefs then press F1 to open help you can click on the QueryDef link in the diagram at the top for this information and much more.

That's as much detail as we can cover on this here as, though it does appear to be closely related, you aren't the OP of the thread, so should try to avoid asking any questions in it.

[zmbd]

vba

Hi Seth,
It's my day off and we had a visit with the Dr. today... all very good news; however, I'm sure you know how it goes... 9am appoint... and you might get out sometime within the next month. :-)

First Code Block post #9:
So long as the string evaluates to a valid SQL you'll be fine. I double check my SQL by doing a debug-print and then doing a cut and past into a design-mode-query-sql-view and making sure it actually pulls the intended records.

Second Code Block Post #9:


Line 2: change to Dim zdbs AS DAO.Database
(or change Line 7 zdbs to db - also watch the db in line 9 as these must all agree - option explicit will help catch these when doing a compile)
The DAO explicit may not be required; however ensures the correct references are used... Access can get confussseededed :)

Line 3: Not needed... unless you're going to open the record set for some reason.

Line 7 and 9: Personally I would not use "zqrydef" as the name to store the query under as you have already used that as a variable name. My personal preference is to name my querys using the prefix "qry_" and append something fairly meaningful to create the name.

I would also change from a "function" to a "sub" as you're not returning anything. However, you could, withing the function check to see if the query was successfully created and return true, false, error, or whatever... many times, if I use this as a function, I'll go ahead and create a record set based on the new query and return the number of records and then close the record-set so that the calling code can use the query.

To help assure yourself, as NeoPa suggested, [F1]. Also, make a backup of your database and play with the code there.... this is just a select query, so it wont hurt your data and the deletion is specific to the one query item so it shouldn't hurt your other queries.

Give'r-a-whirl and let's see what happens.

-z

[Seth Schrock]

Well, nothing happens. Here is what I have;

Private Sub cmbExportList_Click()
Dim strSQL as String
Dim db As Database
Dim zqrydef As QueryDef


strSQL = "SELECT tblDevice.ComputerName, tblIPAddress_New.IPAddress " & _
         "FROM tblDevice LEFT JOIN tblIPAddress_New ON tblDevice.DeviceNumber = tblIPAddress_New.DeviceNumber " & _
         "WHERE tblDevice.Type IN " & strList & " AND tblIPAddress_New.IPAddress <> 'DHCP' " & _
         "ORDER BY tblDevice.Branch, tblIPAddress_New.IPAddress"
         
Set db = CurrentDb

On Error Resume Next
db.QueryDefs.Delete "qryTemp"

Set zqrydef = db.CreateQueryDef("qryTemp", strSQL)

End Sub

The code runs with no errors, but no query is created. This might be a stupid question, but doesn't zqrydef need to be run?

[zmbd]

The only way this will fail is if the SQL is in error that we've ignored

so lets get the string and re-enable the error traping

Insert "stop" after the strSQL
Insert On Error GoTo 0 after that stop
-
Run your code
-
On the break>
-
<ctrl+G>
in the immediate window
?strSql
Select the string and
<ctrl+C>
Open a new query in design mode. Change to SQL view. Select everything in the window and delete it. Paste the SQL in the window <ctrl+V>.
Go ahead at this point and let the remainder of the code run by clicking the green arrow. We might get an error that tells us something here too...
Run the query we just created and see what happens. If there is an error in the SQL you will get snagged here too.

In order for this to work... the query must be valid.... doesn't have to return any records... just has to be valid.

Also Post back the evaluated string here so I can take a look at it - there is a variable "strlist" that is undefined in your code. Suggest using that option explicit and doing a compile. You should set this as a default under tools/options in the VBA editor.
-z

[Seth Schrock]

The variables were defined, but in cutting out the parts of the code that didn't matter to this problem, I missed including them in the post.

And you were correct that the problem was with the query, but not with the syntax. I had failed to include a section of code that set strList. Since strList is part of the criteria, the query obviously failed.

Thanks so much for all of your help!

Here is the whole On_Click sub:

Private Sub cmbExportList_Click()
Dim strDocName As String, strWorksheet As String
Dim strWorksheetPath As String, strFullName As String
Dim blnFileExists As Boolean
Dim strMsg As String, strSQL As String
Dim strList As String
Dim varItem As Variant
Dim zqrydef As DAO.QueryDef
Dim db As Database

With Me.lstType
    strList = "("
    For Each varItem In .ItemsSelected
        strList = strList & .ItemData(varItem) & ","
    Next varItem
    
    Mid(strList, Len(strList), 1) = ")"
End With

strSQL = "SELECT tblDevice.ComputerName, tblIPAddress_New.IPAddress " & _
         "FROM tblDevice LEFT JOIN tblIPAddress_New ON tblDevice.DeviceNumber = tblIPAddress_New.DeviceNumber " & _
         "WHERE tblDevice.Type IN " & strList & " AND tblIPAddress_New.IPAddress <> 'DHCP' " & _
         "ORDER BY tblDevice.Branch, tblIPAddress_New.IPAddress"
On Error GoTo 0

On Error Resume Next

Set db = CurrentDb
 
On Error Resume Next
db.QueryDefs.Delete "qryTemp"
 
Set zqrydef = db.CreateQueryDef("qryTemp", strSQL)

strMsg = "The file already exists.  Would you like to replace it?" & vbCrLf & vbCrLf & "Note: 'No' opens the existing file"

strDocName = strSQL
strWorksheet = "IP Address List"
strWorksheetPath = "C:\IT Inventory\"
strFullName = strWorksheetPath & strWorksheet & ".xlsx"

blnFileExists = MyFileExists(strFullName)

If blnFileExists = True Then
    Select Case MsgBox(strMsg, vbCritical + vbYesNoCancel)
        Case Is = vbYes
            Kill strFullName
            ExportToExcel
        Case Is = vbNo
            RunExcel (strFullName)
        Case Is = vbCancel
            Exit Sub
    End Select
Else
    ExportToExcel
End If
End Sub

[zmbd]

On first glance I suggest move line 24 to after line 31 in the finished code to re-enable the default error traping (or, if you have your own error traping setup then place it after the qry-deletion)
-z