Solved

Export Query recordset to Excel Template using ADO

Posted on 2010-11-24
16
55 Views
Last Modified: 2016-04-11
I am desparately trying to export a query recordset to an excel template without success. The query is has parmeters linked to form objects, which is where I might be going wrong but need some pointers.

So in my vba code , I need to set a recordset connection to the access query "qryHFAuditsDue1"

and then create instance of excel workbook from the template and paste recordset from cell range a2 down (using 'copyfromrecordset' ?)

I have attached my code which has been round the houses and am currently getting an invalid SQL Statement Error.

Please help
Public Sub AuditsDue1_xlFile()
     '   Late Binding (Needs no reference set)
    Dim oXL As Excel.Application
    'Dim oExcel As Object
    Dim sFullPath As String
    'Dim sPath As String
    
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Set rst = New ADODB.Recordset
    Set conn = CurrentProject.Connection

rst.Open "qryHFAuditsDue1", conn, adOpenForwardOnly, adLockReadOnly
     
     '   Create a new Excel instance
    'Set oXL = CreateObject("Excel.Application")
    Set oXL = New Excel.Application
     
     
     '   Only XL 97 supports UserControl Property
    On Error Resume Next
    oXL.UserControl = True
    On Error GoTo 0
     
     
     '   Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "C:\Temp\AuditsDue.xlt"
     
     
     '   Open it
    With oXL
        .Visible = True
        .Workbooks.Add (sFullPath)
        .Cells(2, 1).CopyFromRecordset rst
    End With
    
rst.Close

Set rst = Nothing
     
     
ErrExit:
    Set oXL = Nothing
    Exit Sub
     
ErrHandle:
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit
End Sub

Open in new window

0
Comment
Question by:jezskill
  • 5
  • 4
  • 2
  • +3
16 Comments
 
LVL 7

Expert Comment

by:shaydie
ID: 34209239
Have you tried building your sql statement in the code rather than use the query?

strSQL = "Select............" '(whatever your sql statement should be)
rst.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly
0
 
LVL 7

Expert Comment

by:shaydie
ID: 34209256
That way you can set and make sure the variables you say are coming from form values actually have a value and so on.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 34209637
Building the SQL dynamically is certainly an option with some advantages.
You can, of course, still use the query though.
You just need to explicitly evaluate the parameter values as the expression service can't do that for you through a data access API such as ADO.
You could just use a UDF to open your recordset for you such as shown here.

Cheers.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:jezskill
ID: 34214535
this is my sql for the query recordset

strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade]FROM ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id WHERE (((tblJob.DateScheduled) Between [forms]![frmInvoiceReport].[Calendar1] And [forms]![frmInvoiceReport].[Calendar2]) AND ((tblJobDescriptionLookUpResp.PlannedResponsive)='"A"'));"

The vba code window gives me an error with regards to the last condition and highlights the "A" witha complie error expected: end of statement. should the A not be encapsulated in double quotes?
0
 
LVL 33

Expert Comment

by:Norie
ID: 34215001
You shouldn't try and build an SQL statement like that - it's just going to be a nightmare to debug.

Also, I'm a little confused by this, there is no SQL statement in the code you posted.

I'm also not sure that that query would work using ADO.

That's because of the parameters, if you were to use that SQL for a query or report in Access you would at least have to have the form 'frmInvoiceReport' open.

Anyway, try something like this for the SQL, it will at least make it easier to debug.
Dim strSQL As String

   strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade] "
   strSQL = strSQL & " FROM "
   strSQL = strSQL & " ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) "
   strSQL = strSQL & " INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id "
   strSQL = strSQL & " WHERE (tblJob.DateScheduled Between [forms]![frmInvoiceReport].[Calendar1] AND [forms]![frmInvoiceReport].[Calendar2]) AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

Open in new window

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 34215110
You had a query - but you've moved to dynamically built SQL??
Not a problem in itself - but not necessary, bearing in mind what's been said already about parameter evaluation.

Naturally the forms would need to be open.
If the parameters need to be supplied regardless then you'd need to use a user defined function to supply the criteria instead.

If you're going with SQL in VBA then just concatenate the values in.

strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & [forms]![frmInvoiceReport].[Calendar1] & " AND " & [forms]![frmInvoiceReport].[Calendar2] & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

Cheers
0
 
LVL 1

Author Comment

by:jezskill
ID: 34216454
Thanks for the responses, I shall try your methods over th weekend and respond accordingly.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 34216469
Ah, just noticed "Calendar1". So these are dates?

strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & Format([forms]![frmInvoiceReport]![Calendar1], "\#yyyy\-mm\-dd\#") & " AND " & Format([forms]![frmInvoiceReport].[Calendar2], "\#yyyy\-mm\-dd\#")  & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 125 total points
ID: 34216478
Which is often preferably created as

strSQL = strSQL & " WHERE (tblJob.DateScheduled >= " & Format([forms]![frmInvoiceReport]![Calendar1], "\#yyyy\-mm\-dd\#") & " AND tblJob.DateScheduled < " & Format([forms]![frmInvoiceReport].[Calendar2] + 1, "\#yyyy\-mm\-dd\#")  & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

If your DateScheduled may contain time component parts too.
0
 
LVL 1

Author Comment

by:jezskill
ID: 34222565
Ok , this is the current code I have, unfortunately I am still getting an invalid SQL statement error. The form is open which references the date values (which have no time values) .  Not sure what I should try now?
Public Sub AuditsDue1_xlFile()
     '   Late Binding (Needs no reference set)
    Dim oXL As Excel.Application
    'Dim oExcel As Object
    Dim sFullPath As String
    'Dim sPath As String
    
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set rst = New ADODB.Recordset
    Set conn = CurrentProject.Connection
    
'strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade]FROM ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id WHERE (((tblJob.DateScheduled) Between [forms]![frmInvoiceReport].[Calendar1] And [forms]![frmInvoiceReport].[Calendar2]) AND ((tblJobDescriptionLookUpResp.PlannedResponsive)='A'));"

Dim strSQL As String

   strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade] "
   strSQL = strSQL & " FROM "
   strSQL = strSQL & " ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) "
   strSQL = strSQL & " INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id "
   strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & Format([Forms]![frmInvoiceReport]![Calendar1], "\#yyyy\-mm\-dd\#") & " AND " & Format([Forms]![frmInvoiceReport].[Calendar2], "\#yyyy\-mm\-dd\#") & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"



rst.Open "qryHFAuditsDue1", conn, adOpenForwardOnly, adLockReadOnly
     
     '   Create a new Excel instance
    'Set oXL = CreateObject("Excel.Application")
    Set oXL = New Excel.Application
     
     
     '   Only XL 97 supports UserControl Property
    On Error Resume Next
    oXL.UserControl = True
    On Error GoTo 0
     
     
     '   Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "C:\Temp\AuditsDue.xlt"
     
     
     '   Open it
    With oXL
        .Visible = True
        .Workbooks.Add (sFullPath)
        .Cells(2, 1).CopyFromRecordset rst
    End With
    
rst.Close

Set rst = Nothing
     
     
ErrExit:
    Set oXL = Nothing
    Exit Sub
     
ErrHandle:
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit
End Sub

Open in new window

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 34222678
The syntax looks fine.
I'd suggest the old standard, to print out the SQL (Debug.Print strSQL) and execute that definition in a new query window.
See if it parses.  Nothing stands out to me.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 125 total points
ID: 34223424
One thing I would definitely check is the parantheses.

I don't know where the SQL came from but there seems to be quite a few of them kicking about.

I'm sure most of them are needed but there are times when you copy SQL that's been generated it includes some you don't need.

That's usually not a problem if you are just going to use the SQL as is without adding criteria or values.

The best way to test it is to do what LPurvis suggests.

You might want to add another couple of variables for the dates.
Dim strSQL As String
Dim strStartDate As String
Dim strEndDate As String
 
strStartDate = Format([Forms]![frmInvoiceReport]![Calendar1], "\#yyyy\-mm\-dd\#")
strenddatee Format([Forms]![frmInvoiceReport].[Calendar2], "\#yyyy\-mm\-dd\#")
 
    
   strSQL = "SELECT tblJob.OrderNo, tblJobDescriptionLookUpResp.JobDescription, tblSite.ERComments, tblSite.HSComments, tblSite.[Road Name Retail], tblSite.[Address Line 3], tblSite.Town, tblSite.[Post Code], tblSite.[Road Name], tblSite.[District Name], tblSite.[Channel of Trade] "
   strSQL = strSQL & " FROM "
   strSQL = strSQL & " ((tblJob INNER JOIN tblJobDescriptionLookUpResp ON tblJob.JobDesc = tblJobDescriptionLookUpResp.JobDescriptionID) "
   strSQL = strSQL & " INNER JOIN tblSite ON tblJob.SiteID = tblSite.[R Code]) LEFT JOIN tblInterceptor ON tblSite.[R Code] = tblInterceptor.Site_id "
   strSQL = strSQL & " WHERE (tblJob.DateScheduled Between " & strStartDate & " AND " & strEndDate & ") AND (tblJobDescriptionLookUpResp.PlannedResponsive='A')"

Open in new window

0
 
LVL 1

Author Comment

by:jezskill
ID: 34334250
Apologies for delay, I am still trying to carve out some time to test.
0
 
LVL 6

Accepted Solution

by:
nathaniel earned 250 total points
ID: 38082291
When I tried reconstructing the SQL string (i tested the content of the SQL string from the immediate pane), I think one of the errors are missing parenthesis (after Between and last line):

It should be:

WHERE (tblJob.DateScheduled Between ([forms]![frmInvoiceReport].[Calendar1] AND [forms]![frmInvoiceReport].[Calendar2]) AND (tblJobDescriptionLookUpResp.PlannedResponsive='A'))
0
 
LVL 24

Expert Comment

by:broomee9
ID: 41544411
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question