Custom Search
 


Fix MS Access error: Query must have at least one destination field



I have had tolerated this MS Access error for so long and finally I decided to do something about it.

"Query must have at least one destination field."

Background info:

I have an Access form with a few buttons. One of the buttons opens up an Excel spreasheet by select some data from an Access table and then display them in Excel. I use VBA command DoCmd.OutputTo to do the job, see below. The DoCmd calls a pre-built Access query to select the data.

DoCmd.OutputTo acOutputQuery, "qry_2ndQuarter", acFormatXLS, "2ndQuarter.xls", True

Most of the time it runs without any problems, but once in a while it errors out with error message like this "Query must have at least one destination field.". It's caused by the fact that the SQL query inside qry_2ndQuarter is deleted by Access on random basis.

I debugged step by setp and the sql is definitely erased after running DoCmd.OutputTo command. So after years of frustration (used Access 2003 and Access 2007), I decide to tackle it by VBA code rather than trying to figure out why it got deleted randomly.

I used to manually rebuild the query by pasting SQL into it after it got erased. Now I use VBA code to automatically rebuild the query if the SQL of the query is automatically deleted by Access. This freed me to do more productive work than being a manual labor of copy and paste.

Dim strQueryName as String
strQueryName = "qry_2ndQuarter"

Set objDB = CurrentDb

If objDB.QueryDefs(strQueryName).SQL = "SELECT *;" & vbCrLf Then

    '' Delete the query
    objDB.QueryDefs.Delete(strQueryName)

    '' Re-create the query
    qdef = objDB.CreateQueryDef(strQueryName, "SELECT * FROM Sales WHERE quarter=2")
End If

'' Output to Excel
DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS, "2ndQuarter.xls", True

Note that after Access deletes the query's SQL, it seems that Access rebuilds it by putting in SELECT *; as the SQL (In some versions of Access, the emptied query can be SELECT; instead of SELECT *;). Access also adds a carriage return character after the semi-collon so in the code we need to check if the SQL is equal to "SELECT *;" & vbCrLf

Hope this article helps you happily complete your work and solve the exact problem you are probably facing.

Happy coding!


Copyright© GeeksEngine.com



Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to count distinct records in MS Access
4.How to do transaction based processing in MS Access
5.How to open a document (local/network file or web page) from MS Access
6.How to use ADOX to create unique composite index - the VBA approach
7.How to do cross-table update queries in MS Access - the right way
8.Three efficient ways to get the number of records by using VBA
9.How to create a composite unique index (not as a primary key) in MS Access
10.Use VBA to get the correct number of records in a Recordset object
11.Disable Access Prompt when a record is changed, table deleted, or action queries run
12.How to hide and unhide a MS Access object
13.How to return multiple values from a VBA function (Part 3)
14.How to return multiple values from a VBA function (Part 2)
15.How to return multiple values from a VBA function (Part 1)
16.Three ways to programmatically duplicate a table in MS Access by VBA
17.Create a DLL by CSharp or VB.Net for VBA
18.How to correctly reference and call a DLL
19.How to register a C# or VB.Net DLL
20.Email address validation by Regular Expressions using VBA
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy