Solved

Export Query recordset to Excel Template using ADO

Posted on 2010-11-24
16
51 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
 
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
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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

896 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

11 Experts available now in Live!

Get 1:1 Help Now