Solved

Export Query recordset to Excel Template using ADO

Posted on 2010-11-24
16
48 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:jezskill
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the responses, I shall try your methods over th weekend and respond accordingly.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now